SIMPLE VBA/EXCEL PROBLEM

[ks06]

I get a 'Run-time error 13' message with the code below on the line:
stockdataLengtha = UBound(stockdataa, 0)
What is wrong and how can i rectify this????
Thank you in advance.


Option Explicit
Sub lenght()
Dim stockdataa As Variant
Dim stockdataLengtha As Integer
Sheets("Prob1").Select
stockdataa = Range("A1:A20").Select
stockdataLengtha = UBound(stockdataa, 0)
MsgBox "Length is is " & stockdataLengtha
End Sub

 

[Norie]

I don't think an array can have a 0 dimension.

Also you shouldn't have Select after range.

Try this.

Sub lenght()
Dim stockdataa As Variant
Dim stockdataLengtha As Long
    stockdataa = Sheets("Prob1").Range("A1:A20")
    stockdataLengtha = UBound(stockdataa, 1)
    MsgBox "Length is is " & stockdataLengtha
End Sub

 

[Oaktree]

Welcome to the board.

I think you can reduce this to:

Sub lenght()
MsgBox "Length is "& Sheets("Prob1").Range("A1:A20").Cells.Count
End Sub

 

[ks06]

Great, thanks guys for both suggestions.
I wanted to use the UBound function as I want to scroll through the data and find the largest stock value, if I were to sue the first code suggested can I add to it to do this?
Many thanks again.

 

[Norie]

Why not just use Application.WorksheetFunction.Max?

That should get the maximum value.

If you need the position of the maximum value you would need another approach.

 

[ks06]

Tried 'Application.WorksheetFunction.Max' but could not get it to work in my code

 

[Norie]

How did you try it?

And how did it not work?

Sub lenght()
Dim stockdataa As Variant
Dim stockdataLengtha As Long
    stockdataa = Sheets("Prob1").Range("A1:A20")
    stockdataa = Application.WorksheetFunction.Transpose(stockdataa)
    stockdataLengtha = Application.WorksheetFunction.Max(stockdataa)
    MsgBox "Length is is " & stockdataLengtha
End Sub

 

[ks06]

Thanks for the reply, im was using the wrong code completely....i am a near-novice....thanks again!