Share via

Run-Time Error '1004': Method 'Range' Of Object '_Global' Failed VBA

Anonymous
2022-03-23T19:53:29+00:00

Hi

I have a Macro on Word that in some part of the program it pulls information from an Excel File, the issue is that I used to get the error 1004 but not always, used to be at the second or third time that I run the Macro.

The Textbox48 is the file path which is correct because file always gets open.

"Program" if the sheet name which also is correct.

I call the workbook to be visible and sheet to be active as recommended prior to set rng.

Below is the portion of code where is pointed the error end some screenshots:

Pointing on "Set rng"

Pointing on "Dim rng"

Pointing on "Range"

The question still why this error is intermittent.

Hope you can help me.

Microsoft 365 and Office | Word | Other | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. HansV 462.4K Reputation points MVP Volunteer Moderator
    2022-03-23T21:37:59+00:00
    1. If Program is the sheet name, enclose it in quotes:

    Set xlSheet = xlBook.Worksheets("Program")

    1. Specify the sheet that Range belongs to:

    Set rng = xlSheet.Range("A2:A50")

    2 people found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-03-23T22:20:50+00:00

    Thank you for your help, it fix the problem.

    Just to clarify, Program y a variable that contain the Sheet's name, so quotes aren't necessary.

    0 comments
  2. HansV 462.4K Reputation points MVP Volunteer Moderator
    2022-03-23T22:28:49+00:00

    > Just to clarify, Program y a variable that contain the Sheet's name, so quotes aren't necessary.

    OK, that wasn't clear to me.

    0 comments