Run-time Error on Finding Next Blank Cell in Named Range

[Marhier]

Good afternoon.
I've written a bit of code that copies the cell you've got selected, switches over to a schedule and then pastes that value in the next available cell in a named range of cells.

My code is as follows:

Sub CopytoSchucoSchedule()
    Dim FindBlank As Range
    Application.ScreenUpdating = False
    ActiveSheet.Select
    Selection.Copy
    With SchucoSchedule
        .Visible = -1
        .Select
    Set FindBlank = Range("SchucoDescriptionText").Find(What:="", lookat:=xlWhole)
    FindBlank.Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    End With
    With SchucoSchedule
        .Visible = 2
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

When running this code, I'm getting the following error:

"Run-time error '1004':
Application-defined or object-defined error"


By stepping through (F8), it seems to fail at the following line of code:

Set FindBlank = Range("SchucoDescriptionText").Find(What:="", lookat:=xlWhole)

If someone could provide a solution to this, it would be greatly appreciated.
Thank you.
Regards
Martin

 

[JoeMo]

I don't see anything wrong with that line. If there are no blank cells in the named range you would get a run-time error 91, not 1004. Where is the variable "SchucoSchedule" defined?

 

[Marhier]

Hi JoeMo, thanks for the reply.
"SchucoSchedule" is just the hardcode name of the worksheet I'm pasting the information to... In that worksheet, I have the named range of cells "SchucoDescriptionText" where I want it to find the next available blank cell.

Thank you.
Regards
Martin

 

[Kenneth Hobson]

Maybe check if the found range was not nothing before using it?

e.g.

'Find methods, https://www.excelcampus.com/vba/find-last-row-column-cell/
Sub tFind()
  Dim r As Range
  Set r = Range("A10:C10").SpecialCells(xlCellTypeBlanks)
  If Not r Is Nothing Then Debug.Print r(1).Address
End Sub

 

[Marhier]

Thanks Kenneth.
I changed my code as you suggested to the following:

Sub CopytoSchucoSchedule()
    Dim r As Range
    Application.ScreenUpdating = False
    ActiveSheet.Select
    Selection.Copy
    With SchucoSchedule
        .Visible = -1
        .Select
    Set r = Range("SchucoDescriptionText").SpecialCells(xlCellTypeBlanks)
    If Not r Is Nothing Then Debug.Print r(1).Address
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    End With
    With SchucoSchedule
        .Visible = 2
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Still getting the following error:

"Run-time error '1004':
Application-defined or object-defined error"

Thank you.
Regards
Martin

 

[JoeMo]

Thanks Kenneth.
I changed my code as you suggested to the following:

Sub CopytoSchucoSchedule()
    Dim r As Range
    Application.ScreenUpdating = False
    ActiveSheet.Select
    Selection.Copy
    With SchucoSchedule
        .Visible = -1
        .Select
    Set r = Range("SchucoDescriptionText").SpecialCells(xlCellTypeBlanks)
    If Not r Is Nothing Then Debug.Print r(1).Address
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    End With
    With SchucoSchedule
        .Visible = 2
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Still getting the following error:

"Run-time error '1004':
Application-defined or object-defined error"

Thank you.
Regards
Martin

What line is highlighted when you get the error?

 

[Marhier]

Set r = Range("SchucoDescriptionText").SpecialCells(xlCellTypeBlanks)

 

[Marhier]

I should also mention that due to other functions I have running in this document, this particular piece of code has to sit within a worksheet and not a module.
My original code works fine in a module, just not in a worksheet.


Regards
Martin

 

[JoeMo]

Set r = Range("SchucoDescriptionText").SpecialCells(xlCellTypeBlanks)

That error arises b/c there are no truly blank cells in the named range "SchucoDescriptionText". Kenneth neglected to handle this error in his code.

 

[Marhier]

Hi Joe.
I'm not sure I understand what you mean.
I can assure you the cells within the named range "SchucoDescriptionText" are blank to start with, but want the code to copy across the selection from one sheet to the next available blank cell in that named range.

As I mentioned before, the following code works fine when in a 'Module', but not within a worksheet - which for this particular supplier - needs to stay in the worksheet:

Sub CopytoSchucoSchedule()
    Dim FindBlank As Range
    Application.ScreenUpdating = False
    ActiveSheet.Select
    Selection.Copy
    With SchucoSchedule
        .Visible = -1
        .Select
    Set FindBlank = Range("SchucoDescriptionText").Find(What:="", lookat:=xlWhole)
    FindBlank.Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    End With
    With SchucoSchedule
        .Visible = 2
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub