Find a value in a column and then fill some cells in that row. Everything using InputBox

[Antoniozarzal]

Hello Excelforum, excuse me if I make a mistake with the language because English is not my own.

I want to press a command button in sheet A that pops an inputbox asking for a value. That value is then searched in the column A of the sheet B.

If the value doesn't exist, it should appear a message box saying something like "sorry, it doesn't exist" and exit sub. But if the value exists in that column, then pop up a new input box, asking for a new value, that will appear in the J column of the same row of the value, everything in sheet B.

Finally, new inputboxes will appear until column M, doing the same thing. When finishes, appear a messagebox saying something like "You're done, great!"

And finishes sub

Thank you a lot guys

[djbomaha]

Hi Antoniozarzal,

Here is code that can be assigned to a button that searches and updates data in a sheet named "B" per your description.

Sub UpdateB()

Dim txtA As String
Dim wshB As Worksheet
Dim mRow As Long

Set wshB = Sheets("B")

txtA = InputBox("What is value to be searched?", "Search Value")
On Error Resume Next
mRow = WorksheetFunction.Match(txtA, wshB.Columns("A"), 0)
If (Err.Number <> 0) Then
   Err.Clear
   MsgBox "Sorry, it doesn't exist."
   GoTo 100000
Else
   For i = 10 To 13
      wshB.Cells(mRow, i) = InputBox("What is value for Column " & Chr(64 + i) & "?", Chr(64 + i) & " Value")
   Next i
End If

MsgBox "You're done, great!"

100000 ' End Sub
On Error GoTo 0

End Sub

Hope that helps,

Dan

[Antoniozarzal]

Hi Dan, your code worked awesome, I made some little changes and that was great. Thank you a lot!

[bakerman2]

@ djbomaha

Use Application.Match instead of Worksheetfunction.Match. In case of error code doesn't go into debug and you don't need that entire error-routine.
Just check value of variable is enough to choose how to proceed.

Sub UpdateB()

Dim txtA As String, mRow As Long, i as Long

Set wshB = Sheets("B")

txtA = InputBox("What is value to be searched?", "Search Value")

With Sheets("B")
    mRow = Application.Match(txtA, .Columns("A"), 0)
    If Not IsError(mRow) Then
        For i = 10 To 13
            .Cells(mRow, i) = InputBox("What is value for Column " & Chr(64 + i) & "?", Chr(64 + i) & " Value")
        Next i
    Else: MsgBox "Sorry, non existing value."
    End If
End With
MsgBox "You're done, great!"

End Sub

[djbomaha]

Hi bakerman2,

Thanks for the guidance. It will help me streamline my programming a little bit more. Your suggestion on how to use error handling definitely makes the code more efficient and not as confusing without the "else" statement mine required.

Thanks,

Dan