is it instr??

mikezcg · #1 (**permalink**) 05-19-06, 15:29

Sub test()
Dim search As String
search = Range("A1").Text
Dim find As String
find = Range("A2").Text
Range("A3") = InStr(0, search, find, vbTextCompare)

end sub

i want to see if 1 string is in the other in this case range values are

A1=Advisors V, L.P.’s Statement of Changes in Partner’s

A2=’s Statement of Changes in Partner’s

clearly it is but i cant fiqure out how to do it in vba

Fazza · #2 (**permalink**) 05-21-06, 20:35

Try starting the search at 1. That is, InStr(1, .....

Suggest also using different variable names. find is used already in VBA!

Such as for strings - sFind & sSearch

regards,
Fazza

savbill · #3 (**permalink**) 05-21-06, 22:30

As Fazza mentioned you should use different variable names 'search' may be a reserved term, anyway it is not obvious its a variable.

Here's an example:

Code:

Sub searchstr()
Dim strLookfor as String
Dim strLookin as String
Dim strResult as String

strLookin = Trim(Cells(1, 1))
strLookfor = Trim(Cells(1, 2))
If Instr(1, strLookin, strLookfor) > 0 Then
   strResult = "Found in Column A"
Else
   strResult = "Not Found"
End If

Cells(1, 3) = strResult

End Sub

You notice i used the 'Cells' method verses Range to identify the cell location. Using the 'Cells' method allows you more versitility in controlling the process flow. Often you need to work on an entire worksheet of data. With Cells you can easily do this using a 'For Next' statement to loop through all the rows.

Code:

For i = 2 to 100
' write something to column A
Cells(i, 1) = "This is Row: " & i  
Next

shades · #4 (**permalink**) 05-22-06, 17:34

Quote:

Originally Posted by savbill

End Sub[/CODE]
You notice i used the 'Cells' method verses Range to identify the cell location. Using the 'Cells' method allows you more versitility in controlling the process flow. Often you need to work on an entire worksheet of data. With Cells you can easily do this using a 'For Next' statement to loop through all the rows.

Code:

For i = 2 to 100
' write something to column A
Cells(i, 1) = "This is Row: " & i  
Next

And using the consistent (Hungarian) Notation, this would be something like:
m - designating module level
lng designating Long (vs. Integer)

Code:

Dim mlngI as Long
For mlngI = 2 to 100
' write something to column A
Cells(mlngI , 1) = "This is Row: " & mlngI  
Next

That way you will always know the type of variable and whether it is module or global level.

Internet Services

Web Development

Digital Marketing

Consumer Tech