VBA over-write identical worksheet-ranged-cells only if receiving sheet's cell is empty

  • Thread starter Thread starter EagleOne
  • Start date Start date
E

EagleOne

2003-2007

VBA over-write identical worksheet-ranged-cells only if receiving sheet's cell is empty

Facts:

Sheet1 has range Named "FirstDataSet" of cells A1-Z100
Cells in "FirstDataSet" contain data in about 20% of the cells

Sheet2 has range Named "SecondDataSet" of cells A1-Z100
Cells in "SecondDataSet" contain data in about 20% of the cells -
... but not the same data in similar ranged-cells.

I realize that I could:

For each myCell in FirstDataSet
If myCell.value <> ""
myCell.value = SecondDataSet.Range(same range as myCell).value
End if
Next myCell

Is there a smarter/efficient way to use broader range base than Each myCell?

My guess is no - because the evaluation is on a cell by cell basis??

But I am open to learn!

TIA EagleOne
 
If the cells are filled with values (not formulas), you could limit your loop to
those constant cells.

Dim myRng as range
dim myCell as range
with worksheets("Sheet1")
set myrng = nothing
on error resume next
set myrng = .range("A1:Z100").cells.specialcells(xlCellTypeConstants)
on error goto 0
end with

if myrng is nothing then
'no constant cells in that range
else
for each mycell in myrng.cells
mycell.value = worksheets("Sheet2").range(mycell.address).value
next mycell
end if

(untested, uncompiled. Watch for typos.)

If you have formulas (or a mixture of both), you can use xlcelltypeformulas and
do the same thing again.

And if you want to do it to empty cells (I just read your follow up!), use
xlCellTypeBlanks.

It's like selecting the range and hitting F5, Special and choosing from that
dialog.
 
When no one else dares, Dave does it!

In this case, the data is all constants and that up-front limitation of the "universe" is an
excellent proposal. Humbling as to why I did not think of that.

I thought that there may have been a unique approach to that type of cell updating.

Thanks
 
I couldn't think of a better way.

When no one else dares, Dave does it!

In this case, the data is all constants and that up-front limitation of the "universe" is an
excellent proposal. Humbling as to why I did not think of that.

I thought that there may have been a unique approach to that type of cell updating.

Thanks
Click to expand...
 
Back
Top