VBA _Error 9 Subscript out of range

Question

Why do I get "Subscript out of range" on my line T(k) = Cells(k + 1, 4).Value - z?

Public Sub find()
Dim i, j, k, h As Integer
Dim T() As Double
Dim z As Double
Range("E1").Activate
i = ActiveCell.Row
j = ActiveCell.Column
While Not IsEmpty(Cells(i, j - 2).Value)
  z = Cells(i, j - 2).Value
  k = 0
 While Not IsEmpty(Cells(k + 1, 4).Value)
  T(k) = Cells(k + 1, 4).Value - z
  k = k + 1
 Wend
  For h = 0 To k
   If T(h) = Application.WorksheetFunction.Min(Abs(T(k))) Then
    Cells(i, j).Value = Cells(h + 1, 4).Value
   End If
  Next
 i = i + 1
Wend
End Sub

Possible duplicate of [Populating VBA dynamic arrays](https://stackoverflow.com/questions/8850984/populating-vba-dynamic-arrays) — Jean-François Corbett, Oct 13 '17 at 11:28
This question addresses your problem: https://stackoverflow.com/questions/8850984/populating-vba-dynamic-arrays/8851086#8851086 Make sure to read more than just the first answer! — Jean-François Corbett, Oct 13 '17 at 11:33

Jean-François Corbett · Answer 1 · 2017-10-13T11:26:56.523

0

At the point where you say T(k) = ..., your array T hasn't been allocated yet. There isn't any such thing as T(0) yet. Hence the "Subscript out of range" error.

Before indexing into T, you have to give T a size using ReDim. For example:

Dim T() As Double
ReDim T(0 to 123)  ' or whatever size you need

edited Oct 13 '17 at 11:26

answered Oct 13 '17 at 11:05

Jean-François Corbett

37,420
30
139
188

VBA _Error 9 Subscript out of range

1 Answers1