Vlookup Excel VBA

Question

I need help on simple macro that will replicate a vlookup function. So, I have a table of values from H2:H9 in Sheet1 A value in H2 corresponds to the entire Column D in sheet 3 and the value needs to be copy/pasted into every cell in Column D from row 2 to the last row with data. Same for H3, H3 corresponds to the entire Column AC and a value in H3 must be copy/pasted into every cell in Column AC in sheet 3 from row 2 to the last row. I am using this code for figuring out the last line. Can anyone please please help me?

Dim lastRow As Long
Dim rng As Range

Set rng = ThisWorkbook.Sheets(2).Cells

lastRow = rng.Find(What:="*", After:=rng.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row

Hello, please see this [link](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) for a good explanation by @SiddharthRout about finding the last used range. (Which I learned today :) ) — Dubison, Jun 09 '15 at 13:40

Maxime Petit · Answer 1 · 2015-06-09T13:52:20.680

1

That simple line should help you find the last used row in your sheet.

lastRow= ActiveWorkbook.Sheets(3).UsedRange.Rows.Count

About the rest, is that what you mean?

sheets(3).range("D2:D" & lastRow).value = sheets(1).range("H2").value
sheets(3).range("AC2:AC" & lastRow).value = sheets(1).range("H3").value

edited Jun 09 '15 at 13:52

answered Jun 09 '15 at 13:30

Maxime Petit

23
6

please see the link in the comment above, why it is not good to use `UsedRange` function while determining last used range. This function will fail even though the last cell deleted. It will keep showing deleted cell as last used range. – Dubison Jun 09 '15 at 13:42
Also, switch your 2nd line to "H3". – puzzlepiece87 Jun 09 '15 at 13:43
@Dubison You are right the Usedrange fuction is more dodgy, but easier to demo something with it when you don't know exactly what the file structure is! puzzlepiece87, Thank you for noticing – Maxime Petit Jun 09 '15 at 14:01

score 0 · Answer 2 · answered Jun 09 '15 at 14:26

Something like that

' Find Last row in Sheet1 in Column H
LastRow = ThisWorkbook.Worksheets(1).Cells(ThisWorkbook.Worksheets(1).Rows.Count, "H").End(xlUp).Row
' Get array of range
vArr = ThisWorkbook.Worksheets(1).Range("H2:I" & LastRow).Value
ThisWorkbook.Worksheets(3).Range("D2:D9").Cells.Resize(UBound(vArr), 1) = vArr

Vlookup Excel VBA

2 Answers2