Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Why does this not work
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 363
Default Why does this not work
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....


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Why does this not work
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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 363
Default Why does this not work
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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Why does this not work
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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 363
Default Why does this not work
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


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Why does this not work
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
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 363
Default Why does this not work
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


Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear 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
how can i automatically generate work order numbers from work orde rob h Excel Discussion (Misc queries) 1 July 13th 09 07:59 PM
flash object dont work in my excel work sheet Nitn Excel Discussion (Misc queries) 0 July 4th 09 08:00 AM
If I have a work sheet protected and try to run a macro to hide rows or columns it won't work. Correct? Marc Excel Programming 2 July 12th 06 04:10 AM
Counting dates in multiple work sheets and work books Savage Excel Discussion (Misc queries) 0 December 19th 05 11:41 PM
Is there away to keep "auto save" from jumping to the first work sheet in the work book? Marc New Users to Excel 2 April 21st 05 01:27 AM


All times are GMT +1. The time now is 10:20 PM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement