vba - sum formula works in debug mode but not when I actually run the full macro

[jwoo89]

HI All -

I am having trouble trying to figure out why my code won't work. I want to sum up the column at the end of the last cell in the last row and when I step through debug mode it works perfectly fine. But when I run it all together I get my sum formula does not work and it goes somewhere else on the sheet.

Can someone assist? I have provided my code below. I am getting stuck at the 'sums up total in column and makes it negative code part:

Sub RunFEES()
'
Dim NextRow As Long
NextRow = Range("D" & Rows.Count).End(xlUp).Row + 1

'change balance to positive
Sheets("FEES").Select
    Range("E4").Select
    ActiveCell.FormulaR1C1 = "=ROUND(RC[-1],2)*-1"
    Range("E4").Select
     Range("E4").AutoFill Range("E4:E" & Range("A" & Rows.Count).End(xlUp).Row)
    Columns("E:E").Select

'paste positive values
    Range("E4").Select
     Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("D4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("E:E").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft

'add to last row the account id, segment, currency and balance
Range("A4").End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
ActiveCell.Value2 = "100039"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value2 = "margin"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value2 = "USD"

'sums up total in column and makes it negative
Range("D" & NextRow & ":D" & NextRow).Formula = "=SUM(D4:D" & NextRow - 1 & ")*-1"


'replace Margin with margin
Columns("B:B").Select
Selection.Replace What:="Margin", Replacement:="margin", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

'autofit to see balance to two decimals
Columns("D:D").EntireColumn.AutoFit
Range("D4").End(xlDown).Select

'copy sheet into new one
Sheets("FEES").Select
Range("A1").Select
    ActiveSheet.Copy

MsgBox "Done"

End Sub

Thanks!!

 

[Fluff]

Hi & welcome to MrExcel.
You need to move this

NextRow = Range("D" & Rows.Count).End(xlUp).Row + 1

to below where you select the sheet, otherwise you might be calculating the nextrow based on the wrong sheet.

 

[jwoo89]

Hi & welcome to MrExcel.
You need to move this

NextRow = Range("D" & Rows.Count).End(xlUp).Row + 1

to below where you select the sheet, otherwise you might be calculating the nextrow based on the wrong sheet.

Thank you so much Fluff!!!! this worked perfectly!! I really appreciate it!

 

[Fluff]

If you're interest you could use

Sub RunFEES()
   With Sheets("Fees")
      With .Range("D4", .Range("D" & Rows.count).End(xlUp))
         .Value = Evaluate("round(" & .Address & ",2)*-1")
      End With
      With .Range("A" & Rows.count).End(xlUp).Offset(1)
         .Resize(, 3).Value = Array("100039", "margin", "USD")
         .Offset(, 3).FormulaR1C1 = "=sum(r4c:r[-1]c)*-1"
      End With
      .Range("B:B").Replace "Margin", "margin", xlPart, , False, , False, False
      .Copy
   End With

   MsgBox "Done"
End Sub

which should do the same thing.

 

[jwoo89]

If you're interest you could use

Sub RunFEES()
   With Sheets("Fees")
      With .Range("D4", .Range("D" & Rows.count).End(xlUp))
         .Value = Evaluate("round(" & .Address & ",2)*-1")
      End With
      With .Range("A" & Rows.count).End(xlUp).Offset(1)
         .Resize(, 3).Value = Array("100039", "margin", "USD")
         .Offset(, 3).FormulaR1C1 = "=sum(r4c:r[-1]c)*-1"
      End With
      .Range("B:B").Replace "Margin", "margin", xlPart, , False, , False, False
      .Copy
   End With

   MsgBox "Done"
End Sub

which should do the same thing.

Thanks so much for this! Will give this a shot too! again much appreciated

 

[Fluff]

My pleasure.