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