R1C1 Issue populating cells within Excel

[collierd]

Hello

I have the following macro running within Excel:

 Dim rowReference As Integer
    Dim columnCounter As Integer
    Dim budgetCodeCounter As Integer
   
    rowReference = 2
    
    For budgetCodeCounter = 1 To 4
        For columnCounter = 1 To 12
            Cells(rowReference, 1) = "=budget!R1C2"
            Cells(rowReference, 2) = "=TEXT(budget!R4C" & columnCounter - 1 & "], ""dd/mm/yyyy"")"
            Cells(rowReference, 3) = "=budget!R[" & budgetCodeCounter + 4 & "]C1"
            Cells(rowReference, 4) = "=budget!R5C[" & columnCounter - 3 & "]"
 
            rowReference = rowReference + 1
        Next columnCounter
    Next budgetCodeCounter

I can't quite work out why data is being populated the way it is.
Cells(rowReference, 3) = "=budget!R[" & budgetCodeCounter + 4 & "]C1" populates using incremental rows (row 7 through to row 57)
This should have 12 instances of the first cell followed by 12 of the next etc

Cells(rowReference, 2) = "=TEXT(budget!R4C[" & columnCounter - 1 & "], ""dd/mm/yyyy"")" uses -1
yet Cells(rowReference, 4) = "=budget!R5C[" & columnCounter - 3 & "]" uses -3 to get the same columns on a different row

Is it using some sort of offset?
How does this work?

Thanks

Damian.

 

[GlennUK]

I can't quite work out why data is being populated the way it is.
Cells(rowReference, 3) = "=budget!R[" & budgetCodeCounter + 4 & "]C1" populates using incremental rows (row 7 through to row 57)
This should have 12 instances of the first cell followed by 12 of the next etc

Cells(rowReference, 2) = "=TEXT(budget!R4C[" & columnCounter - 1 & "], ""dd/mm/yyyy"")" uses -1
yet Cells(rowReference, 4) = "=budget!R5C[" & columnCounter - 3 & "]" uses -3 to get the same columns on a different row

Is it using some sort of offset?
How does this work?

You don't seem to have realised how relative referencing works in Excel. Square brackets in a R1C1 formula indicate relative offsets.

Does that explain it?

Cheers, Glenn.

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

 

[collierd]

It does Glenn

I can't see what this is relative to
I will have a look into this

Should I be using the method
Could I perform this without relative referencing?

 

[SkipVought]

Hi,

A Range Object has many PROPERTIES. Two are the VALUE property and the FORMULA property. I think that the complier must be ASSUMING the VALUE property...

Cells(rowReference, 1)[b].formule[/b] = "=budget!R1C2"

Skip,
[sub]
[red][/red]
[/sub]

 

[SkipVought]

.formula

Skip,
[sub]
[red][/red]
[/sub]

 

[collierd]

Looking good now thanks
Using relative referencing method

Damian.