• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Please Help Edit Macro

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:

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

  • Sample_Spreadsheet_Before_Macro.xlsx
    12.4 KB · Views: 2
  • Sample_Spreadsheet_Post_Macro.xlsx
    14.7 KB · Views: 1
Last edited by a moderator:
Back
Top