Why does this not work
Sometimes VBA's help is very "helpful".
Or just recording a macro so you can see how things are spelled.
Corey wrote:
Thank you Dave,
You know i actual changed that but to, Lookin:=xlValue minus the 's'
Cheers
Appreciate you asisstance
Corey....
"Dave Peterson" wrote in message
...
I shouldn't have trusted your original code <vbg.
LookIn:=xlValues
not
LookIn:=xlText
Corey wrote:
Seem to get a Sub Script Out of Range error here :
With Worksheets("Sheet2").Range("C:C")
Set rngFound = .Find(What:=TextBox2.Text, After:=.Cells(.Cells.Count), _
LookIn:=xlText, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, Matchbyte:=False)
???
"Dave Peterson" wrote in message
...
I didn't notice the "with textbox2.value" in the original post.
Maybe this version:
'declare BlkProc at the top of the module--not in any sub.
Dim BlkProc as boolean
Private Sub TextBox2_Change()
Dim rngFound As Range
if blkproc = true then exit sub
With Worksheets("Sheet2").Range("C:C")
Set rngFound = .Find(What:=TextBox2.Text, After:=.Cells(.Cells.Count), _
LookIn:=xlText, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, Matchbyte:=False)
end with
if rngFound is nothing then
msgbox "none found"
else
MsgBox "That Item " & TextBox1.Value & _
" is ALREADY in the List of Items to be Done", vbInformation
End If
blkProc = true
TextBox2.Value = UCase(TextBox2.Value)
blkproc = false
End Sub
Corey wrote:
Dave,
after you advice i have:
Private Sub TextBox2_Change()
With TextBox2.Value
'Dim rngFound As Range
On Error Resume Next
With Worksheets("Sheet2").Range("C:C")
Set rngFound = .Find(What:=TextBox2.Text, After:=.Cells(.Cells.Count), _
LookIn:=xlText, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, Matchbyte:=False)
If rngFound.Count < "" Then
MsgBox "That Item " & TextBox1.Value & " is ALREADY in the List of Items to be Done",
vbInformation
Else
MsgBox "None Found"
Exit Sub
End If
End With
End With
TextBox2.Value = UCase(TextBox2.Value)
End Sub
If i remove the ['] on the On Error Resume Next i Get an error in the SET rngFound section,
But if i leave th e['] in on the O E R N line, it get nothing occurring.
Did i understand correctly ?
Corey....
"Dave Peterson" wrote in message
...
Without testing at all...
Since you're using a with/end with statement he
With Worksheets("Sheet2").Range("C:C")
Then this portion: after:=.range("c1") refers to something not in column C.
Try this in the immediate window:
msgbox range("c:c").range("c1").address
I'd use:
after:=.cells(1)
(the first cell in the range specified in the With statement.)
Actually, I'd use:
With Worksheets("Sheet2").Range("C:C")
Set rngFound = .Find(What:=TextBox2.Text, After:=.cells(.cells.count), _
LookIn:=xlText, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, Matchbyte:=False)
.cells(.cells.count) will mean that you're looking after the last cell in column
C (C65536 in xl2003).
This would make a difference if C1 could be the cell that contains the value for
which you're looking.
Corey wrote:
I am trying to set this code to pick up IF the exact Text(sentance) match is found in Sheet2
Column
C,
is used in Textbox2 then i get a Prompt to Say so, else a Prompt to say NOT.
But why does it not work, although a MATCH is there ?
Private Sub TextBox2_Change()
With TextBox2.Value
Dim rngFound As Range
On Error Resume Next
With Worksheets("Sheet2").Range("C:C")
Set rngFound = .Find(What:=TextBox2.Text, After:=.Range("C1"), LookIn:=xlText,
LookAt:=xlWhole,
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, Matchbyte:=False)
If rngFound.Count < "" Then
MsgBox "That Item " & TextBox1.Value & " is ALREADY in the List of Items to be Done",
vbInformation
Else
Msgbox "None Found"
Exit Sub
End If
End With
End With
TextBox2.Value = UCase(TextBox2.Value)
End Sub
Corey....
--
Dave Peterson
--
Dave Peterson
--
Dave Peterson
--
Dave Peterson
|