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

[Chris_LTD]

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.

 

[krshnn]

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)

 

[Chris_LTD]

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​

 

[Chris_LTD]

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!