I'm writing this VBA code that loops through a defined range and finds the filled cells within in, then it refers 5 different indexes to each cell, and to check if each cell is truly indexed with these values, I write my array to a worksheet.
I can manage the loop to work and print exactly the number of filled cells - but the problem is that it prints out the same exact value for each cell even though they are totally different.
Here is how my worksheet looks like:
And what it prints out is:
Using this code:
As you can see, even though the code loops through the entire range - only the last cell, Cells(6,8) is printed with its indexes.
NOTE: I can only do it using a 2D array of number of molecules x 5 indexes (uni project) so please do not try to change it, I just need to understand the problem of why I am only printing out one value for all cells.
I'd appreciate any help.
I can manage the loop to work and print exactly the number of filled cells - but the problem is that it prints out the same exact value for each cell even though they are totally different.
Here is how my worksheet looks like:
And what it prints out is:
Using this code:
Code:
Sub FindMol()
Dim lMolecules As Integer
lMolecules = 5 'in this case, number of filled cells
Dim Molecules() As Variant
ReDim Molecules(1 To lMolecules, 1 To 5) 'this is a 2d array to fill in a table of number of molecules x 5 indexes
Dim ColInd As Integer
Dim c As Range
For i = 1 To UBound(Molecules, 1) 'number of molecules
For j = 1 To UBound(Molecules, 2) '5 indexes: x,y,dx,dy,color
Randomize
dX = Int((H - L + 1) * Rnd() + L) 'speed vector x
dY = Int((H - L + 1) * Rnd() + L) 'speed vector y
'moves thorugh the defined range, in this case 10x10
For Each c In Worksheets("Main Screen").Range(Cells(1, 1), Cells(lHeight, lWidth))
If c.Interior.ColorIndex <> xlNone Then
ColInd = c.Interior.ColorIndex
'printing the values found to the worksheet
Molecules(i, 1) = i 'giving each molecule a number in order
Worksheets("Parameters").Range("C2:" & Cells(i + 1, 3).Address) = Molecules(i, 1)
Molecules(j, 1) = c.Column
Worksheets("Parameters").Range("D2:" & Cells(lMolecules + 1, 4).Address) = Molecules(j, 1)
Molecules(j, 2) = c.Row
Worksheets("Parameters").Range("E2:" & Cells(lMolecules + 1, 5).Address) = Molecules(j, 2)
Molecules(j, 3) = dX
Worksheets("Parameters").Range("F2:" & Cells(lMolecules + 1, 6).Address) = Molecules(j, 3)
Molecules(j, 4) = dY
Worksheets("Parameters").Range("G2:" & Cells(lMolecules + 1, 7).Address) = Molecules(j, 4)
Molecules(j, 5) = ColInd
Worksheets("Parameters").Range("H2:" & Cells(lMolecules + 1, 8).Address) = Molecules(j, 5)
End If
Next
Next j
Next i
End SubAs you can see, even though the code loops through the entire range - only the last cell, Cells(6,8) is printed with its indexes.
NOTE: I can only do it using a 2D array of number of molecules x 5 indexes (uni project) so please do not try to change it, I just need to understand the problem of why I am only printing out one value for all cells.
I'd appreciate any help.
