Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2009
    Posts
    47

    Unanswered: Help with FIND function

    I am trying to find a user enterd percentage like 97% or 89% in a row of data.

    Dim ReportValue As String
    ReportValue = Application.InputBox("Enter Report Value in XX% format")
    ActiveCell.select
    ActiveCell.EntireRow.Select
    Selection.Find(What:=ReportValue, After:=ActiveCell, LookIn:=xlFormulas,
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Select


    Compiler keeps giving me errors I save the value as a string but it doesnt work.

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    The first line of your code is missing a line break.

    Also, it would be better to defensively code to allow for the chance that the range object's find method doesn't find a cell containing the value you are looking for. Typically we do this using an intermediary range object variable which we can test:

    Code:
    Sub foo()
        Dim ReportValue As String, rngFound As Range
        
        ReportValue = Application.InputBox("Enter Report Value in XX% format")
        
        Set rngFound = ActiveCell.EntireRow.Find( _
                        What:=ReportValue, _
                        After:=ActiveCell, _
                        LookIn:=xlFormulas, _
                        LookAt:=xlPart, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False, _
                        SearchFormat:=False)
                        
        'did we find anything?
        If rngFound Is Nothing Then
            MsgBox "Report value not found...."
        Else
            rngFound.Select
        End If
    
    End Sub
    Hope that helps...

Posting Permissions

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