Insert a formula in a cell via VBA using reference cells

[mblake5]

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

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.

 

[JLGWhiz]

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

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)"

 

[mblake5]

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.

 

[jeffmb]

Try

    ActiveCell.Offset(2, 0).Formula = "=sum(" & Split(ActiveCell.Address, "$")(1) & 1 & ":" & Split(ActiveCell.Address, "$")(1) & ActiveCell.Row & ") * H2"

 

[mblake5]

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?

 

[JLGWhiz]

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.

 

[jeffmb]

The following worked for me:

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

 

[mblake5]

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?

    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 & ""

 

[jeffmb]

Take the qc & qr out of the quotes:

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"

 

[mblake5]

Awesome, thanks. I figured it was something little like that.