Problem with selection blank or non blank
change this
Lrow = .Range("b8" & Rows.Count).End(xlup).Row
to
lRow = .Cells(Rows.Count, "N").End(xlUp).Row
"Joel" wrote:
xldown will end at the 1st blank. It think you need xlup. See changes
Dim Lrow As Long
With ActiveSheet
Lrow = .Range("b8" & Rows.Count).End(xlup).Row
Set myrange = .Range("N8:N" & Lrow)
with myrange
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _
"=IF(RC[-1]0,TODAY(),"""")"
.Copy
.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone,
SkipBlanks:=False, _
Transpose:=False
.NumberFormat = "d-mmm"
end with
End With
"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
|