Vlookup Function in VBA for whole column from another worksheet in the same workbook

Chris_LTD

New Member
Joined
Aug 6, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
I have a repetitive task daily that could go away with the help of VBA. I have a sheet that lists a large group of retail stores. In column "U" of the sheet "Future_Launch_Locations" it has the state that the store is in. In column "N" I need to fill out an HVAC Provider. A second sheet "HVAC Provider" has in column "A" all states and in column "B" the associated provider for that state. I would like to run a macro that quickly does the vlookup or equivalent VBA action, and paste the associated provider into column "N" of the "Future_Launch_Locations" worksheet for me.
 

Attachments

  • HVAC Provider.png
    HVAC Provider.png
    27 KB · Views: 12
  • Future_Launch_Locations.png
    Future_Launch_Locations.png
    24.5 KB · Views: 12

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
VBA Code:
Range("N2").Formula = "=INDEX('HVAC Provider'!$A:$A,MATCH('Future Launch locations'!$U2,'HVAC Provider'!$B:$B,0))"
Range("N2").AutoFill Destination:=Range("N2:N" & Cells(Rows.Count, "U").End(xlUp).Row)
 
Upvote 0
Solution
VBA Code:
Range("N2").Formula = "=INDEX('HVAC Provider'!$A:$A,MATCH('Future Launch locations'!$U2,'HVAC Provider'!$B:$B,0))"
Range("N2").AutoFill Destination:=Range("N2:N" & Cells(Rows.Count, "U").End(xlUp).Row)
hen I put this in it is just placing a #N/A in all the cells

HVAC Provider
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
 
Upvote 0
hen I put this in it is just placing a #N/A in all the cells

HVAC Provider
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
Never mind. I swapped the A:A and the B:B and it worked great thanks!
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top