Vlookup or Index Match (Code)

[Seandobson2402]

Hi All,

I want to learn how to do a VLOOKUP in VBA. But it seems rather confusing when reading different sites online. I wish to put the following formula into Worksheet "Weekly Stats" Cell "D3" -

=VLOOKUP('Weekly Stats'!A1,'My Stats'!C4:M56,4,FALSE)​

1. Worksheets("Weekly Stats") is where the formula needs to go - into cell ("D3")
2. Worksheets("Weekly Stats").Range("A1") is the Value I wish to match in the VLOOKUP
3. Worksheets("My Stats").Range("C4:M56") is the range I wish to match my value to - column ("C") containing the Same data as ("Weekly Stats").Range("A1"), and column ("F") being the fourth column along from C (,4) for the information I wish to display in ("Weekly Stats").Range("D3")
4. I wish the value to be paste special values so no formula will be showing.

Is the above possible?

Thanks
Sean

 

[JoeMo]

If I understand correctly what you want, this should do it:

With Worksheets("Weekly Stats").Range("D3")
      .Formula = "=VLOOKUP('Weekly Stats'!A1,'My Stats'!C4:M56,4,FALSE)"
      .Value = .Value
End With

 

[Eric W]

Another way is to call VLOOKUP from within VBA:

    Set ws = Worksheets("Weekly Stats")
    Set ms = Worksheets("My Stats")
    ws.Range("D3").Value = WorksheetFunction.VLookup(ws.Range("A1"), ms.Range("C4:M56"), 4, False)