View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Patrick Molloy[_2_] Patrick Molloy[_2_] is offline
external usenet poster
 
Posts: 1,298
Default 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