CODEWITHSUNDEEP
excelmultidimensional-arraycomparevba
Karthikeyan Subbiyan
Karthikeyan Subbiyan

Reputation: 5

Multidimensional array and comparing values in each array and give result for each array

I following program to call values to array and compare the values and give a result. I asked this question in another thread. VPA program for compare values in array and give a result (MS Excel)

Here we can see that one dimensional array to which values are assigned. But i want to call values from multiple column when a IF condition is true 

Dim arr as variable
For i = 1 to u 
if mycondition1 then
x = Cells(i, 2).Value
m = Application.WorksheetFunction.CountIf(Range("B4:B" & u), x)
ReDim arr(1 To m)
For j = 1 to u
if mycondition2 then
arr(y) = Cells(j, 27)
End If
Next j

For one dimensional array i use code arr(y) = Cells(j, 27). Here i want to call values from column 27. Like this i want to assign values from different column (Say 27, 28, 29 upto 32). Instead of create six arrays, I know i can use two dimensional array say arr(1 to m, 1 to 6) and get values assigned from cells of each column and rows. But don't know how to compare values for each column similar to codes given below (which is comparison for one column values). I forced to write one comparison codes for each column. So my coding lines are more and take lots of time to execute. And i want do the for loop upto u=100.000 rows. Is there any easy way 

w = arr()
Res = Application.Match(Array("No", "-"), w, 0)
For Each r In Res
ThisWorkbook.Worksheets("Sheet1").Cells(i, 27) = "X"
If Not IsError(r) Then
ThisWorkbook.Worksheets("Sheet1").Cells(i, 27) = "O"
Exit For
End If
Next r
Erase arr()
Exitsinglepart:
End If
Next i
End Sub

Please help me how to rewrite this code for comparing values called from each column.

Upvotes: 0

Views: 1239

Answers (1)

Variatus
Variatus

Reputation: 14383

Firstly, I must say that I don't understand what you want. Secondly, I feel that you are going about it the wrong way. In view of my first observation, I apologise. This is the way I would go about it:-

    Private Sub TestIsYes()
        Dim Arr As Variant
        Arr = Array("Yes", "-", "Yes", "yes", "Yes")

        Debug.Print IsYes(Join(Arr, ","))
    End Sub

Function IsYes(StrArr As String) As String
    ' 25 Mar 2017

    Dim Fun As Boolean

    Fun = CBool(InStr(1, StrArr, "no", vbTextCompare))
    If Not Fun Then
        Fun = CBool(InStr(StrArr, "-"))
    End If
    IsYes = Split("No Yes")(CInt(Fun) + 1)
End Function

As you see, the function IsYes receives a string like "Yes,-,yes,Yes" and returns "Yes" or "No" based upon your criteria. You can test the function by changing the elements of the parameter array in the sub TestIsYes. TestIsYes could write this result directly into any cell on the worksheet.

This function might be modified in whatever way you need, but it remains at the core of your project. The question therefore is how to produce the string passed to IsYes as an argument. You wish (or should wish, perhaps) produce this string from a range. The next function does that.

Private Function RangeToString(Rng As Range) As String
    ' 25 Mar 2017

    Dim Fun As String
    Dim Arr As Variant
    Dim R As Long, C As Long            ' rows / columns

    Arr = Rng.Value
    With Arr
        For R = LBound(Arr) To UBound(Arr)
            For C = LBound(Arr, 2) To UBound(Arr, 2)
                Fun = Fun & Arr(R, C) & ","
            Next C
        Next R
    End With
    RangeToString = Fun
End Function

Note that any array produced from an Excel range is 3-dimensional, even if it was taken from a single column. Therefore the above function produces a string of the kind required by IsYes from any range, regardless of how many columns it has.

I have set up a test in the worksheet range B2:C5 (4 rows, 2 columns - expand or reduce this as you wish), filled with your criteria. I created the following function using the functions explained before.

Function UDFIsYes(Rng As Range) As String
    ' 25 Mar 2017
    UDFIsYes = IsYes(RangeToString(Rng))
End Function

You can call this function from the worksheet entering =UDFIsYes(B2:C5). You can call the same function from a VBA procedure like the following.

Private Sub TestUDF()
    Debug.Print UDFIsYes(ActiveSheet.Range(Cells(2, 2), Cells(5, 3)))
End Sub

I hope this helps. Let me know where this information falls short of what you need.

Upvotes: 0

Related Questions