VLOOKUP ISSUES

[Chris101]

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."

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

__________________________________________________________________________________________________________________________________________________

lastrow = wsh4.Range("A" & Application.Rows.Count).End(xlUp).Row

wsh4.Range("H2").Value = "=VLOOKUP(S2,ServiceAddress,2,TRUE)"

wsh4.Range("H2" & lastrow).FillDown

Thank You,
Chris

 

[Peter_SSs]

When I get stumped can I contact you directly through the board?

No, you have to just post your new question in a new thread (preferably with XL2BB sample data and expected results) & hope somebody will respond.

Both examples work great. And i like the idea of entering the data all at once with one line of code.

Thank You...

You're welcome. Glad to help. Thanks for the follow-up.