Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Match value from input box with value in column

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Arizona
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi, I have a spreadsheet that has hundreds of rows that include a unit number, a name, and several charges generated by the sheet.

    I'm trying to set-up a vba program that prompts user for a unit number, then finds that cell that matches the input(ed) unit number and then pulls the name and charges from the corresponding row.

    I've got the input box and the message box done, but am having trouble looping through the cells in the unit column (I'm still very new at VBA).

    Thanks alot for any help!

  2. #2
    Board Regular zacemmel's Avatar
    Join Date
    Apr 2002
    Location
    Redmond, WA
    Posts
    636
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I would just do something like this ( I haven't tested it, so beware ):

    sub test()
    dim xcol as integer
    dim xrow as integer
    dim gogo as integer
    gogo = 1
    xcol = 1
    xrow = 1
    do while len(cells(xrow, xcol)) and gogo
    if cells(xrow, xcol).value = msgboxvalue then
    gogo = 0
    end if
    xrow = xrow + 1
    loop
    end sub

    This "should" give you the column number and row number of the specified value in the inputbox.



  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Arizona
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I can't seem to tweak that formula to work with my spreadsheet, can I use the find method to return the cells address?

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Location
    Arizona
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I figured out a solution!

    For those interested:

    'This starts the final bill procedure
    'prompt for unit number and move-out date
    unit = InputBox("Enter Unit #:", "Final Billing Form")
    Date = InputBox("Move out date?", "Final Billing Form")
    '

    'find row for that unit number
    With Sheets("Ridge Download").Range("B43:B422")
    resrow = .Find(unit).Row
    End With

    res = ActiveWorkbook.Sheets("Ridge Download").Cells(resrow, 6)

    etc....

    Thanks for you help zacemmel!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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