Results 1 to 4 of 4

Thread: is it instr??

  1. #1
    Join Date
    Oct 2003
    Posts
    311

    Unanswered: is it instr??

    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

  2. #2
    Join Date
    Feb 2006
    Posts
    113
    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

  3. #3
    Join Date
    Feb 2004
    Posts
    533
    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
    ~

    Bill

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    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.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •