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

jwoo89

New Member
Joined
Jan 5, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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:

VBA Code:
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!!
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
You need to move this
VBA Code:
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.
 
Solution

jwoo89

New Member
Joined
Jan 5, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Hi & welcome to MrExcel.
You need to move this
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
If you're interest you could use
VBA Code:
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

New Member
Joined
Jan 5, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
If you're interest you could use
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
My pleasure.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,653
Messages
5,626,105
Members
416,161
Latest member
David1966Lewis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top