Problem with selection blank or non blank

  • Thread starter Thread starter Jaan
  • Start date Start date
J

Jaan

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
 
Try the below.

Sub Mac()

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

Set myrange = Range("N8:N" & Lrow)
myrange.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _
"=IF(RC[-1]>0,TODAY(),"""")"
myrange.Value = myrange.Value
myrange.NumberFormat = "d-mmm"
End With

End Sub

If this post helps click Yes
 
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
 
I think your macro's error is nothing but ordinary result. after you run
your macro first time, all of your blank cells is filled with a formula.
so, when you run the macro again, your macro can't find any blank cells.
the result is no blank cells were found. One way to avoid this error,
use "On Error goto ....".

Keiji
 
change this
Lrow = .Range("b8" & Rows.Count).End(xlup).Row
to
lRow = .Cells(Rows.Count, "N").End(xlUp).Row

Joel said:
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 said:
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
Click to expand...
Click to expand...
 
Just a typo...

Lrow = .Range("b8" & Rows.Count).End(xlup).Row
Drop the 8
Lrow = .Range("b" & Rows.Count).End(xlup).Row


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 said:
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
Click to expand...
Click to expand...
 
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


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
Click to expand...
 
Hi Joel

With you changes will advice that same error
I think issue are quotes, because when I select without macro rangeN8:n &
LRow I can'nt select blank cells with "edit- go to- special- blanks.But
nonetheless very thank for quick respond. And Jacob solution working.Thanks

"Joel" kirjutas:
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 said:
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
Click to expand...
Click to expand...
 
Hi Jacob

Thank you for quick respond.It working. I like you solution without
copy-paste.

Best regards



Jacob Skaria" kirjutas:
Try the below.

Sub Mac()

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

Set myrange = Range("N8:N" & Lrow)
myrange.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _
"=IF(RC[-1]>0,TODAY(),"""")"
myrange.Value = myrange.Value
myrange.NumberFormat = "d-mmm"
End With

End Sub

If this post helps click Yes
---------------
Jacob Skaria


Jaan said:
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
Click to expand...
Click to expand...
 
Jaan; thought to add an explanation for the macro posted earlier.

"" is not considered by excel as a blank cell. Try ISBLANK() on a cell for
which you have used the formula and returned = "".. Try copying this cell and
pastespecial>Values.. Still it is not considered as a blank cell; and that is
the reason why your macro returns the error which says there are no blank
cells.. The macro pasted earlier deal this by replacing the values
myrange.Value = myrange.Value...

If this post helps click Yes
---------------
Jacob Skaria


Jacob Skaria said:
Try the below.

Sub Mac()

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

Set myrange = Range("N8:N" & Lrow)
myrange.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _
"=IF(RC[-1]>0,TODAY(),"""")"
myrange.Value = myrange.Value
myrange.NumberFormat = "d-mmm"
End With

End Sub

If this post helps click Yes
---------------
Jacob Skaria


Jaan said:
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
Click to expand...
Click to expand...
 
Hi Jaan,

And This :
'----------------------------------------
Sub test()
Dim LastRow As Long, Rg As Range, X As Variant
On Error Resume Next
With ActiveSheet
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
With .Range("N8:N" & LastRow)
Set Rg = .SpecialCells(xlCellTypeBlanks)
With Rg
.NumberFormat = "d-mmm"
.Formula = "=IF(" & Rg(1).Offset(-1).Address(0, 0) & ">0,TODAY(),"""")"
End With
X = .Value
.Value = X
End With
End With
End Sub
'----------------------------------------

"Jaan" <[email protected]> a écrit dans le message de groupe de discussion :
(e-mail address removed)...
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
 
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


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
Click to expand...
Click to expand...
 
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.
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


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
Click to expand...
Click to expand...
Click to expand...
 
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.
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
Click to expand...
Click to expand...
Click to expand...
 
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
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.
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top