+ Reply to Thread
Results 1 to 7 of 7

How to Stop IF Statement running continuously?

Hybrid View

  1. 10-03-2013, 09:15 PM #1
    Jardman123
    Jardman123 is offline
    Registered User
    Join Date
    05-26-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    20

    How to Stop IF Statement running continuously?

    Hi guys, I have an IF statement which checks what the value in a cell is then posts a score to another cell according to the value. It works fine, however whenever I click anywhere or do other tasks on the same sheet; the true value for the IF statement keeps firing - basically it adds +2 to the cell whenever I click somewhere.

    Here's the code;

    If Range("$Q$8") < 19 Then
Range("$Q$11") = Range("$Q$11") + 0
ElseIf (Range("$Q$8") >= 19 And Range("$Q$8") <= 25) Then
Range("$Q$11") = Range("$Q$11") + 2
ElseIf (Range("$Q$8") >= 26 And Range("$Q$8") <= 30) Then
Range("$Q$11") = Range("$Q$11") - 1
ElseIf (Range("$Q$8") >= 31 And Range("$Q$8") <= 40) Then
Range("$Q$11") = Range("$Q$11") - 3
ElseIf Range("$Q$8") > 40 Then
Range("$Q$11") = Range("$Q$11") - 5
Else: Range("$Q$11") = Range("$Q$11") + 0


