Vlookup using VBA

[fatalcore]

Hi,
I am stuck with this problem.
What I want is when I click the vlookup. I want to run a vlookup with Column F20, to sheet "Master" and paste the values of Procedure in J20 and continue the procedure.

Then I want the data pulled up by using vlookup be converted to values,So that I can edit them as needed.

I am attaching a dummy sheet for reference with before and after sheet.

Thanks in advance.

[georgiboy]

Here is how you can do the one line...

Sheet7.Range("J20").Value = _
WorksheetFunction.VLookup(Sheet7.Range("F20").Value, Sheet2.Range("B2:G4"), 6, False)

here it is in a loop to suit your merged cells

Dim x As Long

For x = 20 To 36 Step 8
Sheet7.Range("J" & x).Value = _
WorksheetFunction.VLookup(Sheet7.Range("F" & x).Value, Sheet2.Range("B2:G4"), 6, False)
Next x

I avoid using merged cells like that makes life hard as far as i am concerned, i avoid merging cells like the plague.

Hope this helps

[Bob Phillips]

Public Sub LookupValues()
Dim cell As Range

With Range(Range("F20"), Range("F20").End(xlDown)).Offset(0, 4)

.FormulaR1C1 = "=VLOOKUP(RC6,Master!C2:C7,6,FALSE)"
.Value = .Value
End With
End Sub

[fatalcore]

Thanks Mate.