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

A

Antoniozarzal

New Member
Joined
Apr 25, 2016
Messages
9
Hello MrExcel, excuse me if I make a mistake with the language, because English is not my own.

I want to press that command button that pops an InputBox and put a number in it, like the picture. That happens in sheet "Sizes".

p8kp9muai03n434lhha2gph9b6_image1.png


Then I want that value to be searched in Column A of the sheet "FormatSize". If that value doesn't exist, then, appear a MessageBox saying something like "Sorry, this value doesn't exist", and exit sub.

But if the value exists in that column A of sheet "FormatSize", display other InputBoxes asking for values (numbers or letters), that will appear now in columns F, G, H, I, J of the same row of the first value found in Column A.

When finished that, display a MessageBox saying "thank you for updating your sizes!"

I hope I made myself be understood, thank you a lot.

Antonio.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Antoniozarzal said:
Hello MrExcel, excuse me if I make a mistake with the language, because English is not my own.

I want to press that command button that pops an InputBox and put a number in it, like the picture. That happens in sheet "Sizes".

p8kp9muai03n434lhha2gph9b6_image1.png


Then I want that value to be searched in Column A of the sheet "FormatSize". If that value doesn't exist, then, appear a MessageBox saying something like "Sorry, this value doesn't exist", and exit sub.

But if the value exists in that column A of sheet "FormatSize", display other InputBoxes asking for values (numbers or letters), that will appear now in columns F, G, H, I, J of the same row of the first value found in Column A.

When finished that, display a MessageBox saying "thank you for updating your sizes!"

I hope I made myself be understood, thank you a lot.

Antonio.
Click to expand...
Hi Antonio, welcome to the boards.

Is this macro any good to you?

Code: 
Sub UpdateCells()
' Defines variables
Dim Rng As Range, cRange As Range, FindString As String, NewVal As String


' Defines LastRow as the last row of column A on the FormatSize sheet
LastRow = Sheets("FormatSize").Cells(Rows.Count, "A").End(xlUp).Row
' Sets the check range as A1 to the last row of A on the FormatSize sheet
Set cRange = Sheets("FormatSize").Range("A1:A" & LastRow)


' Set variable FindString as the result of the first input box
FindString = InputBox("Ingrese la ficha del trabajador", "Consulta")
    ' With the check range
    With cRange
        ' Set Rng as the cell the desired value is found in
        Set Rng = .Find(What:=FindString, _
                            After:=.Cells(1), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False)
            ' If Rng doesn't exist then...
            If Rng Is Nothing Then
                ' Display a suitable error
                MsgBox "Sorry, this value doesn't exist"
                Exit Sub
            ' Else if Rng does exist then...
            Else
                ' Input box asking for new value of corresponding F column
                NewVal = InputBox("Please select a value for column F", "Column F Value")
                ' Update corresponding cell in column F with the new value
                Range("F" & Rng.Row).Value = NewVal
                ' Input box asking for new value of corresponding G column
                NewVal = InputBox("Please select a value for column G", "Column G Value")
                ' Update corresponding cell in column G with the new value
                Range("G" & Rng.Row).Value = NewVal
                ' Input box asking for new value of corresponding H column
                NewVal = InputBox("Please select a value for column H", "Column H Value")
                ' Update corresponding cell in column H with the new value
                Range("H" & Rng.Row).Value = NewVal
                ' Input box asking for new value of corresponding I column
                NewVal = InputBox("Please select a value for column I", "Column I Value")
                ' Update corresponding cell in column I with the new value
                Range("I" & Rng.Row).Value = NewVal
                ' Input box asking for new value of corresponding J column
                NewVal = InputBox("Please select a value for column J", "Column J Value")
                ' Update corresponding cell in column J with the new value
                Range("J" & Rng.Row).Value = NewVal
                ' Display message box
                MsgBox "Thank you for updating your sizes!"
            End If
    End With
                
End Sub

The above code can be applied to a button which will run when clicked. It asks for the first value to find in an input box. If the value is found in column A of the FormatSize sheet it will proceed with the rest of the code, asking for the corresponding values needed for columns F, G, H, I and J.

If the value is not found it displays an error and exits the macro.
 
Upvote 0
Hi Fishboy, thank you a lot buddy. Just one thing. The values from the inputboxes are appearing in the same sheet of the command button "sizes". But I need them to appear in the FormatSize sheet. Apart from that, it works perfectly.
 
Upvote 0
Antoniozarzal said:
Hi Fishboy, thank you a lot buddy. Just one thing. The values from the inputboxes are appearing in the same sheet of the command button "sizes". But I need them to appear in the FormatSize sheet. Apart from that, it works perfectly.
Click to expand...
Ooops, my bad! Glad to hear it otherwise worked as intended!

Try this modified version. This should update the correct sheet for you:

