lost in my loop!!!!

[dcharland]

I'm trying to figure out what wrong with my loop?

This is the current code that I have:

If Myemailarray(LBound(Myemailarray), LBound(Myemailarray)) <> "" Then 'compare emails
                        For j = LBound(Myemailarray) To UBound(Myemailarray)
                                If Myemailarray(j, 0) = emailclean Then ' email already there
                                    Myemailarray(j, 1) = Status  'update its status only
                                Else
                                    ' look at the next entry in the array
                                End If
                          Next j
                    Else ' Firt email into the array don't need to compare it
                        
                    End If
                    ' array is not empty and don't have the email in the array
                    Myemailarray(mea, 0) = emailclean
                    Myemailarray(mea, 1) = Status
                    mea = mea + 1

I would like to loop to put into the array the email and it's status if the email address is not already there, but if it is only update the status.

 

[mikerickson]

mymailArray is a 2 dimensional array. LBound and UBound should specify which index they are looking at.

For j = LBound(Myemailarray, 1) To UBound(Myemailarray, 1)

 

[dcharland]

Thanks for the response, if I use the code you provided I get an error, but I was able to make it work with the following:

 If Myemailarray(LBound(Myemailarray), LBound(Myemailarray)) <> "" Then 'compare emails
                        For j = LBound(Myemailarray) To UBound(Myemailarray)
                            If Myemailarray(j, 0) = emailclean Then Myemailarray(j, 1) = Status: t = "true" 'update its status only
                                
                        Next j
                     
                     End If
                        If t <> "true" Then ' No match was found so writing the new email and status
                        Myemailarray(mea, 0) = emailclean
                        Myemailarray(mea, 1) = Status
                        mea = mea + 1
                        Else
                        t = ""
                        End If

Now the issue with this piece of code is that I can't ReDim the Lbound of the multi-dimension array, but I don't know how many emails the array will be composed of ahead of time. Do you know a workaround for that?

The only think I can think of is to build two separated array's, one for the email and one for the status! Any other options?

Thanks

 

[mikerickson]

When ReDim Preserving an n dimensional array one can only ReDim Preserve the last dimension.

If you need to ReDim Preserve the first index of a 2-D array, you can use Transpose.

Dim myArray as Variant, workArray As Variant
ReDim myArray (1 to 100, 1 to 3)
' fill myArray with 50 rows of data.

workArray = Application.Transpose(myArray)

ReDim Preserve workArray(1 to 3, 1 to 50)

myArray = Application.Transpose(workArray)

 

[dcharland]

Thanks works wonders this .transpose function