using a formula in VBA with a variable?

Oberon70

Board Regular
Joined
Jan 21, 2022
Messages
160
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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Remove that space in the formula string. Not this "=SUM(B2:B " but this "=SUM(B2:B"
 
Upvote 0
Solution

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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
Back
Top