VBA subscript out of range error 3

[musha26]

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

 

[Loomah]

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?
How do I get the best answers?​

 

[SkipVought]

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,

_[red][/red]

 

[musha26]

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.

 

[PHV]

Use the Formula property of the range instead of the value.

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

 

[SkipVought]

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,

_[red][/red]

 

[musha26]

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)

 

[SkipVought]

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,

_[red][/red]

 

[musha26]

Thanks for all your help, that finally worked sorry had to rush to work, didn't have time to say thanks earlier.