Using UsedRange as limits in a For Each loop but for cells on another sheet ?
 

Excel 2000 VBA.

How could I modify something like

For Each MyCell In MySheet.UsedRange
-stuff
Next

So that during the loop MyCell points to a cell in a sheet other than
MySheet but in the same row, col position on the other sheet. I want to
set-up a grid of formla on a new sheet (my code to do this is where -stuff
is). Each cell of my grid will reference it's counterpart in MySheet, but I
will not know how much data MySheet will have on t until it is used at run
time.

Using UsedRange as limits in a For Each loop but for cells on another sheet ?
 

The following code places a formula in each cell in Sheet2 that has a corresponding value in Sheet1, referring to that value:

For Each oCell In Sheet1.UsedRange
Sheet2.Range(oCell.Address) = "=" & oCell.Address(External:=True)
Next oCell


--

John Green - Excel MVP
Sydney
Australia


"tur13o" wrote in message ...
Excel 2000 VBA.

How could I modify something like

For Each MyCell In MySheet.UsedRange
-stuff
Next

So that during the loop MyCell points to a cell in a sheet other than
MySheet but in the same row, col position on the other sheet. I want to
set-up a grid of formla on a new sheet (my code to do this is where -stuff
is). Each cell of my grid will reference it's counterpart in MySheet, but I
will not know how much data MySheet will have on t until it is used at run
time.

Using UsedRange as limits in a For Each loop but for cells on another sheet ?
 

Star!

"John Green" wrote in message
...
The following code places a formula in each cell in Sheet2 that has a
corresponding value in Sheet1, referring to that value:

For Each oCell In Sheet1.UsedRange
Sheet2.Range(oCell.Address) = "=" & oCell.Address(External:=True)
Next oCell


--

John Green - Excel MVP
Sydney
Australia


"tur13o" wrote in message
...
Excel 2000 VBA.

How could I modify something like

For Each MyCell In MySheet.UsedRange
-stuff
Next

So that during the loop MyCell points to a cell in a sheet other than
MySheet but in the same row, col position on the other sheet. I want to
set-up a grid of formla on a new sheet (my code to do this is
where -stuff
is). Each cell of my grid will reference it's counterpart in MySheet,
but I
will not know how much data MySheet will have on t until it is used at
run
time.