MrExcel Publishing
Your One Stop for Excel Tips & Solutions

tiny bit of VBA code needed

Posted by Chris D on February 07, 2002 8:13 AM

Hi all,

I have:

Dim a(13) As Double

and I find myself regularly changing the size of this depending on the size of a range

what would be the correct syntax so that the 13 is actually always the value of cell B2 in sheet 1

(where B2 is just a count of numbers in column A)

many thanks

Posted by Todd on February 07, 2002 8:15 AM

Have you tried Dim a(Sheets("Sheet1").Range("B2")) as double

Posted by DK on February 07, 2002 8:23 AM

Hi Chris,

You need to use Redim as Dim requires a constant

Dim a() As Double
ReDim a(Sheets("Sheet1").Range("B2")) As Double


Posted by Chris D on February 07, 2002 8:36 AM

Excellent, thanks guys (nt)