Find MatchCase problem

[moonhk]

I am using MatchCase:=False , but below coding can not able to find
040x in Worksheet.

In target worksheet have cc code 040X, I can not able to using 040x to
found target cc code 040X .


Do you know why ?
I also try LookIn:=xlvalue
Set c = (loSheet.Columns("A:A").Find(What:=loString,
After:=loSheet.Range("A1"), _
LookIn:=xlvalue, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, MatchByte:=False))


Sub test_SearchCC()
Dim a As Integer
a = Search_CC("040x")
MsgBox "Result row value is " & a
End Sub


Function Search_CC(loString As String) As Integer
'~~ Find Cost Ctr
Dim c
Dim FirstAddress
Dim myPos As Integer
Dim result As Integer
Dim loSheet As Worksheet
Dim loWorkbook As Workbook
Set loWorkbook = Application.Workbooks(Account.getAccWSheet)
Set loSheet = loWorkbook.Sheets(Account.getCC)


'
' Cells.Find(What:="040X", After:=ActiveCell,
LookIn:=xlFormulas, LookAt _
' :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
' False).Activate


Set c = (loSheet.Columns("A:A").Find(What:=loString,
After:=loSheet.Range("A1"), _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, MatchByte:=False))


If Not c Is Nothing Then
FirstAddress = c.Address
myPos = InStr(2, FirstAddress, "$")
result = VBA.Mid(FirstAddress, myPos, VBA.Len(FirstAddress))
'~~ MsgBox "found " & FirstAddress & " " & Result
Search_CC = result


Else
Search_CC = 0
End If
End Function

 

[NickHK]

Check the Object Browser.
xlvalueS
You can usually tell when a constant/enum etc is not recognised, because the
capitalisation of the word is not corrected by the VB editor.

<Member of Excel.XlFindLookIn>
Const xlValues = -4163 (&HFFFFEFBD)

<Member of Excel.XlAxisType>
Const xlValue = 2

NickHK

 

[moonhk]

The value return is -4163. What should me do ?

Sub t1()

MsgBox xlValues
End Sub

 

[NickHK]

In your code, you are using xlValue.
It should be xlValueS.

NickHK

 

[moonhk]

No. My first t1 using xlValues ,return -4163
while f2 using xlvalue , return 2

Sub t1()
MsgBox xlValues
End Sub

Sub t2()
MsgBox xlValue
End Sub

 

[NickHK]

See the wrong value here ?

<From your first post>
Do you know why ?
I also try LookIn:=xlvalue
Set c = (loSheet.Columns("A:A").Find(What:=loString,
After:=loSheet.Range("A1"), _
LookIn:=xlvalue, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, MatchByte:=False))
</From your first post>

NickHK