Help with Instr function

  • Thread starter Thread starter JMay
  • Start date Start date
J

JMay

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
 
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
 
Sorry but even after changing
If Temp > 0 Then to
If T > 0 Then

I still get same result,,

grrrrrrrr
 
Thanks Don,,

Don Guillett said:
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
(e-mail address removed)
Click to expand...
 
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
Click to expand...

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

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