Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA over-write identical worksheet-ranged-cells only if receiving sheet's cell is empty
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA over-write identical worksheet-ranged-cells only if receiving sheet's cell is empty
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA over-write identical worksheet-ranged-cells only if receivingsheet's cell is empty
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(xlCellTypeCon stants) 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. 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA over-write identical worksheet-ranged-cells only if receiving sheet's cell is empty
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 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(xlCellTypeCon stants) 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. 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA over-write identical worksheet-ranged-cells only if receivingsheet's cell is empty
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Write a macro that hides rows based on empty cells | Excel Discussion (Misc queries) | |||
find empty cells in a column then append row that empty cell is in | Excel Programming | |||
Adding/Removing Rows to Referenced Ranged in Worksheet | Excel Programming | |||
Adding up cell values only with identical info in other cells | Excel Worksheet Functions | |||
How do I link an identical cell address across multiple worksheet. | Excel Worksheet Functions |