title: Hope below code is equivalent to Vlookup with formatting. any suggestions. Excel VBA
body: <p>Below is the code which I came up to get all the source formating whenever I require a vlookup type operation in excel. I am also getting any formulas by this way. If there is a way to elimate the formula and get only the value and formating then it will be of great help.</p>
  
  <pre><code>Option Explicit
  
  Sub finding()
  
      Dim wb As Workbook
      Set wb = ThisWorkbook
  
      Dim ws As Worksheet
      Set ws = ThisWorkbook.Worksheets("Sheet1")
  
      Dim ws1 As Worksheet
      Set ws1 = ThisWorkbook.Worksheets("Sheet2")
  
      Dim i As Integer
      Dim FoundRange As Range
      Dim sFoundRange As String
  
      Dim range_to_copy As Range
      Dim range_to_paste As Range
  
      Dim ToFindString As String
  
      For i = 2 To 9
  
          ToFindString = ws.Cells(i, 1)
  
          On Error Resume Next:
          sFoundRange = ws1.Range("E1:E12").Find(ToFindString).Address
  
          Debug.Print sFoundRange
  
          Set range_to_copy = ws1.Range(Replace(sFoundRange, "E", "F"))
          Set range_to_paste = ws.Range("B" &amp; i)
  
          range_to_copy.Copy
          range_to_paste.PasteSpecial xlPasteAllUsingSourceTheme
  
          Application.CutCopyMode = False
  
      Next i
  
  End Sub
  </code></pre>