Is R1C1 notation better than A1, B1 etc in VBA?

[psulion01]

I ask because i seem to have an easier time with the syntax when using code such as Cells(r, c) and other variants like that (eg Cells(lastRow, 3), Cells(2, LastCol+1), especially when I have loops, incrementing rows/colmns, etc. However there are times when I know exactly what cell i want (ie A7) or I know the row but am dealing with incrementing columns. Is it bad form to mix notations within code? Is it possible to reference a cell in the a1, B1 etc notation if my desired column is an integer, such as from a counter?

For example, if my code sets col=3, how do I convert it from 3 to C?

Probably very simple or not really a desired practice but I'm hung up on this. Reverted to the R1C1 notation in my current project but it seems hard to follow when debugging... i keep finding myself counting the columns to make sure I know where the code is as it executes.

Thanks!

 

[Norie]

If you have the column number and used to using Cells then stick with that.

You can convert it to the alphanumeric and you'll find plenty of functions to do it.

But, and this is my opinion, it's not worth doing the conversion.

It's pretty hard to convert to a 2-character column name, and with the new versions of Excel you would need something that converted to 3-characters.

PS Just noticed your last comment - if you want to find the column number from the alphanumeric you can just try something like this
in the immediate window.

? Range("AAA1").Column

Which gives 703.

 

[hiker95]

psulion01,

For example, if my code sets col=3, how do I convert it from 3 to C?

Try:

Dim strColName As String

strColName = Replace(Cells(1, 3).Address(0, 0), 1, "")

'Or:

strColName = Split(Columns(3).Address, "$")(2)

Reverted to the R1C1 notation in my current project but it seems hard to follow when debugging

This can be true.

I use Range, Cells, and R1C1. It depends on the requirement.

 

[Rasm]

Here is another way of getting a number of a column converted to a string

Astr = Split(Cells(1, ColumnNumber).Address, "$")(1)

 

[xenou]

I use Cells and Range both frequently (preferring Cells generally). Range has the nice feature of kicking into intellisense though.

I rarely use R1C1 in code unless I'm writing formulas to cells. It's worth learning and using ... but alas I haven't bothered. There's an argument to be made for using R1C1 in normal everyday Excel use (Formulas are easily audited as they all look the same).

Interestingly, cells also allows for this syntax:

Cells(1, "A")

Of course Ranges can also be numeric:

Range("1:1")

Accept the mystery.

 

[psulion01]

Thanks to all for the replies. Perhaps I am a bit confused... i was thinking the Cells(r, c) was R1C1 notation in VBA. Seems this is something else.

I guess it's OK to use all methods in code then, depending on the situation. I'm trying to learn programming on my own (messed with vba a bunch but now reading up on C and then java) and one of my main concerns is writing code that is clean, efficient and generally accepted by educated developers (in the event that I use some of these skills at my job).

As always the free resources on this board have been an invaluable resource... thanks!
Mike