Insert a formula in a cell via VBA using reference cells

mblake5

New Member
Joined
May 24, 2012
Messages
18
I know there are various threads out there about this, but I haven't been able to make this work. I am trying to input a formula (and it needs to be a formula, not just the current answer) that is

Code:
activecell.offset(2,0).formula = sum(activecell:selection.end(x1up))*activecell.offset(2,-2)

I know this formula doesn't work, but I cannot seem to figure out what the correct syntax is. As an example, my cursor is in J6 and I need the sum of J6:J? (the last continuous cell with data above J6), multiplied by H8, and put the formula in J8.

And I cannot used the fixed cell numbers on any of it, because I never know how large the table is that I'm manipulating.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
I know there are various threads out there about this, but I haven't been able to make this work. I am trying to input a formula (and it needs to be a formula, not just the current answer) that is

Code:
activecell.offset(2,0).formula = sum(activecell:selection.end(x1up))*activecell.offset(2,-2)

I know this formula doesn't work, but I cannot seem to figure out what the correct syntax is. As an example, my cursor is in J6 and I need the sum of J6:J? (the last continuous cell with data above J6), multiplied by H8, and put the formula in J8.

And I cannot used the fixed cell numbers on any of it, because I never know how large the table is that I'm manipulating.

If you want the formulat to appear in the cell, then it has to be entered as text, like

activecell.offset(2,0).formula = "= sum(activecell:selection.end(x1up))*activecell.offset(2,-2)"
 
Upvote 0
Sorry, I should have been more specific. I know it needs to have quotes, but I was paraphrasing. My problem is that the formula itself does not work for what I'm trying to do.
 
Upvote 0
Try

Code:
    ActiveCell.Offset(2, 0).Formula = "=sum(" & Split(ActiveCell.Address, "$")(1) & 1 & ":" & Split(ActiveCell.Address, "$")(1) & ActiveCell.Row & ") * H2"
 
Last edited:
Upvote 0
Ok, so that is extremely close to working. The only problem is that it is returning this formula:

=SUM(J1:J6)*H2

The activecell is J6, so that's correct, but I need it to figure out where the consecutive data ends (which I used to use .End(x1Up) for), not go to the top of the column. Is there a way to do this?
 
Upvote 0
Ok, so that is extremely close to working. The only problem is that it is returning this formula:

=SUM(J1:J6)*H2

The activecell is J6, so that's correct, but I need it to figure out where the consecutive data ends (which I used to use .End(x1Up) for), not go to the top of the column. Is there a way to do this?


Nope, End(x"one"Up) won't work, but End(x"ell"Up) will. There are no constants in the model that use x1 that I am aware of. They are xl and mso which are related to Excel and Microsoft Office in shorthand. Typos are usually the biggest culprit when debugging new code.
 
Upvote 0
The following worked for me:

Code:
Sub Sum_It()
Dim c As String
Dim r As Integer
Dim f As Integer
    f = ActiveCell.End(xlUp).Row
    c = Split(ActiveCell.Address, "$")(1)
    r = ActiveCell.Row
    ActiveCell.Offset(2, 0).Formula = "=sum(" & c & f & ":" & c & ActiveCell.Row & ") * H2"
End Sub
 
Upvote 0
jeffmb - The new code works great. I then tried to apply the logic to make what was previous "H2" dynamic as well using an offset from the activecell of (2,-2), but am getting a "subscript out of range" error using the split function. Any suggestions?

Code:
    f = ActiveCell.End(xlUp).Row
    c = Split(ActiveCell.Address, "$")(1)
    r = ActiveCell.Row
    qr = ActiveCell.Offset(2, 0).Row
    qc = Split(ActiveCell.Offset(0, -2).Address, "$")(1)
        
    ActiveCell.Offset(2, 0).Formula = "=sum(" & c & f & ":" & c & ActiveCell.Row & ") * " & qc & qr & ""
 
Upvote 0
Take the qc & qr out of the quotes:

Code:
Sub Sum_It()
Dim c As String
Dim r As Integer
Dim f As Integer
Dim qr As Integer
Dim qc As String
    f = ActiveCell.End(xlUp).Row
    c = Split(ActiveCell.Address, "$")(1)
    r = ActiveCell.Row
    qr = ActiveCell.Offset(2, 0).Row
    qc = Split(ActiveCell.Offset(0, -2).Address, "$")(1)
    ActiveCell.Offset(2, 0).Formula = "=sum(" & c & f & ":" & c & r & ") * " & qc & qr
End Sub

Instead of qr and qc you could also use "ActiveCell.Offset(2, -2).Address"
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

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