Code: 
Sub UpdateCells()
' Defines variables
Dim Rng As Range, cRange As Range, FindString As String, NewVal As String


' Defines LastRow as the last row of column A on the FormatSize sheet
LastRow = Sheets("FormatSize").Cells(Rows.Count, "A").End(xlUp).Row
' Sets the check range as A1 to the last row of A on the FormatSize sheet
Set cRange = Sheets("FormatSize").Range("A1:A" & LastRow)


' Set variable FindString as the result of the first input box
FindString = InputBox("Ingrese la ficha del trabajador", "Consulta")
    ' With the check range
    With cRange
        ' Set Rng as the cell the desired value is found in
        Set Rng = .Find(What:=FindString, _
                            After:=.Cells(1), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False)
            ' If Rng doesn't exist then...
            If Rng Is Nothing Then
                ' Display a suitable error
                MsgBox "Sorry, this value doesn't exist"
                Exit Sub
            ' Else if Rng does exist then...
            Else
                ' Input box asking for new value of corresponding F column
                NewVal = InputBox("Please select a value for column F", "Column F Value")
                ' Update corresponding cell in column F with the new value
                Sheets("FormatSize").Range("F" & Rng.Row).Value = NewVal
                ' Input box asking for new value of corresponding G column
                NewVal = InputBox("Please select a value for column G", "Column G Value")
                ' Update corresponding cell in column G with the new value
                Sheets("FormatSize").Range("G" & Rng.Row).Value = NewVal
                ' Input box asking for new value of corresponding H column
                NewVal = InputBox("Please select a value for column H", "Column H Value")
                ' Update corresponding cell in column H with the new value
                Sheets("FormatSize").Range("H" & Rng.Row).Value = NewVal
                ' Input box asking for new value of corresponding I column
                NewVal = InputBox("Please select a value for column I", "Column I Value")
                ' Update corresponding cell in column I with the new value
                Sheets("FormatSize").Range("I" & Rng.Row).Value = NewVal
                ' Input box asking for new value of corresponding J column
                NewVal = InputBox("Please select a value for column J", "Column J Value")
                ' Update corresponding cell in column J with the new value
                Sheets("FormatSize").Range("J" & Rng.Row).Value = NewVal
                ' Display message box
                MsgBox "Thank you for updating your sizes!"
            End If
    End With
                
End Sub
 
Upvote 0
oM390.png

Awesome, now it works exactly as intended. I have a question, if I want to make sure people write the right things on the inputboxes, can I make a data validation as always on the columns? or the macro overpasses that criteria?
Thank you again :)
 
Upvote 0
Antoniozarzal said:
oM390.png

Awesome, now it works exactly as intended. I have a question, if I want to make sure people write the right things on the inputboxes, can I make a data validation as always on the columns? or the macro overpasses that criteria?
Thank you again :)
Click to expand...
You're most welcome.

When it comes to the input boxes (I assume you mean the format size ones rather than the first one), how many "valid" responses will there be for each column F:J?
 
Upvote 0
Fishboy said:
You're most welcome.

When it comes to the input boxes (I assume you mean the format size ones rather than the first one), how many "valid" responses will there be for each column F:J?
Click to expand...

Well, we are talking about sizes of clothing, so
for column F the values are: S, M, L, XL, XXL, XXXL.
For column G: 28, 30, 32, 34, 36, 38.
For column H: 34;35;36;37;38;39;40;41;42;43;44
For column I: 34;35;36;37;38;39;40;41;42;43;44
For column J: 34;35;36;37;38;39;40;41;42;43;44
 
Upvote 0
Antoniozarzal said:
Well, we are talking about sizes of clothing, so
for column F the values are: S, M, L, XL, XXL, XXXL.
For column G: 28, 30, 32, 34, 36, 38.
For column H: 34;35;36;37;38;39;40;41;42;43;44
For column I: 34;35;36;37;38;39;40;41;42;43;44
For column J: 34;35;36;37;38;39;40;41;42;43;44
Click to expand...
Try the following. The code will check each input box for a valid response and will give an error and exit if one is not received. It also checks whether a cell is already updated in case you have to go back to a row you had started but did not finish due to invalid responses:

Code: 
Sub UpdateCells()
' Defines variables
Dim Rng As Range, cRange As Range, FindString As String, NewVal As String


' Defines LastRow as the last row of column A on the FormatSize sheet
LastRow = Sheets("FormatSize").Cells(Rows.Count, "A").End(xlUp).Row
' Sets the check range as A1 to the last row of A on the FormatSize sheet
Set cRange = Sheets("FormatSize").Range("A1:A" & LastRow)


