In cell A1 I have: The Sky is blue
In the input box I enter blue
After running the below (99 times) i get - Didn't Find it! - 99 times !!!

Why?

Sub Foo()
Dim T As Integer
Dim Z As String
Z = InputBox("Enter key word") 'enter blue
T = InStr(1, Z, ActiveSheet.Range("A1"), vbTextCompare)
If Temp 0 Then
MsgBox "Found it!"
Else
MsgBox "Didn't Find it!"
End If
End Sub

Sub Foo()
Dim T As Integer
Dim Z As String
Z = InputBox("Enter key word") 'enter blue
T = InStr(1, Range("A1").Value, Z)
If T 0 Then
MsgBox "Found it!"
Else
MsgBox "Didn't Find it!"
End If
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JMay" wrote in message
...
In cell A1 I have: The Sky is blue
In the input box I enter blue
After running the below (99 times) i get - Didn't Find it! - 99 times
!!!

Why?

Sub Foo()
Dim T As Integer
Dim Z As String
Z = InputBox("Enter key word") 'enter blue
T = InStr(1, Z, ActiveSheet.Range("A1"), vbTextCompare)
If Temp 0 Then
MsgBox "Found it!"
Else
MsgBox "Didn't Find it!"
End If
End Sub

Two problems...

T = InStr(1, Z, ActiveSheet.Range("A1"), vbTextCompare)
should be:
T = InStr(1, ActiveSheet.Range("A1").value, Z, vbTextCompare)
(I like to use .value!)

And don't check
If Temp 0 then
you want
If T 0 then


JMay wrote:

In cell A1 I have: The Sky is blue
In the input box I enter blue
After running the below (99 times) i get - Didn't Find it! - 99 times !!!

Why?

Sub Foo()
Dim T As Integer
Dim Z As String
Z = InputBox("Enter key word") 'enter blue
T = InStr(1, Z, ActiveSheet.Range("A1"), vbTextCompare)
If Temp 0 Then
MsgBox "Found it!"
Else
MsgBox "Didn't Find it!"
End If
End Sub

--

Dave Peterson

Sorry but even after changing
If Temp 0 Then to
If T 0 Then

I still get same result,,

grrrrrrrr


"JMay" wrote:

In cell A1 I have: The Sky is blue
In the input box I enter blue
After running the below (99 times) i get - Didn't Find it! - 99 times !!!

Why?

Sub Foo()
Dim T As Integer
Dim Z As String
Z = InputBox("Enter key word") 'enter blue
T = InStr(1, Z, ActiveSheet.Range("A1"), vbTextCompare)
If Temp 0 Then
MsgBox "Found it!"
Else
MsgBox "Didn't Find it!"
End If
End Sub

Thanks Don,,

"Don Guillett" wrote:

Sub Foo()
Dim T As Integer
Dim Z As String
Z = InputBox("Enter key word") 'enter blue
T = InStr(1, Range("A1").Value, Z)
If T 0 Then
MsgBox "Found it!"
Else
MsgBox "Didn't Find it!"
End If
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JMay" wrote in message
...
In cell A1 I have: The Sky is blue
In the input box I enter blue
After running the below (99 times) i get - Didn't Find it! - 99 times
!!!

Why?

Sub Foo()
Dim T As Integer
Dim Z As String
Z = InputBox("Enter key word") 'enter blue
T = InStr(1, Z, ActiveSheet.Range("A1"), vbTextCompare)
If Temp 0 Then
MsgBox "Found it!"
Else
MsgBox "Didn't Find it!"
End If
End Sub

Read ALL of what Dave said

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JMay" wrote in message
...
Sorry but even after changing
If Temp 0 Then to
If T 0 Then

I still get same result,,

grrrrrrrr


"JMay" wrote:

In cell A1 I have: The Sky is blue
In the input box I enter blue
After running the below (99 times) i get - Didn't Find it! - 99 times
!!!

Why?

Sub Foo()
Dim T As Integer
Dim Z As String
Z = InputBox("Enter key word") 'enter blue
T = InStr(1, Z, ActiveSheet.Range("A1"), vbTextCompare)
If Temp 0 Then
MsgBox "Found it!"
Else
MsgBox "Didn't Find it!"
End If
End Sub

On Sun, 7 Sep 2008 11:38:01 -0700, JMay wrote:

In cell A1 I have: The Sky is blue
In the input box I enter blue
After running the below (99 times) i get - Didn't Find it! - 99 times !!!

Why?

Sub Foo()
Dim T As Integer
Dim Z As String
Z = InputBox("Enter key word") 'enter blue
T = InStr(1, Z, ActiveSheet.Range("A1"), vbTextCompare)
If Temp 0 Then
MsgBox "Found it!"
Else
MsgBox "Didn't Find it!"
End If
End Sub

Another reason to always include Option Explicit in your VBA routines.

From the menu bar:
Tools/Options
Editor
Select: Require Variable Declaration
--ron