At present this is my situation;
I am trying to compare cells (alpha-numeric entries) in 2 columns, A
and C (of different depths). Some of the cell entries in A and C are
exactly equal, while the majority are not.
If a cell in C [say C1] matches a cell in A [say A1], a cell in E [E1]
displays ".... Found". Otherwise, the next closest match is returned.
The following is the UDF;
**************************************************
* *UDF code by Jindon*
*
**************************************************
Function wfindx(r As Range, rng As Range, Optional cap As Integer) As
String
Dim txt1, txt2, rngArray, x, flag As Boolean
' If cell is empty; EXIT!
If IsEmpty(r) Then Exit Function
txt1 = Split(r, " ") ' Split r value by space and put them into an
array.
rngArray = rng.Value ' Put the rng values in an array.
If IsMissing(cap) Then ' Adjusting arg, cap.
cap = UBound(txt1) ' If missing; cap = ubound(txt1).
' If cap is bigger than ubound(txt1).
ElseIf cap - 1 > UBound(txt1) Then
cap = UBound(txt1)
Else
cap = cap - 1 ' Because Lower bound of the array populated from
End If ' Split function is always 0, so needs to be adjusted
For i = LBound(txt1) To cap
x = x & txt1(i) & Chr(32) ' Populate testing string according to the
cap value
Next
' Put the testing string to variable x
x = Trim(x)
For i = LBound(rngArray) To UBound(rngArray) ' Start loop to test
rngArray
' If length of rngArray value is less than testing value, go to Next
If Len(rngArray(i, 1)) < Len(x) Then GoTo skip
' If the testing value found from the begining of the rngArray value
If InStr(1, rngArray(i, 1), x, vbTextCompare) = 1 Then
wfindx = r & " Found": Exit Function
End If
skip:
Next
End Function
***********************************************
Problem with this UDF;
Col A:
D11 10 9 8 TRI STATE LKG 0V
D11 10 9 8 TRI STATE LKG 0V
D11 10 9 8 TRI STATE LKG 0V
D11 10 9 8 TRI STATE LKG 0V
Col C;
D11 10 9 8 TRI STATE LKG 0V
D11 10 9 8 TRI STATE LKG 1V
D11 10 9 8 TRI STATE LKG 2V
D11 10 9 8 TRI STATE LKG 3V
and type into col E: "=wfindx(Cell,Range,3)"
then Col E returns;
D11 10 9 8 TRI STATE LKG 0V Found
D11 10 9 8 TRI STATE LKG 1V Found
D11 10 9 8 TRI STATE LKG 2V Found
D11 10 9 8 TRI STATE LKG 3V Found
even though :-
D11 10 9 8 TRI STATE LKG 1V Found
D11 10 9 8 TRI STATE LKG 2V Found
D11 10 9 8 TRI STATE LKG 3V Found
-: are not in the range (Col A).
Can you advise me how to fix this.?
OR:-
could I avoid the hassle of a lengthy UDF by using a VB function?