SIMPLE VBA/EXCEL PROBLEM

ks06

New Member
Joined
Oct 19, 2006
Messages
40
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
 

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
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
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
Joined
Oct 19, 2006
Messages
40
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
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Oct 19, 2006
Messages
40
Tried 'Application.WorksheetFunction.Max' but could not get it to work in my code
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 19, 2006
Messages
40
Thanks for the reply, im was using the wrong code completely....i am a near-novice....thanks again!
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top