Solved: VBA not reading .Value

[JimS]

I have a formula in cell AA10 on sheet labeled "Metrics" that will be greater then zero if it detects some incomplete cells. This formula works fine.

Now I want to display a message if cell AA10 >0.

Here's the code, but it does not work, it goes from the If to the End IF.

For some reason it's not detecting that AA10 is greater then 0.




Sheets("Metrics").Select

ActiveSheet.Range("AA10").Select

If Range("AA10").Value > 0 Then
MsgBox ("1 or more of your Employees has reported Overtime _
without entering a Reason Code - See Names in RED")
End If

Thanks for any help...

Jim

[Simon Lloyd]

It's not that it's not working, it's how you are using it, where are you putting the code? how are you triggering it?

If you put this in the code module for the worksheet (right click the sheet tab and choose view code) everytime the value in AA10 is changed it will give the message box!
Private Sub Worksheet_Change(ByVal Target As Range)
If Me.Range("AA10").Value > 0 Then
MsgBox ("1 or more of your Employees has reported Overtime _
without entering a Reason Code - See Names in RED")
End If
End Sub

[JimS]

It's embeded in a bigger macro that imports data to a "source" sheet that the formulas on the "Metrics" sheet reference. I only need this to occur once.

[BrianMH]

Option Explicit
Sub test()
If Sheets("metrics").Range("AA10").Value > 0 Then
MsgBox ("1 or more of your Employees has reported Overtime " & _
"without entering a Reason Code - See Names In RED")
End If

End Sub


This works fine for me. From what I have learned on the forum its better to deal with the range directly. There is no need to select the cell. When I pasted your code in it went red because the new line delimiter didn't work. I would imagine its because it was still in the quotes.

[Simon Lloyd]

It's embeded in a bigger macro that imports data to a "source" sheet that the formulas on the "Metrics" sheet reference. I only need this to occur once.Why not post the whole code?

[Chris Bode]

use following code instead


Private Sub mySub()

If Worksheets("Metrics").Range("AA10").Value > 0 Then
MsgBox ("1 or more of your Employees has reported Overtime" & _
"without entering a Reason Code - See Names In RED")
End If

End Sub

[Simon Lloyd]

use following code instead


Private Sub mySub()

If Worksheets("Metrics").Range("AA10").Value > 0 Then
MsgBox ("1 or more of your Employees has reported Overtime" & _
"without entering a Reason Code - See Names In RED")
End If

End Sub
Chris, for our benefit, could you tell us what is so different in your code compared to all those posted?

[mdmackillop]

It's not that it's not working, it's how you are using it, where are you putting the code? how are you triggering it?
The message box is trivial, we still need a fuller answer to this. When is the code expected to run? What user action is required for this to happen?

[JimS]

It works now, the "If Sheets("Disk Metrics").Range("AA10").Value > 0 Then" did the trick.

Thanks to everyone...