Need help on Basic Two Dimensional Dynamic Array

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.

Originally Posted by chrixton

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

Try replace "Dim Array(8 To 37, 3 To 23) As Long" with this two lines.

    Dim lngArray() As Long
    ReDim lngArray(8 To ActiveSheet.Range("C8").End(xlDown).Row, 3 To 23)

Originally Posted by chrixton

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.

Try change "For j = 3 To 23" to this.

For j = 23 To 3 Step -1

note: I've change the variable name to "lngArray" instead of "Array" by trying to avoid using reserved word in VBA


_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

This is how to do what you want. You do not need to use an array, or store values somewhere to be able to process them. This code will start at the bottom of column C, and find the first non-zero value in each row - the number of columns can vary as well.

Sub Testing2()

Dim lngR As Long
Dim rngC As Range

For lngR = Cells(Rows.Count, "C").End(xlUp).Row To 8 Step -1
   Set rngC = Cells(lngR, 3)
   While rngC.Value = 0
      Set rngC = rngC.Offset(0, 1)
   Wend
   Cells(lngR, 2).Value = Cells(6, rngC.Column).Value
Next lngR

End Sub

If it is possible that a row will not have a non-zero value in it, then you should make sure to include a way out:

Sub Testing3()

Dim lngR As Long
Dim rngC As Range

For lngR = Cells(Rows.Count, "C").End(xlUp).Row To 8 Step -1
   Set rngC = Cells(lngR, 3)
   While rngC.Value = 0
      If rngC.Column = Activesheet.UsedRange.Columns.Count Then GoTo NotFound
      Set rngC = rngC.Offset(0, 1)
   Wend
   Cells(lngR, 2).Value = Cells(6, rngC.Column).Value
NotFound:
Next lngR

End Sub

Try:

Sub Testing()

Dim vArray()
Dim i As Long, j As Long, lLR As Long
lLR = Range("C8").End(xlDown).Row

ReDim vArray(8 To lLR, 3 To 23)

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

End Sub

Regards, TMS

You're welcome. Thanks for the rep.