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
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.Code:activecell.offset(2,0).formula = sum(activecell:selection.end(x1up))*activecell.offset(2,-2)
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.
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.
Try
Code:ActiveCell.Offset(2, 0).Formula = "=sum(" & Split(ActiveCell.Address, "$")(1) & 1 & ":" & Split(ActiveCell.Address, "$")(1) & ActiveCell.Row & ") * H2"
Last edited by jeffmb; Jun 1st, 2012 at 05:32 PM.
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?
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
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 & ""
Take the qc & qr out of the quotes:
Instead of qr and qc you could also use "ActiveCell.Offset(2, -2).Address"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
Last edited by jeffmb; Jun 4th, 2012 at 11:30 AM.
Awesome, thanks. I figured it was something little like that.
Like this thread? Share it with others