End If
    Thanks guys.
    Reply With QuoteRegister To Reply
  2. 10-03-2013, 10:10 PM #2
    6StringJazzer
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    26,977

    Re: How to Stop IF Statement running continuously?

    Can't diagnose out of context. Please attach your whole workbook, or at least post all of the code in this module. Is this code in Sub Worksheet_SelectionChange?
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]
    Reply With QuoteRegister To Reply
  3. 10-04-2013, 02:44 AM #3
    Jardman123
    Jardman123 is offline
    Registered User
    Join Date
    05-26-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: How to Stop IF Statement running continuously?

    Assignment 2 data.xlsm

    Here's the spreadsheet, it is for a uni assignment, I must make a questionnaire basically, the answers of which must return specific values to be calculated towards a life expectancy.

    I can't use userforms or controls..I have been instructed to only use msgbox and inputbox.
    Reply With QuoteRegister To Reply
  4. 10-04-2013, 07:00 AM #4
    Jardman123
    Jardman123 is offline
    Registered User
    Join Date
    05-26-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: How to Stop IF Statement running continuously?

    Bump, please help.
    Reply With QuoteRegister To Reply
  5. 10-04-2013, 08:59 AM #5
    djdjdj
    djdjdj is offline
    Forum Contributor
    Join Date
    04-25-2013
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2010
    Posts
    150

    Re: How to Stop IF Statement running continuously?

    Hi,

    Try this.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 10-04-2013, 08:35 PM #6
    Jardman123
    Jardman123 is offline
    Registered User
    Join Date
    05-26-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: How to Stop IF Statement running continuously?

    Thanks, but now I have the opposite problem where it doesnt refresh at all..I think I'm going to make a msgbox saying 'Your BMI is ...' then have that value enter the box and link the assigned values to it.
    Reply With QuoteRegister To Reply
  7. 10-04-2013, 09:45 PM #7
    6StringJazzer
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    26,977

    Re: How to Stop IF Statement running continuously?

    This is an example of why I wanted to see all the code, and where proper code formatting for readability makes it obvious when there is a problem. Your code doesn't take full advantage of indentation. When it's indented, it's easy to see that there is an If missing in the last section. Here is all of the code from that module properly indented. I have highlighted in red where it looks like you meant to have additional code.

    Also, you have several If statements with mutually exclusive conditions. It is better to merge them into an If..Elseif..ElseIf structure as shown.
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

   On Error Resume Next
   
   If Target.Address = "$Q$4" Then
   
      Target.Value = InputBox("What is your cholestrol?")
      
      If Range("$Q$4") < 160 Then
         Range("$Q$11") = Range("$Q$11") + 2
      ElseIf (Range("$Q$4") >= 160 And Range("$Q$4") < 200) Then
         Range("$Q$11") = Range("$Q$11") + 1
      ElseIf (Range("$Q$4") >= 200 And Range("$Q$4") < 240) Then
         Range("$Q$11") = Range("$Q$11") - 1
      ElseIf (Range("$Q$4") >= 240 And Range("$Q$4") < 280) Then
         Range("$Q$11") = Range("$Q$11") - 2
      ElseIf Range("$Q$4") > 280 Then
         Range("$Q$11") = Range("$Q$11") - 4
      Else
         Range("$Q$11") = Range("$Q$11") + 0
      End If
   
   ElseIf Target.Address = "$Q$5" Then
   
      Target.Value = InputBox("What is your blood pressure? (Systolic)")
      
      If Range("$Q$5") < 110 Then
         Range("$Q$11") = Range("$Q$11") + 1
      ElseIf (Range("$Q$5") >= 110 And Range("$Q$5") < 120) Then
         Range("$Q$11") = Range("$Q$11") + 0
      ElseIf (Range("$Q$5") >= 120 And Range("$Q$5") < 150) Then
         Range("$Q$11") = Range("$Q$11") - 1
      ElseIf (Range("$Q$5") >= 150 And Range("$Q$5") < 170) Then
         Range("$Q$11") = Range("$Q$11") - 2
      ElseIf Range("$Q$5") > 170 Then
         Range("$Q$11") = Range("$Q$11") - 4
      Else
         Range("$Q$11") = Range("$Q$11") + 0
      End If
   
   ElseIf Target.Address = "$Q$6" Then
   
      Target.Value = InputBox("How many packs of cigarettes do you smoke daily?")
      
      If Range("$Q$6") = 0 Then
         Range("$Q$11") = Range("$Q$11") + 1
      ElseIf Range("$Q$6") = 1 Then
         Range("$Q$11") = Range("$Q$11") - 3
      ElseIf Range("$Q$6") = 2 Then
         Range("$Q$11") = Range("$Q$11") - 5
      Else
         Range("$Q$11") = Range("$Q$11") + 0
      End If
      
   ElseIf Target.Address = "$Q$7" Then
   
      Target.Value = InputBox("How many family members with heart disease?" & vbNewLine & "No family history = None" & vbNewLine & "One relative over 60 = One over 60" & vbNewLine & "Two relatives over 60 = Two over 60" & vbNewLine & "One relative under 60 = One under 60" & vbNewLine & "Two or more relatives under 60 = Two under 60")
      
      If Range("$Q$7") = "None" Then
         Range("$Q$11") = Range("$Q$11") + 2
      ElseIf Range("$Q$7") = "One over 60" Then
         Range("$Q$11") = Range("$Q$11") + 0
      ElseIf Range("$Q$7") = "Two over 60" Then
         Range("$Q$11") = Range("$Q$11") - 1
      ElseIf Range("$Q$7") = "One under 60" Then
         Range("$Q$11") = Range("$Q$11") - 2
      ElseIf Range("$Q$7") = "Two under 60" Then
         Range("$Q$11") = Range("$Q$11") - 4
      Else
         Range("$Q$11") = Range("$Q$11") + 0
      End If
      
   ElseIf Range("$Q$8") < 19 Then
   
      If Range("$Q$8") = ??? Then ' I added this but not sure what condition you want.
         Range("$Q$11") = Range("$Q$11") + 0
      ElseIf (Range("$Q$8") >= 19 And Range("$Q$8") <= 25) Then
         Range("$Q$11") = Range("$Q$11") + 2
      ElseIf (Range("$Q$8") >= 26 And Range("$Q$8") <= 30) Then
         Range("$Q$11") = Range("$Q$11") - 1
      ElseIf (Range("$Q$8") >= 31 And Range("$Q$8") <= 40) Then
         Range("$Q$11") = Range("$Q$11") - 3
      ElseIf Range("$Q$8") > 40 Then
         Range("$Q$11") = Range("$Q$11") - 5
      Else
         Range("$Q$11") = Range("$Q$11") + 0
      End If ' Added this too, to match the If above
   
   End If

End Sub
    P.S. Spelling error on worksheet and in code: should be "Cholesterol"
    Last edited by 6StringJazzer; 10-04-2013 at 09:49 PM. Reason: P.S.; also corrected code for syntax
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Macro running continuously
    By billpurdom in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-07-2013, 12:29 PM
  2. Continuously running a recorded macro-Please Help
    By Nik.Kaps in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-10-2012, 07:27 AM
  3. VBA won't stop running
    By codsmith in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-03-2011, 09:39 AM
  4. Macro to stop running at last row
    By staples in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-10-2009, 01:52 PM
  5. Stop running all commands
    By daviddoria in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-06-2009, 12:28 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1