Cells V Range

  • Thread starter Thread starter CJ
  • Start date Start date
C

CJ

Hi

I have an interesting problem. I am populating an Excel
template from an Access database using
..Range(.Cells(8, 9), .Cells(8, 10)).select
..Range(.Cells(8, 9), .Cells(8, 10)).CopyFromRecordset rs

All appears to work fine. When I open the Excel sheet, it
doesn't seem like any of the data has been populated. BUT
if I go to my immediate window, then cells(8,9) shows me
the value that should have been pasted in, but range("L9")
shows me the old value that should have been overwritten

I was under the impression that Cells and Range were a
synonymous way of refering to the same thing???

Any help would be appreciated.
 
Don't select or activate the range...

set WS = Worksheets(somesheetnameornumber)
With WS

..Cells(8, 9).CopyFromRecordset rs

End With

----> assuming that there are records in the RS!!

and yes, you can also use
..Range("L8").CopyFromRecordset rs


You are correct Range and cells are sysnonymous
Range(Range("B2"),Range("E7"))
Range(Range("B2"),cells(7,"E"))
Range(Range("B2"),cells(7,5))
Range(Cells(2,2),cells(7,"E"))
Range(cells(7,"E"),Range("B2"))

Patrick Molloy
Microsoft Excel MVP
 
Cj said:
if I go to my immediate window, then cells(8,9) shows me
the value that should have been pasted in, but range("L9")
shows me the old value that should have been overwritten
Click to expand...

Cells(8, 9) is cell "I8" - row 8, column 9
Not surprising that L9 was not changed!

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup
 
Patrick Molloy wrote
You are correct Range and cells are sysnonymous
Range(Range("B2"),Range("E7"))
Click to expand...

OT, but can I reference this one this way?
Range("B2,E7")
 
David said:
OT, but can I reference this one this way?
Range("B2,E7")
Click to expand...

Range("B2,E7") is a range of 2 cells, B2 and E7.
Range("B2","E7"), Range("B2:E7") and Range(Range("B2"), Range("E7"))
are all equivalent and give a range of 48 cells.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup
 
Bill Manville wrote
Range("B2,E7") is a range of 2 cells, B2 and E7.
Range("B2","E7"), Range("B2:E7") and Range(Range("B2"), Range("E7"))
are all equivalent and give a range of 48 cells.
Click to expand...

Thanks for the lesson. I actually had a need to check only two cells in an
Edit|Replace routine, so was curious when I saw these offerings.
 
Back
Top