I think Dave's code is more robust than Jacob's. There is a case of
causing error in Jacob's if not in your data.

Keiji

Jaan wrote:
Hi Dave
It seems your suggestion to use Edit|Replace works perfectly.
Now I have next problem:
whose code I will use - your or Jacobs. Both codes are ok for me. But don't
worry, it's my problem. Thank you once more.

Best Regards

"Dave Peterson" kirjutas:

Are you sure that the range you've specified actually empty--they can't contain
formulas that evaluate to "", either.

And if they used to be formulas that evaluated to "" and you converted to
values, they're still not empty!

But you can fix those offending cells.

Select the range
Edit|Replace
what: (leave empty)
with: $$$$$
replace all

Then do it again:
Edit|Replace
what: $$$$$
with: (leave empty)
replace all

This will change those cells that look empty (values that used to be formulas
that evaluated to "") to really empty cells.

If this fixes the problem, that code could be included in your code.

And remember that if you have spacebar characters in the cells, then the cells
aren't empty either.

Jaan wrote:
Hi Dave
It's not first time You help me. Thank you.
In this issues I am tried again and again, but all the time msgBox showing
me "no blanks!". Is something wrong?

Best regards

"Dave Peterson" kirjutas:

I'd use:

Option Explicit
Sub testme()


Dim Lrow As Long
Dim myRange As Range
Dim myBlanks As Range
Dim myArea As Range

With ActiveSheet
Lrow = .Cells(.Rows.Count, "B").End(xlUp).Row
Set myRange = .Range("N8:N" & Lrow)
End With

Set myBlanks = Nothing
On Error Resume Next
Set myBlanks = myRange.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If myBlanks Is Nothing Then
MsgBox "no blanks!"
Else
myBlanks.NumberFormat = "d-mmm"
myBlanks.FormulaR1C1 = "=IF(RC[-1]0,TODAY(),"""")"
For Each myArea In myBlanks.Areas
myArea.Copy
myArea.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Next myArea
End If

End Sub



Jaan wrote:
Hi All

I need help in resolving my error code. I would like to find the blank cells
in my range and then insert formula. Macro works correctly only first time.
If I run macro next time, it advice me next error:
Run-time error '1004': no cells were found.
My code you can see below

Dim Lrow As Long
With ActiveSheet
Lrow = Range("b8:B" & Rows.Count).End(xlDown).Row

Set myrange = Range("N8:N" & Lrow)
myrange.Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=IF(RC[-1]0,TODAY(),"""")"

myrange.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.NumberFormat = "d-mmm"
End With

If """" means "blank", then what is wrong in my code

Best Regards
--

Dave Peterson

--

Dave Peterson