Results 1 to 8 of 8

User Input Checking & Formula Looping

Threaded View

  1. #1
    Forum Contributor
    Join Date
    07-16-2009
    Location
    australia
    MS-Off Ver
    Excel 2007
    Posts
    116

    User Input Checking & Formula Looping

    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
    Attached Files Attached Files
    Last edited by flebber; 09-09-2010 at 11:24 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1