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

  • Thread starter Thread starter tur13o
  • Start date Start date
T

tur13o

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.
 
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
 
Back
Top