SOLVED

Macro to vlookup from a list if specific text is met

Regular Contributor

I would like to create a macro to vlookup from a list if specific text is met. However, when i execute the macro, the error msg was popped up: "Run-time error '1004' Application or Object-defined error".

 

this is the code below, not sure whether if the code is correct..

Application.ScreenUpdating = FALSE

Dim ws              As Worksheet
Dim Destwb          As Workbook
Dim Destws          As Worksheet
Dim DC2_startrow    As Long
Dim DC2_lastrow     As Long
Dim wsrng           As Range
Dim i               As Long

Set ws = ThisWorkbook.Sheets("TCA")

Set Destwb = Workbooks.Open("C:\Users\hrhquek\Desktop\DC consol testing.xlsx")
Set Destws = Destwb.Sheets("Rapid - List With DC")

DC2_startrow = ws.Range("C:C").Find(What:="DC2", After:=ws.Range("C1")).Row
DC2_lastrow = ws.Range("C:C").Find(What:="DC2", After:=ws.Range("C1"), SearchDirection:=xlPrevious).Row

Set wsrng = ws.Range("D" & DC2_startrow & ":K" & DC2_lastrow)

Destws.Range("V1").EntireColumn.Insert

'On Error Resume Next

For i = DC2_startrow To DC2_lastrow
    
    Destws.Range("V" & i).Value = Application.WorksheetFunction.VLookup(Destws.Range("A" & i).Value, wsrng, 1, 0)
     
Next i

Application.ScreenUpdating = TRUE

End Sub

 

Attaching a pic of how my source workbook (containing macro) would look.

Appreciate the assistance provided in advance!

7 Replies

@hrh_dash 

 

You look through cells on Destws, but the loop bounds DC2_startrow and DC2_endrow are calculated based on ws. Do the sheet match exactly?

 

And you specify 1 as column index in VLookup, so it should return the search value.

 

As usual, it is impossible to know what goes wrong without seeing the workbooks.

Hi @Hans Vogelaar , there will be 2 workbooks. Source and Dest. 

 

Source wb will be the file containing the macro.

 

i tried to insert an IF ELSE function but it doesn't solve the issue. I only need to vlookup based on values that are DC2.

 

Attaching both files for your reference.

best response confirmed by hrh_dash (Regular Contributor)
Solution

@hrh_dash 

Thank you. As I mentioned, DC2_startrow and DC2_lastrow are row numbers on the source sheet.

You use them as row numbers on the destination sheet. That makes no sense.

Can you explain in words what you're trying to accomplish with this macro (which belongs in a standard module, by the way, not in a worksheet module)?

@Hans Vogelaar , i finally got what you meant on the DC2_startrow and DC2_lastrow.

 

It is not a worksheet module because vlookup will only be run when the code is executed not and not executed automatically.

 

Now the code runs perfectly fine.

 

Apologies for the trouble.

@Hans Vogelaar , i have this issue whereby the 2nd vlookup; it does not any populate values. Could it be due to using the syntax (below) twice in a sub?

 

I did a debug.print however it is printing out the correct row numbers.

 

LOD3_startrow = ws.Range("C:C").Find(What:="LOD3", After:=ws.Range("C1")).Row
LOD3_lastrow = ws.Range("C:C").Find(What:="LOD3", After:=ws.Range("C1"), SearchDirection:=xlPrevious).Row

DC2_startrow = ws.Range("C:C").Find(What:="DC2", After:=ws.Range("C1")).Row
DC2_lastrow = ws.Range("C:C").Find(What:="DC2", After:=ws.Range("C1"), SearchDirection:=xlPrevious).Row

 

Below is the vlookup macro:

