pnwAnalyst
New Member
- Joined
- Oct 30, 2017
- Messages
- 9
New to VBA. I've searched the internet and found a bunch of valuable tips, but I've been working on this all darn day and can't come up with a viable solution.
I'm attempting to semi-automate a process here at work and this is the last segment and I'm stuck. I spent the first half of the morning trying to figure out why it was throwing an error only to find out that IFERROR won't work in a macro. And that I'm supposed to use the WorksheetFunction.
Here is the code that I copied and pasted from another macro that worked, but it was only using a vlookup not iferror.
And here is the best example that I found but I couldn't translate it to work for my application.
I'm attempting to semi-automate a process here at work and this is the last segment and I'm stuck. I spent the first half of the morning trying to figure out why it was throwing an error only to find out that IFERROR won't work in a macro. And that I'm supposed to use the WorksheetFunction.
Here is the code that I copied and pasted from another macro that worked, but it was only using a vlookup not iferror.
Code:
Sub AutomateAllTheThings4()
Dim lastRow As Long
Call OptimizeCode_Begin
'PO_DETAILS
Worksheets("PO_DETAILS").Select
Range("BE2").Select
Selection.Formula = "=IFERROR(VLOOKUP($AW2,Full_Item_List!A$2:A$1917,1,0), _
IFERROR(VLOOKUP($AX2,Full_Item_List!A$2:A$1917,1,0), _
IFERROR(VLOOKUP($AY2,Full_Item_List!A$2:A$1917,1,0), _
IFERROR(VLOOKUP($AZ2,Full_Item_List!A$2:A$1917,1,0), _
IFERROR(VLOOKUP($BA2,Full_Item_List!A$2:A$1917,1,0), _
IFERROR(VLOOKUP($BB2,Full_Item_List!A$2:A$1917,1,0), _
IFERROR(VLOOKUP($BC2,Full_Item_List!A$2:A$1917,1,0), _
IFERROR(VLOOKUP($BD2,Full_Item_List!A$2:A$1917,1,0), ""))))))))"
'Fills down the formula. Looks at Column to left to know when to stop filling the formula
lastRow = Range("BD1:BD" & Range("BD1").End(xlDown).Row).Rows.Count
Range("BE2" & ":BE" & lastRow).fillDown
Range("BF2").Select
Selection.Formula = "=IFERROR(VLOOKUP($AW2,Full_Item_List!B$2:B$1917,1,0), _
IFERROR(VLOOKUP($AX2,Full_Item_List!B$2:B$1917,1,0), _
IFERROR(VLOOKUP($AY2,Full_Item_List!B$2:B$1917,1,0), _
IFERROR(VLOOKUP($AZ2,Full_Item_List!B$2:B$1917,1,0), _
IFERROR(VLOOKUP($BA2,Full_Item_List!B$2:B$1917,1,0), _
IFERROR(VLOOKUP($BB2,Full_Item_List!B$2:B$1917,1,0), _
IFERROR(VLOOKUP($BC2,Full_Item_List!B$2:B$1917,1,0), _
IFERROR(VLOOKUP($BD2,Full_Item_List!B$2:B$1917,1,0), ""))))))))"
'Fills down the formula. Looks at Column to left to know when to stop filling the formula
lastRow = Range("BD1:BD" & Range("BD1").End(xlDown).Row).Rows.Count
Range("BF2" & ":BF" & lastRow).fillDown
Call OptimizeCode_End
End SubAnd here is the best example that I found but I couldn't translate it to work for my application.
Code:
Range("D4:D100").Value = Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(Range("B4:B100"), Sheets("Environmental").Range("A812:J912"), 8, False), _
Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(Range("B4:B100"), Sheets("Safety").Range("A812:J912"), 8, False), _
Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(Range("B4:B100"), Sheets("Agency").Range("A812:J912"), 8, False), _
Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(Range("B4:B100"), Sheets("Endurance").Range("A812:J912"), 8, False), _
Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(Range("B4:B100"), Sheets("Performance").Range("A812:J912"), 8, False), _
Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(Range("B4:B100"), Sheets("Abuse").Range("A812:J912"), 8, False), _
Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(Range("B4:B100"), Sheets("Audit").Range("A812:J912"), 8, False), _
Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(Range("B4:B100"), Sheets("Other BU").Range("A812:J912"), 8, False), _
Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(Range("B4:B100"), Sheets("ECO").Range("A812:J912"), 8, False), " ")))))))))
