Ok. Here's my problem. I have a spreadsheet that I use which filters on 6 different filter criteria and copies the result set of each filter criteria to a separate worksheet. However, on occasion there are no results found for a given criteria.

What I want to do is when no results are found for a given criteria to return the words "No Data Found" to a cell on a different spreadsheet.

I'm trying to find the correct syntax for:

If SpecialCells = "" then

Sheet("Smith").Select
Range("C2").Select
ActiveCell.FormulaR1C1 = "No Data"

I do know that SpecialCells represents Filtered Records but that's about all I know what to do with that.

Any ideas?

Thanks guys.

Now Jakester, I'm sure I've shown you many times in the past how to take out the "selecting" and "Activating" in your macros. Tsk, tsk.

When I apply Autofilters, after turning on the filter, I test to see if any rows are showing, and only copy if there is data showing past the row1 titles.


Dim LR As Long

'Code here to turn on your filter

LR = Range("A" & Rows.Count).End(xlUp).Row
If LR > 1 Then
'Code here to copy
Else
'Code here to write a message instead of copying
End If

Now Jakester, I'm sure I've shown you many times in the past how to take out the "selecting" and "Activating" in your macros. Tsk, tsk.

When I apply Autofilters, after turning on the filter, I test to see if any rows are showing, and only copy if there is data showing past the row1 titles.


Dim LR As Long

'Code here to turn on your filter

LR = Range("A" & Rows.Count).End(xlUp).Row
If LR > 1 Then
'Code here to copy
Else
'Code here to write a message instead of copying
End If

JB - I don't recall the tip on taking out Selecting and Activating... I know I would've remembered because I'm still using the darn things. So how can you take those out and still refer to them?

Thanks, again.

By fully addressing your commands with parent/child relationship, you can issue commands to any sheet, any cell/range and insert values/formulas without having once activated or selected anything, much less even brought the sheet up onscreen.

VBA is not human, it has no need to physically touch a cell prior to using it.

Your code, probably recorded, so the recorder shows your human actions:

Sheet("Smith").Select
Range("C2").Select
ActiveCell.FormulaR1C1 = "No Data"


The same code merged down into a fully addressed single command:

Sheet("Smith").Range("C2") = "No Data"


Notice, I even took out the erroneous FormulaR1C1 since you were inserting a text string, not a formula?

If you'd like to post your workbook and the rest of your macro, I'm sure we'll find a LOT of instances to improve efficiency and shorten code at the same time.

I use the macro recorder every day, so I'm not scolding. The code recorded is a starting point only, we have to tweak it down into proper VBA syntax.