"bpascal123" wrote in message
...
Hi,

I have started learning vba for excel. Now, I feel fine with cells
workarounds in a loop or else. But when it comes to a range, I can't
find the reason why if I'm following the excel object model it's not
working.

[ ]

'B - this is working but i'd rather use the cell method as i find it
easier to play with variables for indices in a loop
'wks2.Range("A1:C10") = wks1.Range("A1:C10").Value

[ ]

GS answered your question "Why is this working but that is not?" (Using
Object qualifiers - which you in fact had partly correct in your earlier
question about aligning data in columns.)

I'd like to comment on the differences between using cell indexing and
the A1 reference method you referred to just now.

You are absolutely correct that using Cell(row, column) indexing is much
more suited to programmatic manipulation than the alphabetic based A1
style of referencing.

I'm coming to the conclusion, however, that there are times when using
the A1 reference results in more readable code. Consider:

ColAin = .Transpose(Range("A1:A" & Cells(Rows.Count, _
"A").End(xlUp).Row))

compared with:

With WorksheetFunction
C1in = .Transpose(Range(Cells(1, 1), _
Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)))
End With

or even:

LastC1in = Cells(Rows.Count, 1).End(xlUp).Row
With WorksheetFunction
C1in = .Transpose(Range(Cells(1, 1), Cells(LastC1in, 1)))
End With

The major distinction there, I think, is the difference between A1 style
notation (the Excel user interface default) and RC (Row, Column)
notation that is more naturally suited to programmatic indexing (but not
commonly seen in the user interface.)

Conclusion: use the notation that makes the most sense to you for the
task at hand.

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)