Using cell values in a formula with VBA

[stephen antoine]

Hello all, I'm racking my brain trying to find out how to use a cell value in a formula with VBA.

For example, I have the following code:

FinalRow = Cells(65536, 1).End(xlUp).Row
CYGP = Cells(Final Row, 11).Column
Cells(FinalRow + 2, CYGP).Select


I now need to be able to use the value that's located in the selected cell elsewhere in the spreadsheet. I've tried the following:

ActiveCell.FormulaR1C1 = "=R[FinalRow]C[CYGP]

This was an attempt to put the value that's in Cells(FinalRow, CYGP) into my active cell, but it's not working...Can any of you VBA gurus please help?

 

[Jonmo1]

Like this maybe?

ActiveCell.FormulaR1C1 = "=R[" & FinalRow & "]C[" & CYGP & "]"

or actually, if the ultimate goal is to get the value of Cells(finalrow,CYGP) into the ACTIVE cell...

ActiveCell.Value = Cells(FinalRow,CYGP).Value

 

[stephen antoine]

That got me closer as it's now pulling the value in a cell, but it's pulling values from way down below the data I need....

General question: what do the ampersands do in VBA?

 

[stephen antoine]

Now I typed in the following:

ActiveCell.Formula = Cells(FinalRow, CYGP).value / 5

That worked

I'm not sure what the difference in .Formula and .FormulaR1C1 is...

Would you be able to answer these two general questions?

1) What's the difference between .Formula and .FormulaR1C1
2) What does the Ampersand sign (&) do in VBA

Thanks for your help....

 

[Scott Huish]

Formula is used with A1 style references.
FormulaR1C1 is used with R1C1 references.

See About cell and range references in the help file for more info.

The & does the same thing it does in the spreadsheet, it a concatenation operator.

However, you are not putting a formula in a cell, I would use .Value

ActiveCell.Value = Cells(FinalRow, CYGP).value / 5

 

[stephen antoine]

OK...

Thanks so much for the help!!!

 

[hatepoppy]

btw, an ampersand is the character used to concatenate a string. i.e. ("A" & "B" = AB)

the reason for using concatenation in the above application is that you were trying to use a variable inside the quotation marks surrounding a string.

dim cellname as string
cellname = "C2"
dim MyString1 as string
MyString1 = "=C1+cellname" <--bad, returns exactly what is inside the quotes.
MyString1 = "=C1+" & cellname <--good, returns what is inside the quotes followed by the value of the string cellname, or =C1+C2.

 

[stephen antoine]

Ok thanks....So is there a way to use row and column variables inside the Cell.FormulaR1C1 action?

For instance, I want to base a formula off of a certain cell's value, but in order to find that certain cell I've used variables...

I tried the following:

ActiveCell.FormulR1C1 = "=R[FinalRow + 2]C[CYSales] * R[-5]C"

Where FinalRow is my row value and CYSales is my column value. I then want to multiply the result with a number that is 5 rows up from the active cell...

But I can't get this to work....

 

[hatepoppy]

Ok thanks....So is there a way to use row and column variables inside the Cell.FormulaR1C1 action?

For instance, I want to base a formula off of a certain cell's value, but in order to find that certain cell I've used variables...

I tried the following:

ActiveCell.FormulR1C1 = "=R[FinalRow + 2]C[CYSales] * R[-5]C"

Where FinalRow is my row value and CYSales is my column value. I then want to multiply the result with a number that is 5 rows up from the active cell...

But I can't get this to work....

think of it as building the string youd like to pass to ActiveCell.FormulaR1C1 piece by piece, like so:

ActiveCell.FormulaR1C1 = "=R[" & FinalRow + 2 & "]C[" & CYSales & "] * R[-5]C"

you have to keep all variables OUTSIDE the quotation marks.

 

[stephen antoine]

Thanks...That worked and I figured out what the problem is...

I'm using .FormulaR1C1 so when I put the FinalRow variable in for the reference for the row property it's using the row value of the variable FinalRow and adding it to the Active Cell.

So, if I'm already in cell 1,205, and the final row above is 1,203, then the R1C1 style takes me to row 2,208 by adding them together...

The same is true for the column property....

The following code worked...

ActiveCell.Formula = Cells(FinalRow + 2, CYSales).Value * ActiveCell.Offset(-5, 0).Value