Testing if a variable is greater than 0

Question

I am trying to test to see if a variable (number in an excel cell) is greater than 0. I have two variables being tested against one another and then tested to see if it is greater than 0. The test between the two variables works, but then when I test if it is greater than or less than 0 it skips right over it. I believe it is the way I have defined them? But when I try and define them as an integer or does not work either. Can someone please help with what I am missing? Thank you in advance for your help... I appreciate it.

Sub BA()
Dim Sh As Worksheet
Dim Skill As Range
Dim Allocation As Range
Dim Selection As Range
Dim SelectionText As String
Dim AllocationText As String
Dim AllocationDetract As String
Dim SelectionDetract As String
Dim SelectionLess As String
Dim AllocationLess As String
Dim AllocationContr As Double
Dim SkillContr As Double





SelectionText = " Text 1 "
AllocationText = " Text 2 "
SelectionLess = " Text 3"
SelectionDetract = "Text 4"
AllocationDetract = "Text 5."
AllocationLess = " Text 6"



    For Each Sh In ThisWorkbook.Worksheets
        With Sh.UsedRange
    Set Skill = .Cells.Find(What:="Skill")

Set Allocation = Skill.Offset(15, 1)
Set Selection = Skill.Offset(15, 0)

AllocationContr = CDbl(Allocation)
SkillContr = CDbl(Selection)

    If AllocationContr > SkillContr Then
        If SkillContr > 0 Then
            Range("B1").Value = Range("B1") & AllocationText & 
SelectionLess
                ElseIf SkillContr < Test Then
                    Range("B1").Value = Range("B1") & AllocationText & 
SelectionDetract
        End If
    End If
    If AllocationContr < SkillContr Then
        If AllocationContr > 0 Then
            Range("B1").Value = Range("B1") & SelectionText & 
AllocationLess
                ElseIf AllocationContr < 0 Then
                    Range("B1").Value = Range("B1") & SelectionText & 
AllocationDetract
        End If
    End If

End With
Set Skill = Nothing
Next

End Sub

What do you mean by "skips right over it"? If the variable is <= 0, then the code will jump straight to `End If`. — Rory, May 17 '18 at 13:40
Please give a [mcve] which provides a reproducible example of where a comparison with 0 behaves unexpectedly. The above code has a lot of extraneous details, as well as external dependencies which prevents us from being able to run it without undue effort on our part. — John Coleman, May 17 '18 at 13:40
If the value of either `Allocation` or `Selection` is a non-number string, the conversion to double will result in `0` which is not greater than `0`. You should try using either `Debug.Print "String"` w/ the immediates window or stepping through your code to check the values of the variables.... — Mistella, May 17 '18 at 13:40
Thank you for the info everyone. I mean jump to End If when I say skip right over. Allocation or Selection are both integers, not strings. However, I used the conversion in case formatting gets changed. When I step through the code to debug the variables are correct which is why the first test of AllocationContr > SkillContr is working. — BRolf, May 17 '18 at 13:44
@Mistella appologies, you are correct when I step through the code AllocationContr is 0. Is there a way to convert that to an integer? (It is a number in the excel workbook) The SkillContr produces the correct number. — BRolf, May 17 '18 at 13:47
Maybe try taking a look at the answers here: https://stackoverflow.com/a/6202469/9259306 — Mistella, May 17 '18 at 13:51
@Mistella The problem was the offset function that I was using. For some reason searching for Skill only worked for one of the variables but not both. Your thought of testing 0 was correct and I was able to figure out from there. Thank you for your help. — BRolf, May 17 '18 at 13:54
You're welcome. Would you mind posting the the solution you found as an answer, so other knowledge-seekers won't have to wade through all the comments? — Mistella, May 17 '18 at 13:58

score 0 · Answer 1 · edited May 17 '18 at 15:37

The answer revolved around the definition of the offset. The offset was grabbing an incorrect value thus when I converted it, it converted the variable to a 0. So 0 > 0 did not make sense to the code. I changed the anchor in which the code looks for "Selection" rather than Skill and changed the offset as well. I have posted the full answer corrected below.

Sub BA()

    Dim Sh As Worksheet
    Dim Skill As Range
    Dim Allocation As Range
    Dim Selection As Range
    Dim SelectionText As String
    Dim AllocationText As String
    Dim AllocationDetract As String
    Dim SelectionDetract As String
    Dim SelectionLess As String
    Dim AllocationLess As String
    Dim AllocationContr As Double
    Dim SkillContr As Double


    SelectionText = " Text 1 "
    AllocationText = " Text 2 "
    SelectionLess = " Text 3"
    SelectionDetract = "Text 4"
    AllocationDetract = "Text 5."
    AllocationLess = " Text 6"


    For Each Sh In ThisWorkbook.Worksheets
        With Sh.UsedRange
            Set Skill = .Cells.Find(What:="Selection")

            Set Allocation = Skill.Offset(14, 0)
            Set Selection = Skill.Offset(14, -1)

            AllocationContr = CDbl(Allocation)
            SkillContr = CDbl(Selection)

            If AllocationContr > SkillContr Then
                If SkillContr > 0 Then
                    Range("B1").Value = Range("B1") & AllocationText &
                    SelectionLess
                ElseIf SkillContr < Test Then
                    Range("B1").Value = Range("B1") & AllocationText &
                    SelectionDetract
                End If
            End If

            If AllocationContr < SkillContr Then
                If AllocationContr > 0 Then
                    Range("B1").Value = Range("B1") & SelectionText &
                    AllocationLess
                ElseIf AllocationContr < 0 Then
                    Range("B1").Value = Range("B1") & SelectionText &
                    AllocationDetract
                End If
            End If

        End With
        Set Skill = Nothing
    Next

End Sub

Testing if a variable is greater than 0

1 Answers1