+ Reply to Thread
Results 1 to 17 of 17

help debugging for/next, if/elseif/endif

Hybrid View

  1. #1
    Registered User
    Join Date
    05-14-2013
    Location
    California
    MS-Off Ver
    Excel 2013
    Posts
    78

    help debugging for/next, if/elseif/endif

    Hi everyone, I am hoping someone can help me out with this, i cant seem to find what i am doing wrong.

    This code should be looking in the columns for 0's, chaning the cells to blank. then with cells that arent blank in the columns, it should check to see which ones are not within the specified parameters, and if the cells arent it adds the error message to the end of whatever error message is already in the same row in column 27.
    I really appreciate anyones help on this!
    Sub Eligibilitycheck()
    Dim lastrow As Integer
    Dim i As Integer
    lastrow = Range("A65536").End(xlUp).Row
    
    For i = lastrow To 2 Step -1
        'height
        If Cells(i, 9).Value = "0" Then
            Cells(i, 9).Value = ""
                ElseIf Cells(i, 9).Value < 24 Then
                    Cells(i, 27).Value = Cells(i, 27).Value & "Height - Out of range"
                ElseIf Cells(i, 9).Value > 90 Then
                    Cells(i, 27).Value = Cells(i, 27).Value & "Height - Out of range"
        End If
        
        'weight
        If Cells(i, 10).Value = "0" Then
            Cells(i, 10).Value = ""
                ElseIf Cells(i, 10).Value < 50 Then
                    Cells(i, 27).Value = Cells(i, 27).Value & "Weight - Out of range"
                ElseIf Cells(i, 10).Value > 600 Then
                    Cells(i, 27).Value = Cells(i, 27).Value & "Weight - Out of range"
        End If
        
        'waist circ/bmi
        If Cells(i, 13).Value = "" And Cells(i, 11).Value = "" Then
            Cells(i, 27).Value = Cells(i, 27).Value & "WaistC/BMI - Missing"
        If Cells(i, 13).Value = "0" Then
            Cells(i, 13).Value = ""
            ElseIf Cells(i, 13).Value < 15 Then
                Cells(i, 27).Value = Cells(i, 27).Value & "WaistC - Out of range"
            ElseIf Cells(i, 13).Value > 70 Then
                Cells(i, 27).Value = Cells(i, 27).Value & "WaistC - Out of range"
        End If
        If Cells(i, 11).Value = "0" Then
            Cells(i, 11).Value = ""
                ElseIf Cells(i, 11).Value < 10 Then
                    Cells(i, 27).Value = Cells(i, 27).Value & "BMI - Out of range"
                ElseIf Cells(i, 11).Value > 70 Then
                    Cells(i, 27).Value = Cells(i, 27).Value & "BMI - Out of range"
        End If
        
        'HDL
        If Cells(i, 15).Value = "0" Then
            Cells(i, 15).Value = ""
                ElseIf Cells(i, 15).Value < 10 Then
                    Cells(i, 27).Value = Cells(i, 27).Value & "HDL - Out of range"
                ElseIf Cells(i, 15).Value > 170 Then
                    Cells(i, 27).Value = Cells(i, 27).Value & "HDL - Out of range"
        End If
        
        'LDL
        If Cells(i, 16).Value = "0" Then
            Cells(i, 16).Value = ""
                ElseIf Cells(i, 16).Value < 20 Then
                    Cells(i, 27).Value = Cells(i, 27).Value & "LDL - Out of range"
                ElseIf Cells(i, 16).Value > 300 Then
                    Cells(i, 27).Value = Cells(i, 27).Value & "LDL - Out of range"
        End If
        
        'triglycerides
        If Cells(i, 17).Value = "0" Then
            Cells(i, 17).Value = ""
                ElseIf Cells(i, 17).Value < 20 Then
                    Cells(i, 27).Value = Cells(i, 27).Value & "Tryg - Out of range"
                ElseIf Cells(i, 17).Value > 7000 Then
                    Cells(i, 27).Value = Cells(i, 27).Value & "Tryg - Out of range"
        End If
    
        'glucose
        If Cells(i, 18).Value = "0" Then
            Cells(i, 18).Value = ""
                ElseIf Cells(i, 18).Value < 50 Then
                    Cells(i, 27).Value = Cells(i, 27).Value & "glucose - Out of range"
                ElseIf Cells(i, 18).Value > 500 Then
                    Cells(i, 27).Value = Cells(i, 27).Value & "glucose - Out of range"
        End If
        
        'a1c
        If Cells(i, 19).Value = "0" Then
            Cells(i, 19).Value = ""
                ElseIf Cells(i, 19).Value < 4 Then
                    Cells(i, 27).Value = Cells(i, 27).Value & "hbA1c - Out of range"
                ElseIf Cells(i, 19).Value > 15 Then
                    Cells(i, 27).Value = Cells(i, 27).Value & "hbA1c - Out of range"
        End If
        
        'systolic bp
         If Cells(i, 20).Value = "0" Then
            Cells(i, 20).Value = ""
                ElseIf Cells(i, 20).Value < 70 Then
                    Cells(i, 27).Value = Cells(i, 27).Value & "Systolic BP - Out of range"
                ElseIf Cells(i, 20).Value > 250 Then
                    Cells(i, 27).Value = Cells(i, 27).Value & "Systolic BP - Out of range"
        End If
        
        'Diastolic bp
         If Cells(i, 21).Value = "0" Then
            Cells(i, 21).Value = ""
                ElseIf Cells(i, 21).Value < 40 Then
                    Cells(i, 27).Value = Cells(i, 27).Value & "Diastolic BP - Out of range"
                ElseIf Cells(i, 21).Value > 150 Then
                    Cells(i, 27).Value = Cells(i, 27).Value & "Diastolic BP - Out of range"
        End If
                    
        'Dia > Sys?
        If Cells(i, 21).Value > Cells(i, 20).Value Then Cells(i, 27).Value = Cells(i, 27).Value & "Diastolic greater than Systolic"
        End If
    
    Next i
    
    
    
    
    
    End Sub

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: help debugging for/next, if/elseif/endif

    What do you need help with?

    The only problem I can see is the very last End If.

    It's not needed as the very last If is all on one line.
    If posting code please use code tags, see here.

  3. #3
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: help debugging for/next, if/elseif/endif

    what is happening that shouldn't or not happening that should?
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  4. #4
    Registered User
    Join Date
    05-14-2013
    Location
    California
    MS-Off Ver
    Excel 2013
    Posts
    78

    Re: help debugging for/next, if/elseif/endif

    it is not replacing the 0's with blanks, and it is skipping everything after waist circ/bmi. Deleting the last end if now, thank you.

  5. #5
    Registered User
    Join Date
    05-14-2013
    Location
    California
    MS-Off Ver
    Excel 2013
    Posts
    78

    Re: help debugging for/next, if/elseif/endif

    also, i'm hoping it is clearing the 0's. then comparing to the greater than less than... will it still say cell is out of range if it is comparing a blank cell to <4?

  6. #6
    Registered User
    Join Date
    05-14-2013
    Location
    California
    MS-Off Ver
    Excel 2013
    Posts
    78

    Re: help debugging for/next, if/elseif/endif

    actually, skipping everything after weight...

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: help debugging for/next, if/elseif/endif

    Remove the quotes around 0, unless you are actually looking for the string '0'.

    By the way, the code probably isn't skipping anything, it just appears so because nothing is happening.

    To check that you can step through it with F8.
    Last edited by Norie; 07-12-2013 at 11:49 AM.

  8. #8
    Registered User
    Join Date
    05-14-2013
    Location
    California
    MS-Off Ver
    Excel 2013
    Posts
    78

    Re: help debugging for/next, if/elseif/endif

    when stepping through, it is skipping after this line
    If Cells(i, 13).Value = "" And Cells(i, 11).Value = "" Then
    it then picks up on last four social and then is done running

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: help debugging for/next, if/elseif/endif

    Oops, looks like I misread the code.:oops:

    I blame lack of indentation.

    PS You could post an example workbook with dummy data.

  10. #10
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: help debugging for/next, if/elseif/endif

    all the subsequent code is dependent on that test and will only run if both those conditions are true-which also happens to exclude most of the following code if not all

  11. #11
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: help debugging for/next, if/elseif/endif

    None of the code after that point is executed?

  12. #12
    Registered User
    Join Date
    05-14-2013
    Location
    California
    MS-Off Ver
    Excel 2013
    Posts
    78

    Re: help debugging for/next, if/elseif/endif

    correct, i do have values that are wrong, and 0's even though i have removed the quotes, that arent getting deleted.. I would post a example doc but its health info so i cant

  13. #13
    Registered User
    Join Date
    05-14-2013
    Location
    California
    MS-Off Ver
    Excel 2013
    Posts
    78

    Re: help debugging for/next, if/elseif/endif

    thanks joseph, when i move that to the bottom the rest runs, how can i word it so that i dont have that error? running into a meeting so my posts will be delayed for a little while

  14. #14
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: help debugging for/next, if/elseif/endif

    perhaps just change
    If Cells(i, 13).Value = "" And Cells(i, 11).Value = "" Then
            Cells(i, 27).Value = Cells(i, 27).Value & "WaistC/BMI - Missing"
    to
    If Cells(i, 13).Value = "" And Cells(i, 11).Value = "" Then
            Cells(i, 27).Value = Cells(i, 27).Value & "WaistC/BMI - Missing"
    End If

  15. #15
    Registered User
    Join Date
    05-14-2013
    Location
    California
    MS-Off Ver
    Excel 2013
    Posts
    78

    Re: help debugging for/next, if/elseif/endif

    one last thing... im getting a type error whenever i try to get a this value. Im trying to make an if statement to compare
    cells(i,14) to  Round(cells (i,15)+(i,16)+((i,17)/5))
    , and if the difference is greater than one, (positive or negative) it will produce another error in cells (i,27).

    What is the best way to do that?
    Thanks you guys

  16. #16
    Registered User
    Join Date
    05-14-2013
    Location
    California
    MS-Off Ver
    Excel 2013
    Posts
    78

    Re: help debugging for/next, if/elseif/endif

    thanks guys, working now. Appreciate yalls help!

  17. #17
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646
    Are you trying to round the sum of columns 15,16 and 17 divided by 5?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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