using a formula in VBA with a variable?

Oberon70

Board Regular
Joined
Jan 21, 2022
Messages
152
Office Version
  1. 365
Platform
  1. Windows
I am trying to to use the below to add up a range that needs to be dynamic as different statements have different amount of transactions.

But I am Receiving the error "Run-Time error '1004': Application-defined or object-defined error.

My variables are globally defined.

VBA Code:
Sub adding()
'
' adding Macro
'

'
    wb_created.Sheets("To Be Receipted").Activate
    Range("E2").Select
    LastRow = wb_created.Sheets("To Be Receipted").Cells(Rows.Count, 1).End(xlUp).Row
    ActiveCell.Formula = "=SUM(C2:D2)"
    Selection.AutoFill Destination:=Range("E2:E" & LastRow), Type:=xlFillDefault
    
    Range("B" & (LastRow + 2)).Formula = "=SUM(B2:B " & LastRow & ")"
    Range("C" & (LastRow + 2)).Formula = "=SUM(C2:C " & LastRow & ")"
    Range("D" & (LastRow + 2)).Formula = "=SUM(D2:D " & LastRow & ")"
    Range("E" & (LastRow + 2)).Formula = "=SUM(E2:E " & LastRow & ")"
    
    
End Sub

Sorry about no mini sheet as I can't use add-ons when I am using my work laptop.

If needed, I will create the above on a fake spreadsheet and use mini sheet from my personal computer.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,427
Office Version
  1. 365
Platform
  1. Windows
If you hit "debug", which line of code does it highlight?

Is it the first one?
Unless "wb_created" is a global variable that is being set elsewhere, that would be an issue, as it appears to be an undeclared variable that hasn't been set to anything.
 

Oberon70

Board Regular
Joined
Jan 21, 2022
Messages
152
Office Version
  1. 365
Platform
  1. Windows
If you hit "debug", which line of code does it highlight?

Is it the first one?
Unless "wb_created" is a global variable that is being set elsewhere, that would be an issue, as it appears to be an undeclared variable that hasn't been set to anything.
Yes, it is the 1st line and wb_created is a global variable.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,427
Office Version
  1. 365
Platform
  1. Windows
Can you show us the VBA code where it is being defined and set?

Note that if you are already not in that workbook, you probably need to activate that workbook first before you can activate a sheet in it.
So you may need a line before that like:
VBA Code:
wb_created.Activate
 

Oberon70

Board Regular
Joined
Jan 21, 2022
Messages
152
Office Version
  1. 365
Platform
  1. Windows
Sorry, I shouldn't respond when it is nearly 1 in the morning. The 1st line that has the error is:

VBA Code:
 Range("B" & (LastRow + 2)).Formula = "=SUM(B2:B " & LastRow & ")"

and the global variables I have are:

VBA Code:
Dim wb_created As Workbook
Dim ClosedBook As Workbook
Dim FilePath, FileOnly, PathOnly As String
Dim InvoiceNum As Variant
Dim DateofInvoice As Date
Dim PortfolioCode As String
Dim AgentName As String
Dim InvoiceTotal As Double
Dim InvoiceGST As Double
Dim LastRow As Long

Option Explicit
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,737
Office Version
  1. 365
Platform
  1. Windows
Remove that space in the formula string. Not this "=SUM(B2:B " but this "=SUM(B2:B"
 
Solution

Oberon70

Board Regular
Joined
Jan 21, 2022
Messages
152
Office Version
  1. 365
Platform
  1. Windows
That did the trick. Thank you.
 

Forum statistics

Threads
1,175,456
Messages
5,897,524
Members
434,659
Latest member
Fityi

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