Difficulty with a range object

[Odin]

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.

 

[Norie]

Mark

Try this.

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

 

[Odin]

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]

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]

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]

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.