Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2004
    Posts
    11

    Unanswered: VBA code to find a value in a cell

    Hi,
    I have a list in excel as follows:

    Vendor/InvNbr/InvDate/InvAmt/check

    I want to create a VBA code to prompt the user to input the value to find and then go to the row and put "paid" in the check column if the value is found. If not, display a message back to the user.

    Can someone help me write the VBA code?

    Thank you very much

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    One way to do this is using a input box to prompt the user for a value then use the 'Find' method to search the entire sheet or a defined range. Another way to search the values would be to use a Loop to check each cell in a column, then exit the loop when a match is found.

    This is an example I adapted from code I use which uses the 'Find' method.
    Code:
    Sub FindInput()
        Dim stringToFind As String
        Dim Found As Range
        
        stringToFind = Application.InputBox("Enter invoice Number to find?", _
        "Search String")
        
        If Trim(stringToFind) = "" Then Exit Sub
        
        Set Found = Cells.Find(What:=stringToFind, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext _
            , MatchCase:=False)
        
        If Found Is Nothing Then
        Else
            With Found
                .Activate
                If Not Len(.offset(rowOffset:=0, columnOffset:=1)) = 0 Then
                        .offset(rowOffset:=0, columnOffset:=2) = "paid"
                Else
                ' here I would just activate the cell insted of showing a message
                        msgbox "The value does not Exist"
                End If    
            End With
         End If   
    End Sub
    ~

    Bill

Posting Permissions

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