Good Afternoon all,
I have a VBA macro that allows for data being entered into a text box to be placed in specific areas of my sheet. However, when I click "OK" with no value entered (blank), it ends the sub. I need the sub to stop running when it finds a blank value on the sheet, but when no value is entered into the text box, I need it to be recognized as a "0" value.
I would also like the sub to pick up where the data left off should the sub be ended early (this isn't a requirement for the sheet)
Also, I need the "Cancel" button in the input box to end the sub completely. Any help would be incredibly appreciated; current sub below:
Sub Test()
'
Dim Cl As Range
Dim Res As Variant
Dim i As Long
Dim wks As Worksheet
Application.ScreenUpdating = False
For Each wks In ActiveWorkbook.Worksheets
wks.Visible = xlSheetVisible
Next wks
Sheets("Test").Select
For Each Cl In Range("F2", Range("F" & Rows.Count).End(xlUp))
For i = 1 To 2
Res = InputBox("Please enter the total quantity in " & Cl.Value)
If Cl.Value = "" Then MsgBox "No More Cycle Counts Available"
If Res = "" Then Call Hide
If Res = "" Then Exit Sub
Cl.Offset(, -2).Value = Val(Res)
If Val(Res) = Cl.Offset(, -3).Value Then Exit For
Next i
Next Cl
Application.ScreenUpdating = True
End Sub
I have a VBA macro that allows for data being entered into a text box to be placed in specific areas of my sheet. However, when I click "OK" with no value entered (blank), it ends the sub. I need the sub to stop running when it finds a blank value on the sheet, but when no value is entered into the text box, I need it to be recognized as a "0" value.
I would also like the sub to pick up where the data left off should the sub be ended early (this isn't a requirement for the sheet)
Also, I need the "Cancel" button in the input box to end the sub completely. Any help would be incredibly appreciated; current sub below:
Sub Test()
'
Dim Cl As Range
Dim Res As Variant
Dim i As Long
Dim wks As Worksheet
Application.ScreenUpdating = False
For Each wks In ActiveWorkbook.Worksheets
wks.Visible = xlSheetVisible
Next wks
Sheets("Test").Select
For Each Cl In Range("F2", Range("F" & Rows.Count).End(xlUp))
For i = 1 To 2
Res = InputBox("Please enter the total quantity in " & Cl.Value)
If Cl.Value = "" Then MsgBox "No More Cycle Counts Available"
If Res = "" Then Call Hide
If Res = "" Then Exit Sub
Cl.Offset(, -2).Value = Val(Res)
If Val(Res) = Cl.Offset(, -3).Value Then Exit For
Next i
Next Cl
Application.ScreenUpdating = True
End Sub