-1

Had no luck figuring out how to code this in VBA.

I have a list of names on ws2 starting in cell A5. The size of this list will change daily. I want to vlookup each item from data on ws1. Then would like paste the data as values into corresponding cell in column C.

Picture below is what ws2 looks like. That's the vlookup formula I would have used if done manually.

enter image description here

Could also consider xlookup if you think it's better. Lookup array would be column A on ws1, return array would be column E.

Hoping to keep the code as simple as possible since this is my first VBA project. Any comments/explanations would be much appreciated!

Thanks!

Automating_My_Life
  • 37
  • 5

1 Answers1

0

Tab names

If 'ws1' and 'ws2' are the tab names of the worksheets this should do it, note I've changed A:L to A:E in the formula as you aren't looking up anything past column E.

Sub Button1_Click()
Dim ws As Worksheet

    Set ws = Sheets("ws2")
 
    With ws.Range("C5:C" & ws.Range("A" & Rows.Count).End(xlUp).Row)
        .Formula = "=VLOOKUP(A5, ws1!A:E,5,0)"
        .Value = .Value
    End With

End Sub

Variable names

If 'ws1' and 'ws2' are variable names referencing sheets then use this.

Dim ws1 As Worksheet
Dim ws2 As Worksheet

    Set ws1 = Sheets("Sheet1")
    Set ws2 = Sheets("Sheet2")
 
    With ws2.Range("C5:C" & ws2.Range("A" & Rows.Count).End(xlUp).Row)
        .Formula = "=VLOOKUP(A5,'" & ws1.Name & "'!A:E,5,0)"
        .Value = .Value
    End With

End Sub

Codenames

Finally, if ws1 and ws2 are actually the codenames of the worksheets this should work.

Sub Button1_Click()

    With ws2.Range("C5:C" & ws2.Range("A" & Rows.Count).End(xlUp).Row)
        .Formula = "=VLOOKUP(A5,'" & ws1.Name & "'!A:E,5,0)"
        .Value = .Value
    End With

End Sub
norie
  • 9,609
  • 2
  • 11
  • 18
  • If i named the ws in the VBA editor section, how can i reference that? My sheet names might change over time so i thought naming them in the VBA editor section would be better. I tried substituting the ws2 for the name but it didn't work. – Automating_My_Life Apr 06 '21 at 19:05
  • If you mean you have a variables ws1 and ws2 that reference the sheets see the edited answer. – norie Apr 06 '21 at 19:15
  • Your code works if i use the sheet names, but I want to use the code name I used in the properties section of VBA editor (think it's the called the code name). i don't believe I can use the code name in the vlookup formula. Any way to reference the code name rather than the sheet name? Thanks! – Automating_My_Life Apr 06 '21 at 19:28
  • If 'ws1' and 'ws2' are the codenames of the sheets see my latest edit. – norie Apr 06 '21 at 19:32
  • Worked perfectly. Hoping to get to your level one day my friend. You knew every different way to code that. Could I ask though, how come you didn't use the Application.WorksheetFunction for the vlookup? – Automating_My_Life Apr 06 '21 at 19:42