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