Cells(i, "B") not working

  • Thread starter Thread starter geofferrington
  • Start date Start date
G

geofferrington

Hi All,
I am using 2002 and have the following problem.

The following piece of code works perfectly:

'=== Calculate "comma delimited" =============================
With Range("AC12:AC" & lc)
For i = 1 To rn
..Cells(i) = _
..Cells(i, -26) & "," & _
..Cells(i, -25) & "," & _
..Cells(i, -24) & "," & _
..Cells(i, -23) & "," & _
..Cells(i, -22) & "," & _
..Cells(i, -21) & "," & _
..Cells(i, -20) & "," & _
..Cells(i, -19)
Next i
End With

Column -26 is column "B" and
Column -25 is column "C" etc...

So, I am given to understand that the following should work too:

'=== Calculate "comma delimited" =============================
With Range("AC12:AC" & lc)
For i = 1 To rn
..Cells(i) = _
..Cells(i, "B") & "," & _
..Cells(i, "C") & "," & _
..Cells(i, "D") & "," & _
..Cells(i, "E") & "," & _
..Cells(i, "F") & "," & _
..Cells(i, "G") & "," & _
..Cells(i, "H") & "," & _
..Cells(i, "I")
Next i
End With

But it doesn't and it would make life really simple if it did.

Any suggestions and comments would be greatly appreciated.

Geoff
 
Your problem stems from the fact that you are referencing the cells
within the With Range("AC12:AC" & lc)...End With block. This means that
when you refer to .cells(i,"B") you are actually refering to the second
column from the point of view of your range i.e column AB.

So one option is to keep the code using the .cells(i,-26) structure or
remove the With...End With and use something like:
For i = 12 To rn + 11
Cells(i, 29) = _
Cells(i, "B") & "," & _
Cells(i, "C") & "," & _
Cells(i, "D") & "," & _
Cells(i, "E") & "," & _
Cells(i, "F") & "," & _
Cells(i, "G") & "," & _
Cells(i, "H") & "," & _
Cells(i, "I")
Next i

Hope this helps
Rowan
 
Thanks guys. That was a real help and is much appreciated.

BTW Mark.... I am in Sydney - perhaps a coffee sometime?

Geoff
 
Back
Top