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