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

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.
 
J

John Green

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Loop through cells to find a string and copy/paste the matching column to a new wkbk 3
Referring to ranges as Cell, Range, and variables 6
Find from just certain sheets in a workbook 4
Populating one sheet with specific data from another sheet (I'm dumb) 0
converting a for next loop into a for each loop 2
VBA - specify where data is placed on new sheet 5
For Each loop recheck 6
Problem with sheets that mirror each other 10

Top