If ElseIf statement can not adapt to new data?

[intoit]

Hi,

I'm using the code below to enter the word 'more' or 'less' within a
particular cell, contingent upon the numerical values within two other cells.
It works when I run the macro once, but if one alters the values within the
two numerical cells, and the macro is run again, the code does not adjust to
change the word 'more' or 'less'. It seems to get stuck with repeating the
same 'less' or 'more' over and over again (irrespective of the numercial
values within the cells)? Any ideas how the code can be modified so that it
can be run more than once? Thanks.

If ("Compare_Role!B16.Text") > ("Compare_Role!C16.Text") Then
Range("Compare_Role!E16") = "more"
ElseIf ("Compare_Role!B16.Text") < ("Compare_Role!C16.Text") Then
Range("Compare_Role!E16") = "less"
End If

 

[Jacob Skaria]

("Compare_Role!B16.Text") do not get the value of B16 instead your code
compares the text string ("Compare_Role!B16.Text") and
("Compare_Role!C16.Text") ..Try the below..

Dim ws As Worksheet
Set ws = ActiveWorkbook.Sheets("Compare_Role")
If ws.Range("B16") > ws.Range("C16") Then
ws.Range("E16") = "more"
ElseIf ws.Range("B16") < ws.Range("C16") Then
ws.Range("E16") = "less"
End If

If this post helps click Yes

 

[AltaEgo]

Try either of the following:



Sub test()

With Worksheets("compare_role")
If .Range("B16") > .Range("C16") Then
.Range("E16") = "more"
Else
.Range("E16") = "less"
End If
End With
End Sub

Sub test2()

If Worksheets("compare_role").[B16] > Worksheets("compare_role").[C16] Then
Worksheets("compare_role").[E16] = "more"
Else
Worksheets("compare_role").[E16] = "Less"
End If

End Sub

 

[intoit]

Thanks, Jacob. Interesting, the workbook had to be specified. Bizarre,
considering that the code didn't work (i.e., more than once), even when a
single workbook was open.