Consulting

Results 1 to 4 of 4

Thread: VBA Do While Loop Question

  1. #1

    Question VBA Do While Loop Question

    I am a newbie to VBA. The topic I am facing now is looping.
    There is an excel sheet with years from 1991 to 2019 and different numbers of names under each year.
    The requirements are to create two inputboxes, one asking the users to input a name and the other one asking for a year. If there is a match in the selected year column, it will display a message to let the user know there is a match in which row, then the sub ends. If there is no match after searching the whole column, it will also display a message to indicate there is no match.

    I have tried to finished some steps but have no idea on the others. Please help and many thanks!

    This is how the data looks like:

    data.jpg
    This is the VBA code:

    Sub DoLoop1()
    Dim searchCol As Integer
    Dim nCols As Integer
    Dim rowCount As Integer
    Dim foundMatch As Boolean
    Dim reqName As String

    Sheet1.Activate
    With Range("A1")
    ' use nCols variable here to identify the
    ' number of columns to be searched
    nCols = Cells(1, Columns.Count).End(xlToLeft).Column
    End With

    ' use reqName variable to capture the name to search for
    ' using an input box function

    reqName = LCase(InputBox("Enter the name"))

    ' use input box function to collect year to search, year - 1990 to
    ' get searchCol, which is to determine the column number to search
    ' for customer name, and also to check if year is a valid year
    ' You may want to use a Do loop here for checking user input of the year

    searchCol = InputBox("Enter the year")
    Do While searchCol <= "1990" Or searchCol > "2019"
    searchCol = InputBox("Enter the year again")
    Loop

    rowCount = 2
    foundMatch = False

    ' use Do While ... Loop, to repace the for... next loop,
    ' and If/Then/Else to search the customer name
    ' until it hits an empty cell in the search column.
    ' if there is a match in the search row, (1) display a msgbox
    ' with appropriate title and icon to show the customer name
    ' and record number (not row number), and (2) exit the do loop;
    '
    ' if there is no match in the search row, manually move the
    ' search to next row.
    ' note that if there is no name at all in the selected
    ' column, the body of the loop will never execute

    Do While Cells(rowCount, 1).Value <> ""
    Loop

    If Not foundMatch Then
    MsgBox "No match for " & reqName & " was found.", vbInformation, _
    "DoLoop1 - No Match"
    End If

  2. #2
    Anyone can help ? THX

  3. #3
    Maybe attach a workbook with the data so people don't have to create it for you.

    Re: "it will display a message to let the user know there is a match in which row"
    Don't need a macro for that. That should always be Row1

    Row1 does just not only have the year in it by the looks of it.

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Option Explicit
    
    
    Sub test()
        Dim iYear As Long
        Dim sName As String
        Dim iRow As Long, iCol As Long
        
        iYear = Application.InputBox("Enter Year", "Year", 0)
        If iYear = 0 Then Exit Sub
        
        
        iCol = 0
        On Error Resume Next
        iCol = Rows("1:1").Find(What:=iYear, After:=Cells(1, 1), LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Column
        On Error GoTo 0
        
        If iCol = 0 Then
            Call MsgBox("No year " & iYear, vbCritical + vbOKOnly, "Errror")
            Exit Sub
        End If
    
    
        sName = Application.InputBox("Enter Name", "Name", vbNullString)
        If Len(sName) = 0 Then Exit Sub
    
    
        iRow = 0
        On Error Resume Next
        iRow = Application.WorksheetFunction.Match(sName, Columns(iCol), 0)
        On Error GoTo 0
        
        If iRow = 0 Then
            Call MsgBox("No name = " & sName & " in " & iYear & " found", vbCritical + vbOKOnly, "Errror")
        
        Else
            Call MsgBox(sName & " found in in Column " & iCol & ", row " & iRow, vbInformation + vbOKOnly, "Yea!!")
        End If
    
    
    End Sub
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Tags for this Thread

Posting Permissions

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