Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Message box, fields that are 0
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Message box, fields that are 0
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 "Jimjai" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Message box, fields that are 0
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 "Mike H" wrote: 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 "Jimjai" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Message box, fields that are 0
Thanks for reply Miki,
I am getting "run-time error "1004": Application-defined or object-defined error" Jim "Mike H" wrote: 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 "Mike H" wrote: 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 "Jimjai" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Message box, fields that are 0
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 "Jimjai" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Message box, fields that are 0
Hi,
You must enter a column NUMBER on the input box not a letter. Column A=1 etc Mike "Jimjai" wrote: Thanks for reply Miki, I am getting "run-time error "1004": Application-defined or object-defined error" Jim "Mike H" wrote: 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 "Mike H" wrote: 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 "Jimjai" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Message box, fields that are 0
OOPS wrong thread
"Mike H" wrote: 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 "Jimjai" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Message box, fields that are 0
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) On Wed, 18 Feb 2009 10:57:01 -0800, Jimjai wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
fields names do not show in excel 2007 pivot table fields list | New Users to Excel | |||
comparing 2 fields/cells and displaying a message programitcally | Excel Programming | |||
Excel SHOULD NOT AUTO-CHANGE formated text fields to DATE FIELDS! | Excel Worksheet Functions | |||
Replace Excel Message w/Custom Message | Excel Programming | |||
Fix too few data fields error message when merging excel and word | Excel Discussion (Misc queries) |