Trying to compare values in 3 columns?

[qjd2004]

Hiya,

Arrays are new to me and I'm trying to look at 3 columns and do some comparisons. :huh:

I want to check in the ManagerArr() (assigned to a range in 1 column) and if the value is zero then compare the ClientArr() values and those in the MainframeArr() or else, compare the ManagerArr() to the cells in MainframeArr().

Phew.

Currently I've tried to do it like this:

Public Sub TryArrays()

Dim LastRow As Long
Dim LastCell As Range
Dim ClientArr As Variant
Dim ManagerArr As Variant
Dim MainframeArr As Variant
Dim clRange As String
Dim mfRange As String
Dim acmRange As String

Set LastCell = Range("A1").SpecialCells(xlCellTypeLastCell)
LastRow = LastCell.Row
clRange = "AG" & LastRow
acmRange = "AI" & LastRow
mfRange = "AH" & LastRow

ReDim ClientArr(Range("AG2"), clRange) As Variant
ReDim ManagerArr(Range("AI2"), acmRange) As Variant
ReDim MainframeArr(Range("AH2"), mfRange) As Variant

    For i = LBound(ClientArr()) To UBound(ManagerArr())
        If ManagerArr(i) = 0 Then
            For x = LBound(ClientArr()) To UBound(MainframeArr())
                If ClientArr(x) = MainframeArr(x) Then
                    Range("AK" & x).FormulaR1C1 = "MATCH!"
                Else
                    Range("AK" & x).FormulaR1C1 = "NO MATCH!"
                End If
            Next x
        ElseIf ManagerArr(i) > 0 Then
            For y = LBound(ClientArr()) To UBound(ManagerArr())
                If ClientArr(y) = ManagerArr(y) Then
                    Range("AK" & y).FormulaR1C1 = "MATCH!"
                Else
                    Range("AK" & y).FormulaR1C1 = "NO MATCH!"
                End If
            Next y
        End If
    Next i
        
End Sub

But I get a type mismatch when it gets to the ReDim and also I'm concerned cos I don't know if you can really nest for For Next Loop(s) like that, but it won't get that far anyway for me to find out.

Can someone out there explain how to approach this sort of thing?

Many thanks in advance!

 

[xlbo]

ReDim uses an integer to define how many array entries there will be - you are passing it an address - try this instead

Dim ClientArr As Variant
Dim ManagerArr As Variant
Dim MainframeArr As Variant
Dim clRange As String
Dim mfRange As String
Dim acmRange As String

Set LastCell = Range("A1").SpecialCells(xlCellTypeLastCell)
LastRow = LastCell.Row
clRange = "AG" & LastRow
acmRange = "AI" & LastRow
mfRange = "AH" & LastRow

set clientArr() = range("AG2:" & clRange)
set ManagerArr() = range("AI2:" & acmRange)
set MainframeArr() = range("AH2:" & mfRange)

Then do your loop - you can nest them but it could take a real long time as from your initial code, if ManagerArr(i) = 0 then you loop all the way through the other arrays - somehow I don't think that would be the logic you want...

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[qjd2004]

Hi xlbo

thanks for your reply. I've tried changing my code to Set the ranges of the 3 arrays but I get "subscript out of range" when it gets to Set ClientArr() = Range("AG2:" & clRange) so I don't know where to go from here?

 

[xlbo]

apologies - more haste - less speed needed - amend:
set clientArr() = range("AG2:" & clRange)
set ManagerArr() = range("AI2:" & acmRange)
set MainframeArr() = range("AH2:" & mfRange)

to

clientArr = range("AG2:" & clRange)
ManagerArr = range("AI2:" & acmRange)
MainframeArr = range("AH2:" & mfRange)


Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[qjd2004]

Hi xlbo,

That works (the dimensions get set ok) but I get subscript out of range here

    For i = LBound(ManagerArr()) To UBound(ManagerArr())
        
        Select Case ManagerArr(i)
            Case 0:
                If ClientArr(i) = MainframeArr(i) Then
                    Range("AK" & i).FormulaR1C1 = "MATCH!"
                ElseIf ClientArr(i) <> MainframeArr(i) Then
                    Range("AK" & x).FormulaR1C1 = "NO MATCH!"
                End If
            Case Is > 0:
                If ClientArr(i) = ManagerArr(i) Then
                    Range("AK" & i).FormulaR1C1 = "MATCH!"
                ElseIf ClientArr(i) <> ManagerArr(i) Then
                    Range("AK" & i).FormulaR1C1 = "NO MATCH!"
                End If
        End Select
        
    Next i

I think I'm doing something wrong.

Can you help?

 

[xlbo]

where ??? there is no line indicated - just a whole bunch of 'em




Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[qjd2004]

Hiya,

sorry about that. Error occurs on this line:

For i = LBound(ManagerArr()) To UBound(ManagerArr())

 

[xlbo]

change to:

For i = LBound(ManagerArr) To UBound(ManagerArr)

Select Case ManagerArr(i,1)

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[qjd2004]

Fabulous,

Thanks very much Geoff, it's just the right thing! That worked nicely.