' Set variable FindString as the result of the first input box
FindString = InputBox("Ingrese la ficha del trabajador", "Consulta")
    ' With the check range
    With cRange
        ' Set Rng as the cell the desired value is found in
        Set Rng = .Find(What:=FindString, _
                            After:=.Cells(1), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False)
            ' If Rng doesn't exist then...
            If Rng Is Nothing Then
                ' Display a suitable error
                MsgBox "Sorry, this value doesn't exist"
                ' Exit the macro
                Exit Sub
            ' Else if Rng does exist then...
            Else
                
                ' If the corresponding cell in column F is empty then...
                If Sheets("FormatSize").Range("F" & Rng.Row).Value = "" Then
                    ' Input box asking for new value of corresponding F column
                    NewVal = InputBox("Please select a value for column F", "Column F Value")
                    ' If a valid option is entered then...
                    If IsNumeric(Application.Match(NewVal, Array("S", "M", "L", "XL", "XXL", "XXXL"), 0)) Then
                        ' Update corresponding cell in column F with the new value
                        Sheets("FormatSize").Range("F" & Rng.Row).Value = UCase(NewVal)
                    ' Else if an invalid option is entered...
                    Else
                        ' Display an error message
                        MsgBox "That is not a valid response for this column"
                        ' Exit macro
                        Exit Sub
                    End If
                End If
            
                ' If the corresponding cell in column G is empty then...
                If Sheets("FormatSize").Range("G" & Rng.Row).Value = "" Then
                    ' Input box asking for new value of corresponding G column
                    NewVal = InputBox("Please select a value for column G", "Column G Value")
                    ' If a valid option is entered then...
                    If IsNumeric(Application.Match(NewVal, Array("28", "30", "32", "34", "36", "38"), 0)) Then
                    ' Update corresponding cell in column G with the new value
                    Sheets("FormatSize").Range("G" & Rng.Row).Value = NewVal
                    ' Else if an invalid option is entered...
                    Else
                        ' Display an error message
                        MsgBox "That is not a valid response for this column"
                        ' Exit macro
                        Exit Sub
                    End If
                End If
                    
                ' If the corresponding cell in column H is empty then...
                If Sheets("FormatSize").Range("H" & Rng.Row).Value = "" Then
                    ' Input box asking for new value of corresponding H column
                    NewVal = InputBox("Please select a value for column H", "Column H Value")
                    ' If a valid option is entered then...
                    If NewVal >= 34 And NewVal <= 44 Then
                        ' Update corresponding cell in column H with the new value
                        Sheets("FormatSize").Range("H" & Rng.Row).Value = NewVal
                      ' Else if an invalid option is entered...
                    Else
                        ' Display an error message
                        MsgBox "That is not a valid response for this column"
                        ' Exit macro
                        Exit Sub
                    End If
                End If
                
                ' If the corresponding cell in column I is empty then...
                If Sheets("FormatSize").Range("I" & Rng.Row).Value = "" Then
                    ' Input box asking for new value of corresponding I column
                    NewVal = InputBox("Please select a value for column I", "Column I Value")
                    ' If a valid option is entered then...
                    If NewVal >= 34 And NewVal <= 44 Then
                        ' Update corresponding cell in column I with the new value
                        Sheets("FormatSize").Range("I" & Rng.Row).Value = NewVal
                    ' Else if an invalid option is entered...
                    Else
                        ' Display an error message
                        MsgBox "That is not a valid response for this column"
                        ' Exit macro
                        Exit Sub
                    End If
                End If
                    
                ' If the corresponding cell in column J is empty then...
                If Sheets("FormatSize").Range("J" & Rng.Row).Value = "" Then
                    ' Input box asking for new value of corresponding J column
                    NewVal = InputBox("Please select a value for column J", "Column J Value")
                    ' If a valid option is entered then...
                    If NewVal >= 34 And NewVal <= 44 Then
                        ' Update corresponding cell in column J with the new value
                        Sheets("FormatSize").Range("J" & Rng.Row).Value = NewVal
                    ' Else if an invalid option is entered...
                    Else
                        ' Display an error message
                        MsgBox "That is not a valid response for this column"
                        ' Exit macro
                        Exit Sub
                    End If
                End If
                
                ' Display message box
                MsgBox "Thank you for updating your sizes!"
            End If
    End With
                
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question
Fixing of VBA Code
Replies
3
Views
250
jolivanes
jolivanes
G
  • Solved
Format & Protect range of cells using VBA
Replies
7
Views
291
Gajendran Yadhav
G
B
  • Solved
Find column and delete rows below....
2
Replies
11
Views
559
Alex Blakenburg
A
J
  • Solved
Combine rows based on previous column duplicate data
Replies
2
Views
203
jessiehayden
J
tabbytomo
  • Solved
Copy entire row if cell has specific value, looking at multiple columns and worksheets.
Replies
2
Views
298
NateSC
NateSC

Forum statistics

Threads
1,215,028
Messages
6,122,753
Members
449,094
Latest member
dsharae57

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top