none
Creating formulas in VBA problem using variables RRS feed

  • Question

  • Hi,

     

    Having a few problems creating formulas in VBA,

    I'll try and explain,

    LastCellGT is the last cell in row in column G that has any value.

    FirstCellG is the first cell in row in column G that has any value.

    I'm trying to create a bit of code for a formula that will calculate "LastCellGT" * "FirstCellG".

    I've tried this but failed miserably:-

    Range("G" & LastCellG - 2).Formula = "=SUM(("G" & FirstCellG) * ("G" & LastCellGT))"

    Hope someone can help,

    Thanks

    Best regards

    Scott

     

     

    Monday, July 18, 2011 4:34 PM

Answers

  • If your variables are row numbers:

    Range("G" & LastCellG - 2).Formula = "=G" & FirstCellG & "*G" & LastCellG

    If your variables are range objects

    LastCellG.Offset(-2,0).Formula = "=" & FirstCellG.Address & "*" & LastCellG.Address

    Note that there is no need to wrap your multiplication in a SUM function.

     


    HTH, Bernie
    • Marked as answer by styoda Tuesday, July 19, 2011 10:56 AM
    Monday, July 18, 2011 6:35 PM

All replies

  • On Mon, 18 Jul 2011 16:34:28 +0000, styoda wrote:
     
    >
    >
    >Hi,
    >
    >
    >Having a few problems creating formulas in VBA,
    >
    >I'll try and explain,
    >
    >LastCellGT is the last cell in row in column G that has any value.
    >
    >FirstCellG is the first cell in row in column G that has any value.
    >
    >I'm trying to create a bit of code for a formula that will calculate "LastCellGT" * "FirstCellG".
    >
    >I've tried this but failed miserably:-
    >
    >Range("G" & LastCellG - 2).Formula = "=SUM(("G" & FirstCellG) * ("G" & LastCellGT))"
    >
    >Hope someone can help,
    >
    >Thanks
    >
    >Best regards
    >
    >Scott
    >
    >
     
    Since you only post a snippet, it is not possible to say where you went wrong.
     
    It is good practice to always precede your VBA module with Option Explicit, and to explicitly Dim all your variables.   In your wording, you indicate that LastCellGT and FirstCellG refer to cells, which would imply they are Range variables.  However, in your code you seem to be treating them as numeric variables.  Also, you do not mention the type for variable "LastCellG".  If you do not have Option Explicit set, then VBA will Dim this as a variable of type Variant.
     
    It seems you want to put this formula into column G at a point two rows above the last cell.  Of course, that will overwrite anything you had in that cell previously, so you might want to re-think that.
     
    The following is my interpretation of what you are trying to do:
     
    ==========================
    Option Explicit
    Sub foo()
        Dim LastCellGT As Range
        Dim FirstCellG As Range
    Set FirstCellG = Range("G1")
    Set LastCellGT = Cells(Cells.Rows.Count, "G").End(xlUp)
     
    LastCellGT.Offset(rowoffset:=-2).Formula = _
        "=sum(" & FirstCellG.Address(0, 0) & _
        "*" & LastCellGT.Address(0, 0) & ")"
     
    End Sub
    =======================
     

    Ron
    Monday, July 18, 2011 6:09 PM
  • If your variables are row numbers:

    Range("G" & LastCellG - 2).Formula = "=G" & FirstCellG & "*G" & LastCellG

    If your variables are range objects

    LastCellG.Offset(-2,0).Formula = "=" & FirstCellG.Address & "*" & LastCellG.Address

    Note that there is no need to wrap your multiplication in a SUM function.

     


    HTH, Bernie
    • Marked as answer by styoda Tuesday, July 19, 2011 10:56 AM
    Monday, July 18, 2011 6:35 PM
  • Thanks very much Bernie, had to make an adjustment but works perfectly.

    I had to place in "SUM" in the formula, as "#value" showed up in the cell.

    This is what I did,

    Range("G" & LastCellG - 2).Formula = "=SUM(G" & FirstCellG & ":G" & LastCellG & ")"

    Thanks

    Best regards

    Scott

    Tuesday, July 19, 2011 10:56 AM
  • What you did is correct, to put a SUM formula for a range. I thought you wanted

    >a formula that will calculate "LastCellGT" * "FirstCellG". 

    But note that your code may put the formula into the range being summed, creating a circular reference. Depends on the First and Last cell.


    HTH, Bernie
    Tuesday, July 19, 2011 12:56 PM
  • Thanks It is really helpful.
    Thursday, May 7, 2015 10:32 AM