Dave Peterson
9/22/2009 2:53:00 PM
I couldn't think of a better way.
EagleOne@discussions.microsoft.com wrote:
>
> 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
>
> Dave Peterson <petersod@verizonXSPAM.net> wrote:
>
> >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.
> >
> >
> >EagleOne@discussions.microsoft.com wrote:
> >>
> >> 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
--
Dave Peterson