Method Range of Object Workbook failed

[Student7]

I'm trying to set a range that would be every other cell in range C4:FE4, ie C4,E4,G4...

I don't know a better way to do it, so I'm doing this:

Set rngMyRange = wsMySheet.Range("C4,E4,G4, ..., FE4")

This way of doing it works, up to a point. As soon as I have more than 67 cells listed in my list, I get the error "Method Range of Object Workbook Failed"

Is there a better way to set this every other cell range?

[Norie]

What do you want to do with this range?

Perhaps there's another approach?

[Student7]

I'm reading user entered data out of the cells in the range stepping through each cell in the range. 7 worksheets (days of the week) all layed out the same. basic construct is this:

For each ws in Application.ActiveWorkbook
set rngMyRange = ws.Range(("C4,E4,G4, ..., FE4")
For each rngThisCell in rngMyRange
DoStuff()
Next rngThisCell
Next ws

The workbook is data entry for employee time. Sheets are days of the week, Rows are employees, rngMyRange is basically the payroll cost code to put hours on. At the end of the week, after all data entry is done, the programming steps through the sheets, employees, and cost codes and creates another workbook that contains timecard sheets - one worksheet for each employee.

[Norie]

So why not just loop through the cells rather than trying to create a range including them all?

[Student7]

What I'm thinking is something like this, is there a better way to 'loop through cells'?

For each ws in Application.Activeworkbook
For Y = 1 to 200 step 2
strCellValue = ws.Range("C4").Cells(1,Y).Value
DoStuff(strCellValue)
Next Y

I think this would work. I'm still wondering about the original error. There's got to be some clever way to create a large range of alternating cells without typing all the cell addresses out.

[Bob Phillips]

Why not put the loop in DoStuff and step through the cells there 2 cells at a time.

[mikerickson]

This kind of thing might be faster than a loop of "write to one cell" instructions.
Dim myArray as Variant

For i = 1 to myRange.Columns.Count/2 step 2
myArray(i) = myRange.Cells(1,i)
myArray(i+1) = newData(i+1)
Next i

myRange.Value = myArray

[rory]

Seems a bit pointless, but you could do this:
Dim rng As Range, lngCol As Long
Set rng = Range("C4")
For lngCol = 5 To 161 Step 2
Set rng = Union(rng, Cells(4, lngCol))
Next lngCol

[Student7]

Thankyou all for the input. I see that using a range in this case isn't necessary and I'll loop through the cells, something like the example above.

Rory, thanks for providing the example. I'm (obviously) just learning VBA and I didn't know about the Union funtion. I knew there had to be something like this, but I was looking in the wrong place. I was looking for some method on a range object, like rng.AddRange() .