Multiple Cells

[Kramer]

Hello. I have a sub that iterates over some cells in my worksheet. So
something like this:

CurRow=1
Do While (CurRow <= 1000)
CurCol = 1
Do While (CurCol <=1000)
Worksheet.Cells(CurRow, 1) .Value = ComputeValue(CurRow, CurCol)
CurCol = CurCol + 1
Loop
CurRow = CurRow + 1
Loop

I want to select some of those cells during the iteration, but don't
have good way to select a range. For instance I want to be able to do
something like:

Worksheets.Range(CurCol & CurRow & ":" & CurCol + 37 & CurRow +
29).Select

But that that wouldn't work because CurCol is a numeric value not the A
B C ... value that Range expects.

Any idea?

 

[Don Lloyd]

Hi,
Addendum, meant to include in the previous post.

Worksheet.Cells . . . . . . . won't work
If it's the activesheet you can use
Activesheet.Cells . . . . . . . if you want.

Don

 

[Kramer]

I wasn;t really asking about how to refer to a Worksheet. I guess that
I did post it wrong. What I meant to say was this:

OutWorksheet = Worksheets("some name")
CurRow=1
Do While (CurRow <= 1000)
CurCol = 1
Do While (CurCol <=1000)
OutWorksheet.Cells(CurRow, 1) .Value = ComputeValue(CurRow,
CurCol)
CurCol = CurCol + 1
Loop
CurRow = CurRow + 1
Loop

I want to select some of those cells during the iteration, but don't
have good way to select a range. For instance I want to be able to do
something like:
OutWorksheet.Range(CurCol & CurRow & ":" & CurCol + 37 & CurRow +
29).Select

So basically, I'm looking for a way to build a range object given
numerical indices CurCol and CurRow. Any help?

 

[Dave Peterson]

First, if you're using xl2003 or below, you don't have 1000 columns in your
worksheet.

and you can do stuff like:

with worksheets("sheetnamehere")
.select
.range(.cells(currow,curcol),.cells(currow+29,curcol+37)).select
end with

Or you could use .Resize()

with worksheets("sheetnamehere")
.select
.cells(currow,curcol).resize(29+1,37+1).select
'or
.cells(currow,curcol).resize(30,38).select
end with

Remember, you have to be on the active sheet to do .select's.

 

[Kramer]

Thanks a lot.

I guess that I don't have 1000 cols (it was just an example), but I do
have a lot.

Thanks again.

 

[Don Lloyd]

I assume you didn't see my first post !
Don

I wasn;t really asking about how to refer to a Worksheet. I guess that
I did post it wrong. What I meant to say was this:

OutWorksheet = Worksheets("some name")
CurRow=1
Do While (CurRow <= 1000)
CurCol = 1
Do While (CurCol <=1000)
OutWorksheet.Cells(CurRow, 1) .Value = ComputeValue(CurRow,
CurCol)
CurCol = CurCol + 1
Loop
CurRow = CurRow + 1
Loop

I want to select some of those cells during the iteration, but don't
have good way to select a range. For instance I want to be able to do
something like:
OutWorksheet.Range(CurCol & CurRow & ":" & CurCol + 37 & CurRow +
29).Select

So basically, I'm looking for a way to build a range object given
numerical indices CurCol and CurRow. Any help?