Answered by:
Creating formulas in VBA problem using variables
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
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
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 rethink that.The following is my interpretation of what you are trying to do:==========================Option ExplicitSub foo()Dim LastCellGT As RangeDim FirstCellG As RangeSet 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 
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

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

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 