Aug 24 2022 08:39 PM
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!
Aug 25 2022 12:24 AM
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.
Aug 25 2022 03:07 AM
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.
Aug 25 2022 04:09 AM
SolutionThank 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)?
Aug 25 2022 06:09 AM
@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.
Aug 25 2022 08:04 PM
@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..
Aug 26 2022 03:27 AM
Try to single-step through the code and inspect the value of the variables. Perhaps that will provide a clue.
Aug 26 2022 03:55 AM
Aug 25 2022 04:09 AM
SolutionThank 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)?