Excel Run error - No Cells were found!

[Phil Payne]

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.

'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"

	A	B	C	D
1	1	dfgh	dfhdfh	dfhh
2	2	WBS Element	Establishment	Cost Element
3	3	WBS 001	Abercorn	504307
4	4			504401
5	5			504402
6	6			Result
7	7	WBS 003	Anderson	504307
8	8			504402
9	9			Result
10	10	WBS 099	Achcraig	504126
11	11			504307
12	12			Result
13	13	WBS 102	Balornock	504122
14	14			504129
15	15			504307
16	16			504402
17	17			Result

<tbody>
</tbody>

 

[VoG]

Try

Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("B3:C" & LR).SpecialCells(xlCellTypeBlanks)
    .FormulaR1C1 = "=R[-1]C"
End With

 

[Phil Payne]

Try

Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("B3:C" & LR).SpecialCells(xlCellTypeBlanks)
    .FormulaR1C1 = "=R[-1]C"
End With

HelloVoG

Thanks for the prompt response. Unfortunately the same error occurs.
Perhaps there a way to trap this with If/Then/Else statements. I'm trying that but can't seem to get it!
Regards,

 

[VoG]

Sounds like your "blanks" are not actually blank. This should avoid the error but may not actually do anything

Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
On Error Resume Next
With Range("B3:C" & LR).SpecialCells(xlCellTypeBlanks)
    .FormulaR1C1 = "=R[-1]C"
End With
On Error GoTo 0

 

[Phil Payne]

Hello again VoG

I just tried to use on error resume next and it gave rise to other problems. So I removed it and run my macro (with your code) again and, believe it or not, it worked.
I must have 'corrected' something when I removed the 'On Error Resume Next'

I will be trying to break this by running it a few times.

Thanks for your help.

 

[Phil Payne]

Thanks VoG

I have incorporated your latest with the on error codes and have run with no problems.

When I input On Error Resume Next I did not have On Error goto 0.

Cheers.

Sounds like your "blanks" are not actually blank. This should avoid the error but may not actually do anything

Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
On Error Resume Next
With Range("B3:C" & LR).SpecialCells(xlCellTypeBlanks)
    .FormulaR1C1 = "=R[-1]C"
End With
On Error GoTo 0

 

[hiker95]

Team,

When I tried this to change the formulae to values, range B16:C17 displayed #N/A:

Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("B1:C" & LR).SpecialCells(xlCellTypeBlanks)
  .FormulaR1C1 = "=R[-1]C"
  .Value = .Value
End With
End Sub

The following update to the code gets rid of the #N/A's:

Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("B1:C" & LR)
  With .SpecialCells(xlCellTypeBlanks)
    .FormulaR1C1 = "=R[-1]C"
  End With
  .Value = .Value
End With