I am having a little issue with some code.
My Goal: Is to have the macro stop allow the user to enter input data into columns BI & BJ. The input will be simple either 0 or 1 or 2. Once input done I would hope it would then loop through formula and place results in BK & BL respectively.
I have written some code but I haven't coded how to stop and start after user input. I have attempted to check for errors in input. Any feedback or suggestion appreciated.
I have attached a sample sheet.
This is my current code.
Sub NeedUserInput()
Dim CellLoop As Range
Dim lastrow
lastrow = Worksheets("Datasheet").Cells(Rows.Count, 1).End(xlUp).Row
'stop to allow user input into Column BI & BJ
MsgBox ("Enter 0,1 or 2 into Columns BI or BJ")
' How do I stop and Start????
'start here when user input complete
' Check for Blanks or Typo's only accepted Values are 0 0r 1 or 2
For i = lastrow To 1 Step -1
If IsEmpty(Cells(i, "BI")) Then
MsgBox ("Blank Cell at" & Cells(i, "BI"))
End If
Next i
For i = lastrow To 1 Step -1
If IsEmpty(Cells(i, "BJ")) Then
MsgBox ("Blank Cell at" & Cells(i, "BJ"))
End If
Next i
For i = lastrow To 1 Step -1
If IsEmpty(Cells(i, "BI")) Then
MsgBox ("Blank Cell at" & Cells(i, "BI"))
End If
Next i
For i = lastrow To 1 Step -1
If Not IsNumeric(Cells(i, "BI")) Then 'error
MsgBox ("Non Numeric Entry at" & Cells(i, "BI"))
ElseIf i > 3 Then
MsgBox ("Invalid Entry at" & Cells(i, "BI"))
End If
Next i
' Check BI for Values entered and put formula result in Column "BK"
For Each CellLoop In Worksheets("Datasheet").Range(Cells(2, 62), Cells(lastrow, 60)).Cells
If CellLoop.Value = "0" Then
CellLoop.Formula = "=IF(AP2<>0,(AO2-(AO2/((AQ2*1.65)+(AR2)+(AS2*0.8)))*((AR2)+(AS2*0.8)))/AP2,0)*1.25)"
ElseIf CellLoop.Value = "1" Then
CellLoop.Formula = "=IF(AU2<>0,(AT2-(AT2/((AV2*1.65)+(AW2)+(AX2*0.8)))*((AW2)+(AX2*0.8)))/AU2,0)*1.25)"
ElseIf CellLoop.Value = "2" Then
If Range("V2") > 17 Then
CellLoop.Formula = "=IF(V2<>0,(U2-(U2/((W2*1.65)+(X2)+(Y2*0.8)))*((X2)+(Y2*0.8)))/V2,0))"
ElseIf Range("V2") < 18 Then
CellLoop.Formula = "=IF(V2<>0,(U2-(U2/((W2*1.65)+(X2)+(Y2*0.8)))*((X2)+(Y2*0.8)))/V2,0)*0.725)"
End If
Next CellLoop
End Sub
Bookmarks