Thread: Problem using vLookup in Excel VBA

I have a spreadsheet into which I place a number in one column and it needs to get the matching location info from another spreadsheet (which is usually closed). I can do this using a vLookup in the cells, but this is clumsy and as the first spreadsheet already has some 4500 rows (and growing), it is slow and causes the file size to be very large.

Therefore I have decided to do it in VBA, but I am having a problem with it that I cannot seem to solve. Any help would be appreciated......

The VBA code I have in the first spreadsheet is as follows:

(This is located in Sheet1 code so that it executes automatically when the value of the cell in column AP is changed in the current row)

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim intAssetRow, intBUILDnum As Integer
Dim lRowSelected As Long

intAssetRow = ActiveCell.Row

If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub

    On Error Resume Next

''''''''''''''''''''''''''''''''''''''''''''''''''
'Update LOCATION Info in current row using vLookup
''''''''''''''''''''''''''''''''''''''''''''''''''

If Not Intersect(Target, Range("AP2:AP65535")) Is Nothing Then

    Application.ScreenUpdating = True
    Application.EnableEvents = True

    If Not Target.Cells.Value = "" Then
    
    intBUILDnum = ("AP" & intAssetRow)
        
        Range("AO" & intAssetRow).Formula = "=VLookup(intBUILDnum, '\\ODFSLFS1\im\5.Records\Assets\Locations\[OD Locations Master List.xls]Building Master List'!$A$2:$I$335,5,FALSE)"
        Range("AO" & intAssetRow).Value = Range("AO" & intAssetRow).Value
        
        Range("AQ" & intAssetRow).Formula = "=VLookup(""SCRAPPED"", '\\ODFSLFS1\im\5.Records\Assets\Locations\[OD Locations Master List.xls]Building Master List'!$A$2:$I$335,2,FALSE)"
        Range("AQ" & intAssetRow).Value = Range("AQ" & intAssetRow).Value
    
    End If
    
End If

The second vLookup line works flawlessly, but the first does not. I need the vLookup to work with which ever row is currently active, so that this will work no matter how many rows there are. The other problem is that the value in column AP is not always SCRAPPED, it varies from row to row. So the value it does the vLookup on has to be a variable which is why the (working) second vLookup line is no good as it is.

I can see what is happening with the first vLookup, it places the vLookup formula into the cell in column AO but intBUILDnum is a parameter that the spreadsheet cannot resolve, so it gives me a #NAME? error. I just don't know enough vba to be able to see the solution, though I suspect the code needs to be changed quite a lot to be able to do what I am asking.

As I said before your help will be greatly appreciated......

Try

        Range("AO" & intAssetRow).Formula = "=VLookup(" & intBUILDnum & ", '\\ODFSLFS1\im\5.Records\Assets\Locations\[OD Locations Master List.xls]Building Master List'!$A$2:$I$335,5,FALSE)"

Regards