Need help on Basic Two Dimensional Dynamic Array

[chrixton]

Hello,

I am new to VBA. I have managed to write the following code:

Sub Testing()

Dim Array(8 To 37, 3 To 23) As Long

Dim i As Integer, j As Integer

For i = 8 To 37

   For j = 3 To 23
   Array(i, j) = Cells(i, j)
   If Array(i, j) > 0 Then
   Cells(i, 2).Value = Cells(6, j).Value
   Exit For
   End If
   Next j

Next i

End Sub

The purpose of this code is to loop through each row in a table (C8:W37), find the first non-zero value, and copy paste the value in row 6 of that column into the same row but in column 2. The above code works just fine for what I want it to do.

The problem is that the number of the rows in the table may change. So instead of Array(8 To 37, 3 To 23) I would want it to be like Array(8 To x, 3 To 23), where x would equal = ActiveSheet.Range("C8").End(xlDown).Row

However, I cannot seem to accomplish this. Also for another purpose I would like to adapt this code to reverse the loop so that it work backwards like lets say Array(8 To 37, 23 To 3), but I cannot seem to accomplish this either. I am stuck and I don't know how to proceed to resolve these issues.

Any assistance would be highly appreciated.