Macro cell reference help please

[dangerousdaveeg]

I have a spreadsheet with lots of data that is linked to other tabs in my spread sheet and I am looking for a way to copy a range of cells and then increase the reference in the pasted cells, so for example<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I have data In Range “C3:C5” with references like<o></o>
<o> </o>
C3 - “='Raw Data'!A$4”<o></o>
C4 - “='Raw Data'!A$17”<o></o>
C5 – “='Raw Data'!A$30”<o></o>
<o> </o>
I might be missing something really obvious here but when I copy these three cells and paste them three cells down the reference does not change to “B” <o></o>
<o> </o>
I want the pasted cells to have <o></o>
<o> </o>
C6 - “='Raw Data'!B$4”,<o></o>
C7 - “='Raw Data'!B$17”<o></o>
C8 – “='Raw Data'!B$30”<o></o>
<o> </o>
I have to do this for every work day in the year so would prefer a macro that may make my life a lot easier, I have several macro’s in my workbook but this one I can’t seem to find no matter how much searching and testing I have tried.<o></o>
<o> </o>
So basically what I need is to<o></o>
Select range C3:C5<o></o>
Copy<o></o>
Move 3 cells down (which I can do)<o></o>
Paste (but increase the cell reference letter up one but keep row the same)<o></o>

 

[taurean]

Considering you want to repeat it 365 times, this shall work:

Public Sub SetAddress()
Const csSheet As String = "'Raw Data'!" 'Change to suit
Dim lRow As Long, lCol As Long
    Range("C3").Formula = "=" & csSheet & "$A$4"
For i = 1 To 365
    lRow = 4 + (i Mod 3) * 13
    lCol = Int(i / 3) + 1
    Range("C" & i + 3).Formula = "=" & csSheet & Cells(lRow, lCol).Address
Next i
End Sub

 

[Norie]

Try this.

Dim I As Long
Dim col As Long
For I = 1 To 365 Step 3
    col = col + 1
    Range("D" & I + 2).FormulaR1C1 = "='Raw Data'!R4C" & col
    Range("D" & I + 3).FormulaR1C1 = "='Raw Data'!R17C" & col
    Range("D" & I + 4).FormulaR1C1 = "='Raw Data'!R30C" & col
Next I