Mehod 'Range' of object '_Global" failed ...runtime error 1004

%3CLINGO-SUB%20id%3D%22lingo-sub-1461826%22%20slang%3D%22en-US%22%3EMehod%20'Range'%20of%20object%20'_Global%22%20failed%20...runtime%20error%201004%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1461826%22%20slang%3D%22en-US%22%3E%3CP%3EAlthough%20I%20have%20successfully%20ran%20similarly%20coded%20macros%2C%20this%20particular%20one%20is%20driving%20me%20crazy.%26nbsp%3B%20Below%20is%20the%20code%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20MoonSpeedNew()%3C%2FP%3E%3CP%3EWorksheets(%22Sheet4%22).Activate%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%20color%3D%22%23333333%22%3ESet%20r1%20%3D%20Range(%22MoonVariables%22)%3C%2FFONT%3E%3C%2FSTRONG%3E%3CBR%20%2F%3ESet%20r2%20%3D%20Range(%22day**bleep**spvalue%22)%3CBR%20%2F%3ESet%20r3%20%3D%20Range(%22Resultvariables%22)%3C%2FP%3E%3CP%3EDim%20Highlimit%20As%20Single%3CBR%20%2F%3EDim%20oldlimit%20As%20Single%3CBR%20%2F%3EDim%20Lowlimit%20As%20Single%3CBR%20%2F%3EDim%20limit%20As%20Single%3CBR%20%2F%3EDim%20Diff%20As%20Single%3CBR%20%2F%3EDim%20imax%20As%20Integer%3CBR%20%2F%3EDim%20i%20As%20Integer%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20macro%20hangs%20up%20at%20the%20statement%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ESet%20r1%20%3D%20Range(%22MoonVariables%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Ewith%20the%20above%20runtime%20error%201004%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20checked%20the%20Range%20for%20%3CSTRONG%3EMoonVariables%3C%2FSTRONG%3E%20under%20Name%20Manager%20in%20excel%20...looks%20okay%20to%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1461826%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
New Contributor

Although I have successfully ran similarly coded macros, this particular one is driving me crazy.  Below is the code,

 

Sub MoonSpeedNew()

Worksheets("Sheet4").Activate

Set r1 = Range("MoonVariables")
Set r2 = Range("day**bleep**spvalue")
Set r3 = Range("Resultvariables")

Dim Highlimit As Single
Dim oldlimit As Single
Dim Lowlimit As Single
Dim limit As Single
Dim Diff As Single
Dim imax As Integer
Dim i As Integer

 

The macro hangs up at the statement 

Set r1 = Range("MoonVariables")

with the above runtime error 1004

 

I checked the Range for MoonVariables under Name Manager in excel ...looks okay to me.

 

 

2 Replies
Highlighted

Sorry, I found my mistake.  I was referring to the wrong sheet.  My apologies. @Jmaheshri 

@Jmaheshri 

 

There may be one of the two issues....

 

  1. The named range has the worksheet scope and when you use that range in the code and code gets executed, the sheet which it belongs to is not the active sheet. e.g. if the named range was defined for Sheet1 and it has a worksheet scope and when you run the code, Sheet2 is active at that moment, the code will throw an error. OR
  2. The named range is made dynamic with the help of Indirect function or the named range returns an error.