Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Find MatchCase problem
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 62
Default Find MatchCase problem


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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4,391
Default Find MatchCase problem
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" wrote in message
oups.com...


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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 62
Default Find MatchCase problem
The value return is -4163. What should me do ?

Sub t1()

MsgBox xlValues
End Sub


NickHK wrote:
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" wrote in message
oups.com...


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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4,391
Default Find MatchCase problem
In your code, you are using xlValue.
It should be xlValueS.

NickHK

"moonhk" wrote in message
ps.com...
The value return is -4163. What should me do ?

Sub t1()

MsgBox xlValues
End Sub


NickHK wrote:
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" wrote in message
oups.com...


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




Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 62
Default Find MatchCase problem
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 wrote:
In your code, you are using xlValue.
It should be xlValueS.

NickHK

"moonhk" wrote in message
ps.com...
The value return is -4163. What should me do ?

Sub t1()

MsgBox xlValues
End Sub


NickHK wrote:
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" wrote in message
oups.com...


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



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4,391
Default Find MatchCase problem
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

"moonhk" wrote in message
oups.com...
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 wrote:
In your code, you are using xlValue.
It should be xlValueS.

NickHK

"moonhk" wrote in message
ps.com...
The value return is -4163. What should me do ?

Sub t1()

MsgBox xlValues
End Sub


NickHK wrote:
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" wrote in message
oups.com...


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





Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Find MatchCase problem moonhk Excel Programming 0 October 26th 06 07:21 AM
Find and Find Next problem Richard Hocking Excel Programming 2 September 13th 06 03:51 PM
MatchCase syntax? teeb Excel Discussion (Misc queries) 2 May 5th 06 11:51 AM
find problem jon Excel Programming 2 May 24th 04 11:27 AM
Problem with FIND LSB Excel Programming 2 January 12th 04 04:16 AM


All times are GMT +1. The time now is 06:59 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement