Andrew Kedem
New Member
I've been working with a macro for months and would appreciate help to solve an error that's appearing.
I'd also appreciate help in adding a few steps to the macro.
When I run the macro, occasionally the error, #VALUE!, appears in the DESCRIPTION cells. In the attached sample POST MACRO file, the error appears in the following cells: R23, R23, R31, R32, R35, R39 and R41.
Help Requested:
1) Would someone help me edit the macro so that the "#VALUE!" error does not happen?
Here's the macro:
Additional Help Requested
2) For header rows (i.e. rows with SKU but not price or rate), is there a way to CUT the data in the SKU field and PASTE it in the DESCRIPTION field?
3) After every SUBTOTAL row, could a blank row be inserted?
Let me know if you have questions. Thank you for helping in any way you can.
Andrew
I'd also appreciate help in adding a few steps to the macro.
When I run the macro, occasionally the error, #VALUE!, appears in the DESCRIPTION cells. In the attached sample POST MACRO file, the error appears in the following cells: R23, R23, R31, R32, R35, R39 and R41.
Help Requested:
1) Would someone help me edit the macro so that the "#VALUE!" error does not happen?
Here's the macro:
Code:
Sub AndrewKedem()
Dim Lr As Long
Lr = Range("A" & Rows.Count).End(xlUp).Row
With Range("M2:M" & Lr)
.Value = Evaluate(Replace("if(@<>"""",@,if(isnumber(search(""total""," & .Offset(, -12).Address & ")),""Subtotal"",""""))", "@", .Address))
End With
With Range("L2:L" & Lr)
.FormulaR1C1 = Evaluate(Replace("if(@<>"""",@,if(" & .Offset(, 1).Address & "=""Subtotal"",""=r[-1]c"",""""))", "@", .Address))
.Value = .Value
End With
With Range("R2:R" & Lr)
.FormulaR1C1 = Evaluate(Replace("if(@<>"""",@,if(" & .Offset(, -5).Address & "=""Subtotal"",""=rc[-5]&"""" """"&r[-1]c[-5]"",""=r[1]c[-5]""))", "@", .Address))
.Value = .Value
End With
With Range("E2:E" & Lr)
.FormulaR1C1 = Evaluate(Replace("if(@<>"""",@,if(" & .Offset(, 8).Address & "=""Subtotal"",""=r[-1]c"",""=r[1]c""))", "@", .Address))
.Value = .Value
End With
With Range("L2:M" & Lr)
.SpecialCells(xlBlanks).FormulaR1C1 = "=r[1]c"
.Value = .Value
End With
End Sub
Additional Help Requested
2) For header rows (i.e. rows with SKU but not price or rate), is there a way to CUT the data in the SKU field and PASTE it in the DESCRIPTION field?
3) After every SUBTOTAL row, could a blank row be inserted?
Let me know if you have questions. Thank you for helping in any way you can.
Andrew
Attachments
Last edited by a moderator: