Macro to vlookup from a list if specific text is met

MVP

Aug 25, 2022

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)?

hrh_dash

Iron Contributor

Aug 25, 2022

HansVogelaar , 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.

hrh_dash

Iron Contributor

Aug 26, 2022

HansVogelaar , 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..

Forum Discussion

Macro to vlookup from a list if specific text is met

Resources