Excel Message box, fields that are > 0

[Jimjai]

Hi,

I would like to create a macro button that loops thru a column, if any
values on that column is greater than 0 then pop the message saying "pass
due" Thanks

Jimmy

 

[Mike H]

Hi,

Right click your sheet tab, view code and paste this in and run it. You are
prompted for a column number to scan

Sub standard()
Dim MaxCol As Long, MyCol As Long, x As Long
MaxCols = ActiveSheet.Columns.Count
Do
MyCol = Val(InputBox("Enter column to scan"))
Loop Until MyCol <= MaxCols
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For x = 1 To lastrow
If Cells(x, MyCol).Value > 0 Then
MsgBox "Pass Due in cell " & Cells(x, MyCol).Address
End If
Next
End Sub

Mike

 

[Mike H]

slight change in case there is text in the range

Sub standard()
Dim MaxCol As Long, MyCol As Long, x As Long
MaxCols = ActiveSheet.Columns.Count
Do
MyCol = Val(InputBox("Enter column to scan"))
Loop Until MyCol <= MaxCols
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For x = 1 To lastrow
If IsNumeric(Cells(x, MyCol).Value) And _
Cells(x, MyCol).Value > 0 Then
MsgBox "Pass Due in cell " & Cells(x, MyCol).Address
End If
Next
End Sub

Mike

 

[Jimjai]

Thanks for reply Miki,

I am getting "run-time error "1004": Application-defined or object-defined
error"

Jim

 

[Mike H]

Hi,

From your mmessage header I guess your trying to copy the used range of each
sheet so try this

Sub group()
Dim i As Long
For i = 2 To Sheets.Count
lastrow = Worksheets("total").Cells(Rows.Count, "A").End(xlUp).Row
Worksheets(i).UsedRange.Copy _
Destination:=Worksheets("total").Cells(lastrow + 1, 1)
Next
End Sub

Mike

 

[Mike H]

Hi,

You must enter a column NUMBER on the input box not a letter. Column A=1 etc

Mike

 

[Mike H]

OOPS wrong thread

Hi,

From your mmessage header I guess your trying to copy the used range of each
sheet so try this

Sub group()
Dim i As Long
For i = 2 To Sheets.Count
lastrow = Worksheets("total").Cells(Rows.Count, "A").End(xlUp).Row
Worksheets(i).UsedRange.Copy _
Destination:=Worksheets("total").Cells(lastrow + 1, 1)
Next
End Sub

Mike

 

[Chip Pearson]

If you need only to determine whether there exists a value greater
than zero but do not need to get the actual location of that value,
use

Sub AAA()
Dim R As Range
With Worksheets("Sheet1")
Set R = Range(.Cells(1, "A"), _
.Cells(.Rows.Count, "A").End(xlUp))
End With
If Application.Sum(R.Value) > 0 Then
MsgBox "Past Due"
End If
End Sub


If you do need to determine where the non-zero value occurs, use

Sub BBB()
Dim RR As Range
Dim R As Range
With Worksheets("Sheet1")
Set RR = Range(.Cells(1, "A"), _
.Cells(.Rows.Count, "A").End(xlUp))
End With
For Each R In RR.Cells
If IsNumeric(R.Value) Then
If R.Value > 0 Then
Application.Goto R, True
MsgBox "Past Due"
Exit For
End If
End If
Next R
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)