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

 

[MARK858]

Click the formula tab then click Name Manager, find SchucoDescriptionText. What is in the Scope column?

Also what do the 2 formula below give you?

=ROWS(SchucoDescriptionText)*COLUMNS(SchucoDescriptionText)
=COUNTA(SchucoDescriptionText)

 

[Kenneth Hobson]

If the named range is not in the sheet where you run the code, be sure to prefix the Range with the Worksheet. e.g.

'Sheet1 code, range named SchucoDescriptionText is in Sheet2
Sub Test()
  Debug.Print Sheet2.Range("SchucoDescriptionText").Address(external:=True)
End Sub

Select and Activate don't work in Sheet code per se. Seems like I got it to work once but it is best to be more explicit. My usual practice is to avoid Select and Activate and such when possible. If you want to use such, do it in a Module, not Sheet code. You can then call the routine from a Sub if needed.

If needed, I can show you how to add Error checks if you use the SpecialCells() method.

 

[JoeMo]

Hi Joe.

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:

Guess I missed that point. See Kenneth's post (#12) about qualifying the sheet your named range is in.

 

[Marhier]

Ah right, I understand what you mean!
No, my code wasn't in the sheet the range was in... Doing what Kenneth suggested and qualifying the sheet name worked a treat.

Just amended my original code to the following:

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

Thank you to everyone who contributed.
This has helped my understanding massively.
I'm slowly getting there.

Have a good day all.
Regards
Martin