Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

VBA subscript out of range error 3

Status
Not open for further replies.

musha26

Technical User
May 21, 2007
8
GB
Hi
I have just started learning VBA and I have an error coming up "subs cript out of range error)

I just have 2 workesheets in a workbook, one with a table of numbers, and I'm trying to get the minimum value within teh range and insert it in cell d5 of the previous worksheet. here's my code.

Sub MinValue()

Worksheets("Answers").Range("d8").Value = "=min(MyRange5)"

End Sub
 
hi
first thing to check is does the worksheet "Answers" exist? ie is " Answers" or "Answers " etc?

;-)
If a man says something and there are no women there to hear him, is he still wrong? [ponder]
How do I get the best answers?
 


Hi,

Subscript out of range usually means that you are referencing an unknown object, in this case, the Answers worksheet.

Are you certain that you have a sheet named Answers WITHOUT SPACES?

Or it could be that you had another workbook open and that OTHER workbook, with no Answers worksheet, had the focus?

Skip,

[glasses] [red][/red]
[tongue]
 
Thanks Skipvought and loomah.

You were right I had the wrong spelling of the worksheet name. Now My code runs with zero as the annswer, but that is not the minimum value in the specified range.
 
Use the Formula property of the range instead of the value.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 




Are you sure that you are referring to the correct range?

1. In the Name Box, select Range5

2. Right-Click in the StatusBar at the bottom and select Min

Is that your value?

Skip,

[glasses] [red][/red]
[tongue]
 

I have changed the range to cell reference instead of naming the range. to
"=min(b2:k1829)"
My problem now is this range refers to a range in a different worksheet, ie(data) to the one the result is going in to ie(answer) So ewould I have to call the
"=worksheets("data").range min(b2:k1829)
 




Why would you not use a named range???
[tt]
"=range min(data!b2:k1829)"
[/tt]
just like it would appear in the forumla bar.

And as PHV suggested, assign this value to the Formula property.

Skip,

[glasses] [red][/red]
[tongue]
 
Thanks for all your help, that finally worked sorry had to rush to work, didn't have time to say thanks earlier.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top