Use variables in R1C1 Formula

[dmkennard2]

Hi,
I am using code to extract data from Access into a spreadsheet.

I am having problems inserting formula at the end of certain data columns that totals the column above. The first row will always be row 8 but the last row will always change.

Am i missing something in my formula code below as it fails each time i run it.

lngLastRow = xlSheet.Range("A65536").End(xlUp).Row
        lngLastColumn = xlSheet.Range("Z7").End(xlToLeft).Column
Set xlRange = xlSheet.Range(Cells(lngLastRow + 1, lngLastColumn - 7), xlSheet.Cells(lngLastRow + 1, lngLastColumn - 7))

' Insert formulas

With xlRange
    .Value = "Results"
End With

Set xlRange = xlSheet.Range(Cells(lngLastRow + 1, lngLastColumn - 6), xlSheet.Cells(lngLastRow + 1, lngLastColumn - 6))

With xlRange
    .FormulaR1C1 = "=SUM(R[" & 8 - lngLastRow & "]C:R[" & lngLastColumn & "]C)"
End With

Thanks in advance.
Dazz

 

[xlbo]

what line does it fail on?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[dmkennard2]

Hi,
Thanks for getting back to me. Looks like i have solved it with the code below.

With xlRange
    .FormulaR1C1 = "=SUM(R[" & 8 - lngLastRow - 1 & "]C:R[" & lngLastRow - lngLastRow - 1 & "]C)"
End With

Probably not the best way of doing it, but it seems to be working.

dazz

 

[GlennUK]

Well, one thing is that:

lngLastRow - lngLastRow - 1

is the same as

-1

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Rofeu]

I think the main issue here is a misunderstanding of absolute and relative references with R1C1-style.

No matter in what row your formula ends up in the following will always work:

=SUM(R8C:R[-1]C)

numbers in between square brackets are offsets relative to the cell the function is in.

Another point:

Set xlRange = xlSheet.Range(Cells(lngLastRow + 1, lngLastColumn - 6), xlSheet.Cells(lngLastRow + 1, lngLastColumn - 6))

seems to me the same thing as:

Set xlRange = xlSheet.Cells(lngLastRow + 1, lngLastColumn - 6)

Cheers,

Roel