Run-time error '9': At a loss for where to go

noslenwerd

Board Regular
Joined
Nov 12, 2019
Messages
76
Office Version
  1. 365
Hello,

I am running into an issue any time I try to run a macro with my excel "dashboard" when I have any other workbook open. That includes completely blank workbooks.

Below is the error, and also the last line of code below is where the error seems to happen. I even tried changing the name to the actual Sheet (Sheet4), and still no luck.

subcript.JPG


VBA Code:
Option Explicit

Sub CreateWordDocuments()
Dim CustRow, CustCol, FinalCol As Long
Dim DocLoc, TagName, TagValue, TagRow, TemplName, FileName, TempFileName, ReviewLoc, WebsiteURL As String
Dim WordDoc As Word.Document, WordApp As Word.Application
Dim WordContent As Word.Range

Call Acc_Expand
Call ReqFields
Call TrimURL
Call populatetopics

    TempFileName = Sheets("DSSWorksheet").Range("comp")
    WebsiteURL = Sheets("DSSWorksheet").Range("C40")
    MsgBox "Please allow 1-2 minutes while the review for " & TempFileName & " is processed." & vbCrLf & vbCrLf & "The Dashboard will now minimize, and Word will open when the review is ready", vbExclamation
    ActiveWindow.WindowState = xlMinimized
    
    With Sheets("ReviewContent")

Thanks as always for your help.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,692
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
As Mark said, if you are referring to a sheet in the same workbook as the code, use ThisWorkbook.Sheets("ReviewContent") rather than just Sheets("ReviewContent")
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,776
I'm guessing your actual problem is where you dimmed your variables. For example
Code:
Dim DocLoc, TagName, TagValue, TagRow, TemplName, FileName, TempFileName, ReviewLoc, WebsiteURL As String
Putting "String" on the end doesn't dim all your variables as strings. Some of these variables seem like they must be strings or they will crash (ie filename etc) dependent upon how U use them. XL will do whatever if your not specific. Not sure if this helps or why your code crashes where it does? I'm confident that Joe, Mark and Rory will help U find a resolution to this but starting with the basics is usually good when U come across puzzling errors. HTH. Dave
 

noslenwerd

Board Regular
Joined
Nov 12, 2019
Messages
76
Office Version
  1. 365
As Mark said, if you are referring to a sheet in the same workbook as the code, use ThisWorkbook.Sheets("ReviewContent") rather than just Sheets("ReviewContent")
This worked perfectly. Thank you!

Is there a similar command for Word? This process opens Word and runs macros there as well. Will 'ThisDocument' work in a similar fashion?
 

noslenwerd

Board Regular
Joined
Nov 12, 2019
Messages
76
Office Version
  1. 365
I'm guessing your actual problem is where you dimmed your variables. For example
Code:
Dim DocLoc, TagName, TagValue, TagRow, TemplName, FileName, TempFileName, ReviewLoc, WebsiteURL As String
Would best practice be to do something like:

Dim DocLoc As String
Dim TagName As String
Dim TagValue As String
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,692
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
If your code is in Excel but automating word, then you should assign the document to a variable when you open it and then refer to that variable.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,261
Messages
5,635,135
Members
416,842
Latest member
Ateen4ever

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top