Insert a formula in a cell via VBA using reference cells
Insert a formula in a cell via VBA using reference cells
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Insert a formula in a cell via VBA using reference cells

  1. #1
    New Member
    Join Date
    May 2012
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Insert a formula in a cell via VBA using reference cells

     
    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.

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    9,964
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert a formula in a cell via VBA using reference cells

    Quote Originally Posted by mblake5 View Post
    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)"

  3. #3
    New Member
    Join Date
    May 2012
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert a formula in a cell via VBA using reference cells

    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.

  4. #4
    Board Regular
    Join Date
    Jul 2008
    Location
    Fort Lauderdale
    Posts
    168
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert a formula in a cell via VBA using reference cells

    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.

  5. #5
    New Member
    Join Date
    May 2012
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert a formula in a cell via VBA using reference cells

    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?

  6. #6
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    9,964
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert a formula in a cell via VBA using reference cells

    Quote Originally Posted by mblake5 View Post
    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.

  7. #7
    Board Regular
    Join Date
    Jul 2008
    Location
    Fort Lauderdale
    Posts
    168
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert a formula in a cell via VBA using reference cells

    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

  8. #8
    New Member
    Join Date
    May 2012
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert a formula in a cell via VBA using reference cells

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

  9. #9
    Board Regular
    Join Date
    Jul 2008
    Location
    Fort Lauderdale
    Posts
    168
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert a formula in a cell via VBA using reference cells

    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 by jeffmb; Jun 4th, 2012 at 11:30 AM.

  10. #10
    New Member
    Join Date
    May 2012
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert a formula in a cell via VBA using reference cells

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

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com