Macro to Replace Cell with VLOOKUP

[borjasanz]

Hi I'm very new at using Macros I only know to record them not to write them but recording this one didn't seem to work quite right.

I'm trying to do the following:

Replace the value of Column 1 (Account number) with its corresponding value that is Restaurant name.
I have a long list of data and I would first filter the accounts I want that are in column one. However only some of these are restaurant accounts (I'm only interested in these)
Then after filtering them, I would like the macro to run a vlookup to another sheet to search for the restaurant name that matches the content of the cell in column 1 and then paste itself as values on top of the account number.

The problem I have is these lists of data are going to vary in size, I want the macro to detect when there is no more data and then stop because I had the problem when recording it that I had to go all the way down to row 100,000 and the macro would turn really slow and it would ruin the purpose because I want to copy the data to another document to upload to SAP system.

Any ideas?

The accounts I'm interested in are the ones above: 990001348 (to help you with the initial filter) The vlookup table is on sheet 2. The column of the table I want is 3.

Thanks!!!!

[bmxfreedom]

Your post is a little confusing. Are you trying to return account numbers or Restaurant names? Are you saying that the account numbers are duplicated for Restaurants and Non-Restaurants, thus your idea for the need for a filter? (I ask because a filter won't help in this scenario unless the values are filtered and then pasted elsewhere first.)

It's best not to use a filter or any kind of pasting if possible. It gets a bit tricky, but it's possible to use code to figure this all out.

[borjasanz]

Your post is a little confusing. Are you trying to return account numbers or Restaurant names? Are you saying that the account numbers are duplicated for Restaurants and Non-Restaurants, thus your idea for the need for a filter? (I ask because a filter won't help in this scenario unless the values are filtered and then pasted elsewhere first.)

It's best not to use a filter or any kind of pasting if possible. It gets a bit tricky, but it's possible to use code to figure this all out.

Sorry, I want to have Restaurant names. And yeah I forgot, after I filter I want the macro to paste only visible cells elsewhere. Then comes the vlookup and then finally the paste as values of the restaurant names on top of the account numbers to replace them.

[bmxfreedom]

I'm still missing the need for the filter... This can all be done with vba, without filtering, and without copying or pasting. It would be easy to create a loop that looks at each value in column A on sheet 1, makes sure that it is bigger than the number you want (990001348), and then use the match function to return the row on which the account was found, which can be in turn used to define the range on which the Restaurant name is located, and use range.value=range.value to place the name in the cell where the account number was. Try this:

Sub ReplaceAccountNums()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim LstRw As Integer
Dim Matchnum As Integer
LstRw = Cells(ThisWorkbook.Sheets(1).Rows.Count, 1).End(xlUp).Row
For i = 2 To LstRw
    On Error GoTo nxti
    If Range("A" & i).Value <= 990001348 Then
    GoTo nxti
    Else
    Matchnum = WorksheetFunction.Match(Sheets(1).Range("A" & i).Value, Sheets(2).Range("A:A"), 0)
    ThisWorkbook.Sheets(1).Range("A" & i).Value = Sheets(2).Cells(Matchnum, 3).Value
    End If
nxti:
Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

There may be some variables here that need to be changed based on your actual workbook, but I won't know those without looking at it. For instance, this assumes that the values being matched start at row 2. This may not be the case, in which case, the 2 in "For i = 2 To LstRw" would need to change to whatever that row is.

[borjasanz]

I'm still missing the need for the filter... This can all be done with vba, without filtering, and without copying or pasting. It would be easy to create a loop that looks at each value in column A on sheet 1, makes sure that it is bigger than the number you want (990001348), and then use the match function to return the row on which the account was found, which can be in turn used to define the range on which the Restaurant name is located, and use range.value=range.value to place the name in the cell where the account number was. Try this:

Sub ReplaceAccountNums()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim LstRw As Integer
Dim Matchnum As Integer
LstRw = Cells(ThisWorkbook.Sheets(1).Rows.Count, 1).End(xlUp).Row
For i = 2 To LstRw
    On Error GoTo nxti
    If Range("A" & i).Value <= 990001348 Then
    GoTo nxti
    Else
    Matchnum = WorksheetFunction.Match(Sheets(1).Range("A" & i).Value, Sheets(2).Range("A:A"), 0)
    ThisWorkbook.Sheets(1).Range("A" & i).Value = Sheets(2).Cells(Matchnum, 3).Value
    End If
nxti:
Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

There may be some variables here that need to be changed based on your actual workbook, but I won't know those without looking at it. For instance, this assumes that the values being matched start at row 2. This may not be the case, in which case, the 2 in "For i = 2 To LstRw" would need to change to whatever that row is.

I'm having trouble with the line "Matchnum = WorksheetFunction.Match(Sheets(1).Range("A" & i).Value, Sheets(2).Range("A:A"), 0)"

What can I do? I think it doesn't find a match.

[bmxfreedom]

That's odd. The "On Error GoTo nxti" should take care of that problem. Maybe I'm mistaken and that doesn't work with worksheet functions. Try changing this line:

Matchnum = WorksheetFunction.Match(Sheets(1).Range("A" & i).Value, Sheets(2).Range("A:A"), 0)

to these lines:

If IsError(WorksheetFunction.Match(Sheets(1).Range("A" & i).Value, Sheets(2).Range("A:A"), 0)) then
GoTo nxti
Else
Matchnum = WorksheetFunction.Match(Sheets(1).Range("A" & i).Value, Sheets(2).Range("A:A"), 0)
End If

I haven't had time to test this, but it may solve the issue.