Need help on Basic Two Dimensional Dynamic Array

Hello,

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

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

Try:

Please Login or Register  to view this content.

Regards, TMS

You're welcome. Thanks for the rep.