Function If 2 array = value

[Infine]

Hello,

So I have created an array with 2 values in it SEE: KMyarray = Application.Transpose(Range(rngArray1, rngArray2))
In my other variable I have StrLookForString = <code here> (see below).

I want to see if the value in MyArray1 and the value in MyArray2 = StrLookForString .

So basically
MyArray(1) & MyArray(2) = Variable


How do I do this? See some of the code below:

Set rngArray1 = Range(Sheets("Köpunderlag").Range("B2"), Sheets("Köpunderlag").Range("B2").End(xlDown))
Set rngArray2 = Range(Sheets("Köpunderlag").Range("G2"), Sheets("Köpunderlag").Range("G2").End(xlDown))

KMyarray = Application.Transpose(Range(rngArray1, rngArray2))


 StrLookForString = Sheets("OFIC Köp").Cells(i, 5) & Sheets("OFIC Köp").Cells(i, 12)
        BoolHit = FindLoop(KMyarray, StrLookForString)

If BoolHit = True Then
SomeCodeHere
Else
Something else

And this is the function which is not working - my guess:

Function FindLoop(arr, val) As Boolean
' Function to find matching value in array.
    Dim r As Long
    For r = 1 To UBound(arr, 1)
        If CStr(arr(r, 1)) & CStr(arr(r, 2)) = val Then
            FindLoop = True
            arr(r, 1) = "OK"
            arr(r, 2) = "OK"
            Exit Function
        End If
    Next r
End Function

This is what is not working (my guess): If CStr(arr(r, 1)) & CStr(arr(r, 2)) = val Then


Would really appreciate if anyone can help me, I am stuck here.

 

[Infine]

How come it is getting from C D E F & G? It should be from B2 and then going down as far as there is data in B (LastRow)?
Same with G2 to LastRow. Which part of the code includes D E F? It is not suppose to do so, so I might fix that..

 

[Fluff]

This part of your code is doing it

kmyarray = Application.Transpose(Range(rngArray1, rngArray2))

Will both B & G have the same number of rows?

 

[Infine]

Hmm okey then that is my only issue atm.

What should I write if I only want

B2 -> Lastrow
G2 -> Lastrow

    Set rngArray1 = Range(Sheets("Köpunderlag").Range("B2"), Sheets("Köpunderlag").Range("B2").End(xlDown))
    Set rngArray2 = Range(Sheets("Köpunderlag").Range("G2"), Sheets("Köpunderlag").Range("G2").End(xlDown))

 
   KMyarray = Application.Transpose(Range(rngArray1, rngArray2))

And yes, they should do have the same number of rows.

 

[Fluff]

I would tend to keep them as two arrays & use it like

With Sheets("Köpunderlag")
   rngArray1 = .Range("B2", .Range("B2").End(xlDown)).Value2
   rngArray2 = .Range("G2", .Range("G2").End(xlDown)).Value2
End With

'start loop here
StrLookForString = Sheets("OFIC Köp").Cells(i, 5) & Sheets("OFIC Köp").Cells(i, 12)
        BoolHit = FindLoop(rngArray1, rngArray2, StrLookForString)

If BoolHit = True Then
'SomeCodeHere
Else
'Something else
End If

End Sub


Function FindLoop(arr1, arr2, val) As Boolean
' Function to find matching value in array.
    Dim r As Long
    For r = 1 To UBound(arr1, 1)
        If CStr(arr1(r, 1)) & CStr(arr2(r, 1)) = val Then
            FindLoop = True
            arr1(r, 1) = "OK"
            arr2(r, 1) = "OK"
            Exit Function
        End If
    Next r
End Function

 

[Infine]

rngArray1 = Sheets("Köpunderlag").Range("O1", Sheets("Köpunderlag").Range("O1").End(xlDown)).Value2
rngArray2 = Sheets("Köpunderlag").Range("S1", Sheets("Köpunderlag").Range("S1").End(xlDown)).Value2

working

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Infine]

May I ask one last question in the same matter?

Function FindLoop(arr1, arr2, val) As Boolean
' Function to find matching value in array.
    Dim r As Long
    For r = 1 To UBound(arr1, 1)
        If CStr(arr1(r)) & CStr(arr2(r)) = val Then
            FindLoop = True
            arr1(r) = "OK"
            arr2(r) = "OK"
            Exit Function
        End If
    Next r
End Function

This is my Function now and it is working perfectly.

However, I want to be able to return the row (r) aswell on my

If BoolHit = True Then
'SomeCodeHere
Else
'Something else
End If

At the moment I am writing

            Sheets("Dualitet").Cells(i + 3, 11).Value = srngArray1(i)
            Sheets("Dualitet").Cells(i + 3, 12).Value = srngArray2(i)

But the "i" here (on srngArray1(i) and not in the Cells) is with a different row because I am looping on something else. I want that when the "FindLoop = True" the r it finds it will be the "i" on my code above. How do I do this? Return the row where it finds the True Value.

 

[Fluff]

How about

Function FindLoop(arr1, arr2, val) As Long
' Function to find matching value in array.
    Dim r As Long
    For r = 1 To UBound(arr1, 1)
        If CStr(arr1(r)) & CStr(arr2(r)) = val Then
            FindLoop = r
            arr1(r) = "OK"
            arr2(r) = "OK"
            Exit Function
        End If
    Next r
End Function

and then change BoolHit to long & use

If BoolHit >0 Then

 

[Infine]

Will try. Am I suppose to put this like

Sheets("Dualitet").Cells(i + 3, 12).Value = srngArray2(FindLoop)

 

[Fluff]

No, it needs to be BooHit, not Findloop