Difficulty with a range object

Odin

New Member
Joined
Jul 13, 2006
Messages
32
Hi Folks,

I am having some problems with a range object that I created. I am trying to assign a set of values from an array to this range object. Before assigning these values, I increment iRowCount by one to move the range one row lower in the spreadsheet. When I use the following code with the Select statement, it works fine:

Worksheets("EvtData").Select
Set EvtCurrentRow = Range(Cells(iRowCount, 1), Cells(iRowCount, EvtCols))

So here is the problem. Since the EvtData sheet may be “very hidden”, I cannot use the Select statement. I tried doing it as shown below, however I get a Runtime Error 1004 “Application-defined or object-defined error”.

Set EvtCurrentRow = Worksheets("EvtData").Range(Cells(iRowCount, 1), Cells(iRowCount, EvtCols))

Does anyone know how to get around this? Many thanks for your help.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
Mark

Try this.
Code:
With Worksheets("EvtData")
 Set EvtCurrentRow = .Range(.Cells(iRowCount, 1), .Cells(iRowCount, EvtCols))
 

Odin

New Member
Joined
Jul 13, 2006
Messages
32
Hi,

I just tried it


With Worksheets("EvtData")
Set EvtCurrentRow = .Range(Cells(iRowCount, 1), Cells(iRowCount, EvtCols))
EvtCurrentRow.Value = EvtTempArray
End With


and I still get the same error at the same place. Thanks for your help anyway.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
Mark

You didn't actually try what I posted.:)

The crucial thing is the dot qualifier . before Cells.

Without that VBA will assume you are referring to Cells on the currently active sheet, not your hidden sheet.
 

Odin

New Member
Joined
Jul 13, 2006
Messages
32
You were absolutely right! It worked! It is amazing what a couple of well placed dots can do.

Thanks for following up again and bringing it to my attention. Much appreciated.
 

Odin

New Member
Joined
Jul 13, 2006
Messages
32
You were absoutely right! It worked! It is amazing what a couple of well placed dots can do.

Thanks for following up again and bringing it to my attention. Much appreciated.
 

Forum statistics

Threads
1,141,677
Messages
5,707,777
Members
421,527
Latest member
Tamiwsw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top