How to search in VBA?
 

I am creating a UDF where I have to retrive one matching value.
My part no comprises of a commen string and 6 variable string.
This makes 6 combinations out of which only one is found in the master
sheet.
I want to know which one is found in master sheet.

Here is a example.
Common Variable Final
L34251154 0024 L3425110024
L34251154 2549 L3425112549
L34251154 5074 L3425115074
L34251154 7599 L3425117599
L34251154 0049 L3425110049
L34251154 5099 L3425115099

I have to test all 6 final strings to findout which one is in master.

So far I have following which is not working.

Option Base 1
Function VV(SER As Range, DUMP As Range)
Dim LT(6) As Variant
If SER.Count < 1 Then End

Series = Left(SER.Value, 7)

LT(1) = Series & "0024"
LT(2) = Series & "2549"
LT(3) = Series & "5074"
LT(4) = Series & "7599"
LT(5) = Series & "0049"
LT(6) = Series & "5099"

For I = 1 To 6
Debug.Print LT(I)
Debug.Print "I= " & I
With DUMP
' Set C = .Find(LT(I), LookIn:=xlValues).Value
Set C = .Find(What:=LT(I).Value, After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate


If Not C Is Nothing Then
firstAddress = C.Address
VV = C.Value
End If
End With
Debug.Print "VV= " & VV
Next

End Function

Request if someone can point out my mistake please.

Regards,
Madiya

How to search in VBA?
 

There are a couple of issues in your code.

1. Remove the .Activate from your find something like this

Set C = .Find(What:=LT(I).Value, _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)
2. You are not doing a findNext so you do not need FirstAddress
3. You can exit the loop with an exit for when you have found a match.

All of that being said it would be a shorter and easier function to do a
CountIf to determine if there is a matching entry. Just my 2 cents.
--
HTH...

Jim Thomlinson


"Madiya" wrote:

I am creating a UDF where I have to retrive one matching value.
My part no comprises of a commen string and 6 variable string.
This makes 6 combinations out of which only one is found in the master
sheet.
I want to know which one is found in master sheet.

Here is a example.
Common Variable Final
L34251154 0024 L3425110024
L34251154 2549 L3425112549
L34251154 5074 L3425115074
L34251154 7599 L3425117599
L34251154 0049 L3425110049
L34251154 5099 L3425115099

I have to test all 6 final strings to findout which one is in master.

So far I have following which is not working.

Option Base 1
Function VV(SER As Range, DUMP As Range)
Dim LT(6) As Variant
If SER.Count < 1 Then End

Series = Left(SER.Value, 7)

LT(1) = Series & "0024"
LT(2) = Series & "2549"
LT(3) = Series & "5074"
LT(4) = Series & "7599"
LT(5) = Series & "0049"
LT(6) = Series & "5099"

For I = 1 To 6
Debug.Print LT(I)
Debug.Print "I= " & I
With DUMP
' Set C = .Find(LT(I), LookIn:=xlValues).Value
Set C = .Find(What:=LT(I).Value, After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate


If Not C Is Nothing Then
firstAddress = C.Address
VV = C.Value
End If
End With
Debug.Print "VV= " & VV
Next

End Function

Request if someone can point out my mistake please.

Regards,
Madiya