PDA

View Full Version : VBA Error Subscript Out of Range



celes
08-25-2008, 11:46 AM
Hello,

I keep getting the error subscript out of range for the bolded lines of code

For Each Row In Worksheets("Input Dates").Range("Start_Date")
'benchmark data
For k = 1 To j
z = Application.VLookup(Worksheets("Input Dates").Range("Start_Date").Cells(k), Worksheets("TempData").Range("A2:B1000"), 1, False)
If Not IsError(z) Then
benchmarkArr(1, k) = z
benchmarkDates(1, k) = Worksheets("Input Dates").Range("Start_Date").Cells(k).Value
Else
z = Application.index(Worksheets("TempData").Range("A2:B1000"), Application.Match(Worksheets("Input Dates").Range("Start_Date").Cells(k), Worksheets("TempData").Range("Benchmark_Dates"), -1), 2)
benchmarkArr(1, k) = z
benchmarkDates(1, k) = Application.index(Worksheets("TempData").Range("A2:B1000"), Application.Match(Worksheets("Input Dates").Range("Start_Date").Cells(k), Worksheets("TempData").Range("Benchmark_Dates"), -1), 1)
End If


Can anyone help me? z is a variant and the two arrays are variant arrays.

Edited by Aussiebear: When posting code to the forum, please use the VBA button to wrap your code. It makes the code so much easier to read.

jrdnoland
08-25-2008, 12:19 PM
Have you tried stepping through the code? You should be able to see where the error occurs. Is it in the beginning or the end? Your range is out on one end or the other I think. What is J set to?

It's hard for me to tell without having the whole thing to check.

darthobra
08-25-2008, 12:31 PM
celes,

Are the arrays 2 dimensional arrays? If they are...

Dim benchmarkArr() As Variant


then they are not clearly defined. If you are creating a single value array then

Dim benchmarkArr(1 to 10) As Variant

and ReDim if K > 10.

Hope this helps.

Darth

celes
08-25-2008, 01:10 PM
Hello,

Thank you for your help. J is an integer that has been set to the number of values in a range. The two arrays are two-dimensional and they have been redimmed. The odd thing is that I get a value for z, but when it comes to putting z into the arrays, I get a subscript out of range error..

Kenneth Hobs
08-25-2008, 01:16 PM
It is hard to see what the subscript would be. You should probably step through the code as advised or use debug.print to print the subscript values so that you are getting what you expect.

You can post a short example if you like.

darthobra
08-25-2008, 01:39 PM
How did you define your Arrays? Are they Base1 or 0?

If benchmarkArr(1, k) = z is out of range then you should check the value of K when it is out of range. This code indicates that you are setting and resetting the value(s) at r1 with each iteration. Is that what was intended?

Darth

jrdnoland
08-25-2008, 02:39 PM
What happens if you try

For k = 1+ 1 To j - 1

instead of For k = 1 To j

Try changing the bounds and see if that helps point to the error.