I am trying to use VLOOKUP in VBA. I have a workbook with worksheets "DB Load" and "Service Address". I am looking up "Circuit ID's In sheet "DB Load" (column S) and matching that Circuit ID to the worksheet "service address" where column A contains the Circuit ID's and column B in the worksheet contains the service address', with column B containing the address' that I would like to pull and the fill in column H (Service Address 1) on the DB Load sheet. I hope I did not confuse you.
When I use VLOOKUP manually it works great. but when I try to use my code, well let's just say I get a migraine.
I tried using VLOOKUP two different ways. The first way i used the worksheet function. The second way I tried was copying the formula when I performed the VLOOKUP manually.
Using the worksheet function I get the first 9 rows in column H filled in with the incorrect address but at least I made some progress. A win in my book. The rest of the cells in column H are blank.
Using the method of copying the manual formula I get the first cell filled in but with the correct address. A definite win in my book. however, the rest of the cells are blank.
The two ways that I have tried to write the VLOOKUP are below. If anyone can help me figure out what I am doing wrong would be very much appreciated.
I have also attached a couple of screen shots of the worksheets for review if needed.
Thank you in advance for your time and assistance.
wsh4 is the worksheet "DB Load."
wsh4 is the worksheet "Service Address."
__________________________________________________________________________________________________________________________________________________
Thank You,
Chris
When I use VLOOKUP manually it works great. but when I try to use my code, well let's just say I get a migraine.
I tried using VLOOKUP two different ways. The first way i used the worksheet function. The second way I tried was copying the formula when I performed the VLOOKUP manually.
Using the worksheet function I get the first 9 rows in column H filled in with the incorrect address but at least I made some progress. A win in my book. The rest of the cells in column H are blank.
Using the method of copying the manual formula I get the first cell filled in but with the correct address. A definite win in my book. however, the rest of the cells are blank.
The two ways that I have tried to write the VLOOKUP are below. If anyone can help me figure out what I am doing wrong would be very much appreciated.
I have also attached a couple of screen shots of the worksheets for review if needed.
Thank you in advance for your time and assistance.
wsh4 is the worksheet "DB Load."
wsh4 is the worksheet "Service Address."
VBA Code:
wsh4.Activate
lastrow = wsh4.Range("A" & Application.Rows.Count).End(xlUp).Row
For j = 2 To lastrow
wsh4.Range("H" & j).Value = Application.WorksheetFunction.VLookup(wsh4.Range("S2" & j), wsh3.Range("A:B"), 2, 1)
Next j
VBA Code:
lastrow = wsh4.Range("A" & Application.Rows.Count).End(xlUp).Row
wsh4.Range("H2").Value = "=VLOOKUP(S2,ServiceAddress,2,TRUE)"
wsh4.Range("H2" & lastrow).FillDownThank You,
Chris
)
