ExcelBanter - Problem with selection blank or non blank

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Problem with selection blank or non blank (https://www.excelbanter.com/excel-programming/433095-problem-selection-blank-non-blank.html)

Jaan

Problem with selection blank or non blank

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

Jacob Skaria

Problem with selection blank or non blank

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" 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

joel

Problem with selection blank or non blank

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

keiji kounoike

Problem with selection blank or non blank

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

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

Patrick Molloy[_2_]

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

Dave Peterson

Problem with selection blank or non blank

Just a typo...

Lrow = .Range("b8" & Rows.Count).End(xlup).Row
Drop the 8
Lrow = .Range("b" & Rows.Count).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

--

Dave Peterson

Dave Peterson

Problem with selection blank or non blank

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

Jaan

Problem with selection blank or non blank

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" 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

Jaan

Problem with selection blank or non blank

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" 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

Jacob Skaria

Problem with selection blank or non blank

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
pastespecialValues.. 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" wrote:

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" 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

michdenis

Problem with selection blank or non blank

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" a écrit dans le message de groupe de discussion :
...
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

Jaan

Problem with selection blank or non blank

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

Problem with selection blank or non blank

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

Jaan

Problem with selection blank or non blank

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

keiji kounoike

Problem with selection blank or non blank

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

All times are GMT +1. The time now is 10:06 AM.