Sub vlkuptoDCconsol_DC1()
    
    Application.ScreenUpdating = FALSE
    
    Dim ws          As Worksheet
    Dim Destwb      As Workbook
    Dim Destws      As Worksheet
    Dim LOD3_startrow As Long
    Dim LOD3_lastrow As Long
    Dim wsrng       As Range
    Dim i           As Long
    Dim DestwslastRow As Long
    Dim DestwslastRow2 As Long
    
    Dim DC2_startrow As Long
    Dim DC2_lastrow As Long
    Dim j           As Long
    Dim wsrng1      As Range
    
    Set ws = ThisWorkbook.Sheets("TCA")
    
    Set Destwb = Workbooks.Open("C:\Users\hrhquek\Desktop\DC consol testing.xlsx")
    Set Destws = Destwb.Sheets("Rapid - List With DC")
    
    LOD3_startrow = ws.Range("C:C").Find(What:="LOD3", After:=ws.Range("C1")).Row
    LOD3_lastrow = ws.Range("C:C").Find(What:="LOD3", After:=ws.Range("C1"), SearchDirection:=xlPrevious).Row
    
    Set wsrng = ws.Range("D" & LOD3_startrow & ":K" & LOD3_lastrow)
    DestwslastRow = Destws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    Destws.Range("O:S").EntireColumn.Insert
    
    On Error Resume Next
    
    For i = 2 To DestwslastRow
        
        Destws.Range("O" & i).Value = Application.WorksheetFunction.VLookup(Destws.Range("A" & i).Value, wsrng, 7, 0)
        Destws.Range("O:O").NumberFormat = "dd-Mmm-yy"
        
        Destws.Range("P" & i).Value = Application.WorksheetFunction.VLookup(Destws.Range("A" & i).Value, wsrng, 5, 0)
        Destws.Range("P:P").NumberFormat = "$#,##0.00_);($#,##0.00)"
        
        Destws.Range("Q" & i).Value = Application.WorksheetFunction.VLookup(Destws.Range("A" & i).Value, wsrng, 8, 0)
        
        Destws.Range("S" & i).Value = Application.WorksheetFunction.VLookup(Destws.Range("A" & i).Value, wsrng, 6, 0)
        
        Destws.Range("R" & i).Value = Left(Destws.Range("S" & i), Len(Destws.Range("S" & i)) - 5)
        
    Next i
    

    DestwslastRow2 = Destws.Cells(Destws.Rows.Count, "O").End(xlUp).Row
      
    Destws.Range("V:Z").EntireColumn.Insert
    
    DC2_startrow = ws.Range("C:C").Find(What:="DC2", After:=ws.Range("C1")).Row
    DC2_lastrow = ws.Range("C:C").Find(What:="DC2", After:=ws.Range("C1"), SearchDirection:=xlPrevious).Row
    
    Set wsrng1 = ws.Range("D" & DC2_startrow & ":K" & DC2_lastrow)
    DestwslastRow = Destws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    On Error Resume Next
    
    For j = 2 To DestwslastRow 'Vlookup not populating data 
        
        Destws.Range("V" & i).Value = Application.WorksheetFunction.VLookup(Destws.Range("A" & i).Value, wsrng1, 7, 0)
        Destws.Range("V:V").NumberFormat = "dd-Mmm-yy"
        
        Destws.Range("W" & i).Value = Application.WorksheetFunction.VLookup(Destws.Range("A" & i).Value, wsrng1, 5, 0)
        Destws.Range("W:W").NumberFormat = "$#,##0.00_);($#,##0.00)"
        
        Destws.Range("X" & i).Value = Application.WorksheetFunction.VLookup(Destws.Range("A" & i).Value, wsrng1, 8, 0)
        
        Destws.Range("Z" & i).Value = Application.WorksheetFunction.VLookup(Destws.Range("A" & i).Value, wsrng1, 6, 0)
        
        Destws.Range("Y" & i).Value = Left(Destws.Range("S" & i), Len(Destws.Range("S" & i)) - 4)
        
    Next j
    
    Application.ScreenUpdating = TRUE
    
End Sub

 

appreciate the assistance..

 

@hrh_dash 

Try to single-step through the code and inspect the value of the variables. Perhaps that will provide a clue.

, realise that there wasn't a need for 2 for loops.. thanks for the advice..