none
how to convert an EXCEL =FIND( ) formula to VBA in Excel 2010

    Question

  • I have this excel formula =MID(A1,1,(FIND("DC=",A1)-2))

    But Excel 2010 compiler accepts the =MID but balks at the FIND function.
    I've searched the forum for 45 min and can't find a solution.
    Help appreciated
    Thanks,  Jimbo213

    - - - - - - - -

    Dim r As Long
    Dim c As Long
    Dim CellValue As Long
     
    c = 1
    For r = 1 To Selection.Rows.Count
    CellValue = Selection.Cells(r, 1).Value

    If CellValue <> "" Then
        CellValue = Mid(Selection.Cells(r, 1), 1, (Find("DC=", Selection.Cells(r, 1)) - 2))
     
    ' other stuff goes here later
           
        c = Selection.Columns.Count
    End If

    Next c
    Next r

    • Edited by Jimbo213 Friday, December 10, 2010 7:45 PM
    Friday, December 10, 2010 6:50 PM

Answers

  • The VBA function InStr provides the equivalent, but the syntax is slightly different:

    strText = "Microsoft Access"
    strSearch = "cc"
    intPos = InStr(strText, strSearch)

    Click in InStr in the Visual Basic Editor and press F1 to get more detailed info.


    Regards, Hans Vogelaar
    • Marked as answer by Jimbo213 Friday, December 10, 2010 7:12 PM
    • Unmarked as answer by Jimbo213 Friday, December 10, 2010 7:43 PM
    • Marked as answer by Jimbo213 Friday, September 06, 2013 2:06 AM
    Friday, December 10, 2010 7:06 PM
  • If you compare the code with the formula, you'll see that the -2 shouldn't have been placed outside the parentheses:

    Selection.Cells(r, 1).Value = Mid(Selection.Cells(r, 1).Value, 1, InStr(1, Selection.Cells(r, 1).Value, "DC=") - 2)


    Regards, Hans Vogelaar
    • Proposed as answer by Bruce Song Tuesday, December 14, 2010 9:12 AM
    • Marked as answer by Bruce Song Saturday, December 18, 2010 6:42 AM
    Friday, December 10, 2010 10:54 PM
  • Hi Jim,

             Have your resoved your issue yet? Were the suggestions help you or not? I have tried Hans' solution, it works well. If you still have any questions, feel free to follow up.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Jimbo213 Friday, September 06, 2013 2:02 AM
    Friday, December 17, 2010 3:35 AM
  • Yes - I marked this issue as answered in 2010  [Jimbo213]

    Mr. JMontoni reactivated this thread on 9/5/2013


    Jim Holstein

    • Marked as answer by Jimbo213 Friday, September 06, 2013 2:06 AM
    Friday, September 06, 2013 2:06 AM

All replies

  • The VBA function InStr provides the equivalent, but the syntax is slightly different:

    strText = "Microsoft Access"
    strSearch = "cc"
    intPos = InStr(strText, strSearch)

    Click in InStr in the Visual Basic Editor and press F1 to get more detailed info.


    Regards, Hans Vogelaar
    • Marked as answer by Jimbo213 Friday, December 10, 2010 7:12 PM
    • Unmarked as answer by Jimbo213 Friday, December 10, 2010 7:43 PM
    • Marked as answer by Jimbo213 Friday, September 06, 2013 2:06 AM
    Friday, December 10, 2010 7:06 PM
  • Thank you Hans.  I apologize in advance for being so uninformed on VBA.

    I am getting a Run-Time error '13' Type Mismatch and would appreciate your assistance in converting this excel formula =MID(A1,1,(FIND("DC=",A1)-2))  .....  where I am looping down all rows in the A column.

    Dim r As Long
    Dim c As Long
     
    c = 1
    For r = 1 To Selection.Rows.Count

        Selection.Cells(r, 1).Value = Mid(Selection.Cells(r, 1).Value, 1, InStr(1, Selection.Cells(r, 1).Value, "DC=")) - 2
     
    ' other stuff goes here later

    Next r

    End Sub


    Jim Holstein
    Friday, December 10, 2010 7:31 PM
  • If you compare the code with the formula, you'll see that the -2 shouldn't have been placed outside the parentheses:

    Selection.Cells(r, 1).Value = Mid(Selection.Cells(r, 1).Value, 1, InStr(1, Selection.Cells(r, 1).Value, "DC=") - 2)


    Regards, Hans Vogelaar
    • Proposed as answer by Bruce Song Tuesday, December 14, 2010 9:12 AM
    • Marked as answer by Bruce Song Saturday, December 18, 2010 6:42 AM
    Friday, December 10, 2010 10:54 PM
  • Hi Jim,

             Have your resoved your issue yet? Were the suggestions help you or not? I have tried Hans' solution, it works well. If you still have any questions, feel free to follow up.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Jimbo213 Friday, September 06, 2013 2:02 AM
    Friday, December 17, 2010 3:35 AM
  • Can anyone explain me why instr for *0 doesn't work?

    See below.

    Thx.


    Sub Find_Star_Zero()
    For r = 1 To 10
        For c = 1 To 10
            If InStr(Cells(r, c).Value, "*0") Then
                Cells(r, c).Select
                GoTo Found
            End If
        Next c
    Next r
    Found:

    End Sub

    Thursday, September 05, 2013 7:27 PM
  • Did you mean * as wildcard? InStr doesn't work with wildcards. InStr(Cells(r, c).Value, "*0") returns a positive number if the cell contains the literal string *0.

    To find any string followed by 0, use

    Sub FindZero()
        Dim cel As Range
        Set cel = Range("A1:J10").Find(What:="0", LookAt:=xlPart)
        If Not cel Is Nothing Then
            cel.Select
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, September 05, 2013 8:23 PM
  • NO, I have several cells with values and formulas that are multiplied by zero just for testing purposes and I want to find them using VBA code; but it seems that InStr(Cells(r, c).Value, "*0") doesn't recognize the "*0" in the cell (i.e. cell A2 is =+A1*0)

    Thursday, September 05, 2013 8:50 PM
  • Ah - you don't want to look at the cell values but at the formulas. In your macro, change

            If InStr(Cells(r, c).Value, "*0") Then

    to

            If InStr(Cells(r, c).Formula, "*0") Then

    or use

    Sub Find_Star_Zero()
        Dim cel As Range
        Set cel = Range("A1:J10").Find(What:="~*0", _
            LookIn:=xlFormulas, LookAt:=xlPart)
        If Not cel Is Nothing Then
            cel.Select
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Thursday, September 05, 2013 8:58 PM
  • Yes - I marked this issue as answered in 2010  [Jimbo213]

    Mr. JMontoni reactivated this thread on 9/5/2013


    Jim Holstein

    • Marked as answer by Jimbo213 Friday, September 06, 2013 2:06 AM
    Friday, September 06, 2013 2:06 AM
  • GREAT!!!

    Thx.

    Friday, September 06, 2013 2:09 PM