VAB, Message if Positive Values

[Willmonbo]

I am dealing with a dataset that has columns that for the most part should only have negative values entered in them, when you click away from the sheet or you're closing the file and there are positive numbers entered in the predefined columns, I want a warning message to come up (it should allow you to proceed).

your help is appreciated.

 

[fredlo2008]

Hi Willmondo,

This will check for possitive values in Column A

Sub CheckPositves()

Dim lrow As Long
Dim mymsg As String


lrow = Range("A" & Rows.Count).End(xlUp).Row


For i = 1 To lrow
    
    If Cells(i, 1).Value > 0 Then
        mymsg = MsgBox("There is positive value in Row: " & i & " in Column A" & vbCr & _
                        "Would you like to continue?", vbInformation + vbYesNo, "Positive value")
        If mymsg = vbNo Then
            Cells(i, 1).Activate
            Exit Sub
        End If
    End If
Next i


End Sub

 

[Willmonbo]

Thanks Fredlo,

I have multiple columns that I need this VBA to check for positive values, where should I indicate which ones they are?
Also, it errors out when I get the warning message if I click on “No”.

Can you please take another stab at it?

I’m grateful for your time and help!

 

[Mahesh_P]

Try :

Sub Mtest()
Dim LR As Long, PR As Long, s As Long, m As Long
Dim Temp As String
LR = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count
PR = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For s = LR To 1 Step -1
For m = PR To 1 Step -1
If Application.WorksheetFunction.CountIf(Cells(s, m), ">0") = 1 Then
Temp = MsgBox("Positive Value Found in" & Cells(s, m).Address & "  Do u want to Continue ?", vbYesNo, "Warning")
If Temp = vbYes Then
Cells(s, m).Select
Else
Exit Sub
End If
End If
Next m
Next s
Application.ScreenUpdating = True
End Sub

 

[Willmonbo]

Thanks Mahesh,

now it errors out when I click yes to continue,

the error I'm getting is a run-time error 1004 "select method of range class failed