Reference cells using variables

  • Thread starter Thread starter Reggie
  • Start date Start date
R

Reggie

Hi and TIA! I'm exporting data from MSAcess to Excel.
The data is appended to my worksheet from many different
sources(queries). The problem I'm having is that after I
transfer the data I want to select ranges of
cells and format them. The data doesn't always end up in
the exact same location every time as the number of
records varies. I can run the code listed below the first
time and everything works fine, however, if I close out of
excel and try to run it again, after all object references
instances of excel are destroyed and new ones created, I
get the following error:("Error # 1004' Method 'Cells' of
Object_Global' Failed). Here's the procedure I'm
executing from Access2K:

'Send Recordset to Excel
objWS1.Range("A2").CopyFromRecordset rstDetail
Set DailyRange = objWS1.UsedRange
intRowCount = DailyRange.Rows.Count
intRowCount = intRowCount - 2
With objWS1
.Range(Cells(intRowCount, 1), Cells _
intRowCount, 15)).Merge
End With
Set objWS1 = Nothing
Basically I need to know how to select a range of cells
using variables as the row & column indexes to select an
area. Is it possible? Any help is appreciated. Thanks
for your time!!
 
'Send Recordset to Excel
objWS1.Range("A2").CopyFromRecordset rstDetail
Set DailyRange = objWS1.UsedRange
intRowCount = DailyRange.Rows.Count
intRowCount = intRowCount - 2
With objWS1
.Range(Cells(intRowCount, 1), Cells _
intRowCount, 15)).Merge
End With
Set objWS1 = Nothing
Basically I need to know how to select a range of cells
using variables as the row & column indexes to select an
area. Is it possible? Any help is appreciated. Thanks
for your time!!
--
Click to expand...

Reggie


Not sure why you get the error, but you already have variable rows and
columns in your code.This bit

With objWS1
.Range(Cells(intRowCount, 1), Cells _
intRowCount, 15)).Merge
End With

just re-use this techniwue.

Regards

Bob
 
Thanks Bob. I don't know why I get the error either. What gets me is that
it runs fine the first time I run it, but then If I try to run it again I
get the error until I reboot my machine and run it again. Strange! Thanks
for your time though!

--
Reggie

"Half this game is 90% mental."

----------
 
Back
Top