Cells(i, "B") not working

[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

 

[Rowan Drummond]

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

 

[Guest]

Create range names for each of the columns
select a cxolumn
Insert > Name > Define ...

- - Mark

PS See my excel stuff at:
http://www.geocities.com/excelmarksway

"You might find something if you look in the right place"

 

[geofferrington]

Thanks guys. That was a real help and is much appreciated.

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

Geoff