Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Start Cell B1 then find first blank cell, insert subtotal, next non blank, then next blank, sutotal cells in between......... | Excel Programming | |||
Pasting Selection In First Blank Row | Excel Discussion (Misc queries) | |||
Delete blank rows in selection | Excel Programming | |||
Inserting Blank Lines in Selection | Excel Programming | |||
Deleting blank cells in a selection | Excel Programming |