Using a variable in formula

[rguy84]

How do you use a variable in a formula?

I have a variable:

lastRow = Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row

I have a function called CalcTime(start,end), and I need to insert that into mlastRow. I am attempting

Range("M" & lastRow).Formula = "=CalcTime("K"&lastRow,"L"&lastRow)"

I either get an undefined error or the cell is =CalcTime(KlastRow,LlastRow) versus =Calctime(K4,L4) depending where and how many quotes I add

 

[rguy84]

I tried that earlier doesn't work, it says syntax error

 

[jasonb75]

Yours looks like too many " jonmo

Range("M" & lastRow).Formula = "=CalcTime(K" & lastRow &",L" & lastRow &")"

or maybe

Range("M" & lastRow).FormulaR1C1 = "=CalcTime(RC11,RC12)"

 

[Jonmo1]

good catch jason.

Nice with the R1C1

 

[rguy84]

Jason are RC11 and RC12 special values? Or can you tell me what that says?

 

[jasonb75]

Those are ranges specified in R1C1 reference style.

RC11 translates to same row, column 11
RC12 translates to same row, column 12

in short, if the formula is written to M4, then it will come out as =Calctime(K4,L4)
if it's written to M20 then it will come out as =CalcTime(K20,L20)

Same logic applies anywhere, the row used in the formula will be the same as the row holding the formula.

 

[rguy84]

haha nice. Almost like cheating. Out of curiousity why does it put $k12 vs k12? Cause technically to reference a cell you do $a$1?

 

[jasonb75]

Just the way it translates between the 2 reference styles

R defines the row, C defines the column, a number after the row or column defines it as absolute (i.e. with a $ prefix)

No number, defines it as relative, using the row or column holding the formula.

Numbers in [] act as an offset relative to the cell holding the formula.

R[-1]C[2] means 1 row above, 2 columns right (negative numbers offset left or up, positive down or right).

As with A1 references, you can mix the absolute and relative parts of the ranges, which is what happens when you write the formula with vba, it would appear to use the most relevant format based on how the range is specified.

Maybe jonmo will be kind enough to elaborate on any points I may have missed / mis-described.

 

[rguy84]

Thanks for that