Within an IF statement, populate an array with values

[cfdh_edmundo]

Hi,

I've got a very basic if statement in VBA that color codes cells and writes text in an adjacent cell when conditions are met. The code is as follows and works exactly as I need:

If Cells(rowCount, 15).Value = Cells(rowCount - 1, 15).Value And Cells(rowCount, 16).Value <> Cells(rowCount - 1, 16).Value Then
    Cells(rowCount, 17).Value = "Flagging Point"
    Cells(rowCount, 15).Interior.ColorIndex = 36
    Cells(rowCount, 16).Interior.ColorIndex = 36
End If

What I would like to do is, in addition to the above, put Cells(rowCount, 15).Value into a one dimensional array if the condition is met. I havent yet declared the array.

Any help would be great.

Many thanks,

Chris

 

[Fluff]

Is this part of a loop with "rowcount" being the counter?

 

[cfdh_edmundo]

Yes, exactly that, my if statement is within a for loop which loops on rowCount

NumRows = Range("A1", Range("A1").End(xlDown)).Rows.Count

For rowCount = 14 To NumRows - 1

'(then here is the IF statement and some other IF statements)

Next rowCount

 

[cfdh_edmundo]

I think I may have figured out a workaround:


I declared flagString As String and then added this bit into the IF statement

If Cells(rowCount, 15).Value = Cells(rowCount - 1, 15).Value And Cells(rowCount, 16).Value <> Cells(rowCount - 1, 16).Value Then
    Cells(rowCount, 17).Value = "Flagging Point"
    Range(Cells(rowCount - 1, 15), Cells(rowCount, 16)).Interior.ColorIndex = 36
    flagString = flagString & Cells(rowCount, 15).Value & "~"
 
End If

Then after the for loop ends, I add in this code:

flagArray = Split(flagString, "~")
MsgBox Join(flagArray, vbCrLf)

this seems to work!

 

[Fluff]

Ok, how about

NumRows = Range("A1", Range("A1").End(xlDown)).Rows.Count
ReDim Ary(1 To NumRows) As Variant
For RowCount = 14 To NumRows - 1

   If Cells(RowCount, 15).Value = Cells(RowCount - 1, 15).Value And Cells(RowCount, 16).Value <> Cells(RowCount - 1, 16).Value Then
       Cells(RowCount, 17).Value = "Flagging Point"
       Cells(RowCount, 15).Interior.ColorIndex = 36
       Cells(RowCount, 16).Interior.ColorIndex = 36
       i = i + 1
       Ary(i) = Cells(RowCount, 15).Value
   End If
Next RowCount