Phil Payne
Board Regular
- Joined
- May 17, 2013
- Messages
- 131
- Office Version
- 365
- Platform
- Windows
Hello all,
I have a worksheet which is an output from a database. (Example below)
I need to populate the empty cells in columns B and C with the data in the cells immediately above and the code I have (also below) works well if run in isolation.
However I have now combined this code with other code into one macro and I receive an error (Debug) message "No cells were found" on line 3 'selection.specialcells'.
Is there a way to avoid this? I cannot work out how to specify the last row of the range, as the number of rows will change (increase) over time.
Can anyone assist please?
Thanks in anticipation.
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]dfgh[/TD]
[TD]dfhdfh[/TD]
[TD]dfhh[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD] WBS Element[/TD]
[TD]Establishment[/TD]
[TD]Cost Element[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD]WBS 001[/TD]
[TD]Abercorn[/TD]
[TD]504307[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]504401[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]504402[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]7[/TD]
[TD]WBS 003[/TD]
[TD]Anderson[/TD]
[TD]504307[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD]504402[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]10[/TD]
[TD]WBS 099[/TD]
[TD]Achcraig[/TD]
[TD]504126[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD]504307[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]13[/TD]
[TD]WBS 102[/TD]
[TD]Balornock[/TD]
[TD]504122[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD]504129[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD]504307[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD]504402[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD]Result[/TD]
[/TR]
</tbody>[/TABLE]
I have a worksheet which is an output from a database. (Example below)
I need to populate the empty cells in columns B and C with the data in the cells immediately above and the code I have (also below) works well if run in isolation.
However I have now combined this code with other code into one macro and I receive an error (Debug) message "No cells were found" on line 3 'selection.specialcells'.
Is there a way to avoid this? I cannot work out how to specify the last row of the range, as the number of rows will change (increase) over time.
Can anyone assist please?
Thanks in anticipation.
Code:
'MsgBox "Fill empty cells in columns B C with entry above"
Range("a3:d3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]dfgh[/TD]
[TD]dfhdfh[/TD]
[TD]dfhh[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD] WBS Element[/TD]
[TD]Establishment[/TD]
[TD]Cost Element[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD]WBS 001[/TD]
[TD]Abercorn[/TD]
[TD]504307[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]504401[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]504402[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]7[/TD]
[TD]WBS 003[/TD]
[TD]Anderson[/TD]
[TD]504307[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD]504402[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]10[/TD]
[TD]WBS 099[/TD]
[TD]Achcraig[/TD]
[TD]504126[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD]504307[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]13[/TD]
[TD]WBS 102[/TD]
[TD]Balornock[/TD]
[TD]504122[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD]504129[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD]504307[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD]504402[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD]Result[/TD]
[/TR]
</tbody>[/TABLE]