# SIMPLE VBA/EXCEL PROBLEM

#### ks06

##### New Member
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????

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

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

#### Norie

##### Well-known Member
I don't think an array can have a 0 dimension.

Also you shouldn't have Select after range.

Try this.
Code:
``````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

##### MrExcel MVP
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

##### New Member
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

##### Well-known Member

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

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

#### Norie

##### Well-known Member
How did you try it?

And how did it not work?
Code:
``````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

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

Replies
1
Views
637
Replies
2
Views
126
Replies
2
Views
90
Replies
4
Views
975
Replies
9
Views
186

1,136,272
Messages
5,674,757
Members
419,525
Latest member
helensesc

### 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.

### Which adblocker are you using?

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

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