Macro to execute a vlookup from A1 to last cell used

Hi Guys,

I am here to ask help on creating a macro to make my life easier.

I have 2 sheets, Database and Input. In Database I have 2 columns: "Users" and "Country", I need to have the macro to autofill the column A in the Input sheet when I write the User in the column B.

The Database is updated daily so I can remove or add entries, this is why I need to have a command that keeps executing the vlookup in the column A UNTIL the end cell of column B.

edited:

this seems to work if anyone is looking for the same:

Sub Hello()

Dim ws As Worksheet
Dim LastRow As Long
Dim TargetRange As Range

On Error GoTo MyErrorHandler:

Set ws = Sheets("Database")

LastRow = ws.Cells(Rows.Count, "B").End(xlUp).Row

Set xWs = Sheets("Input")

LastRowXX = xWs.Cells(Rows.Count, "B").End(xlUp).Row

Set TargetRange = ws.Range("A1:B" & LastRowXX)

result = Application.WorksheetFunction.VLookup(Sheets("Input").Range("B1:B" & LastRow), TargetRange, 2, False)

'MsgBox result

Range("A1:A" & LastRowXX) = result

MyErrorHandler:
If Err.Number = 1004 Then
MsgBox "error"
End If

End Sub

Hello and welcome.

Unfortunately you have broken one of the forum rules and so no-one can help you at this time.

You need to insert code tags around your code.

The following standard message explains what you need to do.


Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

While you are at it.

Please post a copy of your spreadsheet.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.