Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
help with VBA code re comparing cells and generating message with detailed info
Hi there,
I wonder if someone can help me with code to deal with a particular issue: I have two columns of cells which capture two different pieces of data in real time. Call them X and Y, with an X and a Y in every row (or most rows). One of the pieces of data comes with a time stamp in the data source. The other piece of data I time stamp through VBA code (i.e. checking when that part of the worksheet changes and saving that time). So both X and Y have a column saying what time they arrived. What I need to do is, checking the entire column of individual X and Y values on a by-row comparison is "If X Y and the time X arrived is after the time Y arrived then pop up a message box saying 'X exceeded Y at zz:zz time." Obviously it would be easy to make a cell which could compare X and Y and the times, but I need to do this through code because I need the message box to have the detailed info about X and Y and the times at the moment the message is triggered. And also because X and Y will change frequently so a cell without a persistent message (until someone closes it) is likely to be missed. any help would be greatly appreciated! if you can, please e-mail me directly in addition to responding to the group. thanks in advance, Ross |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
help with VBA code re comparing cells and generating message with detailed info
Is there a way to do this by naming ranges instead of using columns
and offsets (in case I insert or delete columns which then make the offset numbers wrong)? For example compare range("xvalues") to range("yvalues") and range("xtimes") to range("ytimes") one row at a time? Thanks again Ross "J.E. McGimpsey" wrote in message ... One way: Assume X is in column A, datestamp for X in column B, Y in column C, datestamp for Y in column D (adjust as needed): Public Sub CheckValuesAndDateStamps() Dim cell As Range For Each cell In Range("A1:A" & _ Range("A" & Rows.Count).End(xlUp).Row) With cell If .Value .Offset(0, 2).Value Then If .Offset(0, 1).Value .Offset(0, 3).Value Then _ MsgBox "Row " & cell.Row & ":" & vbNewLine _ & .Value & " exceeded " & _ .Offset(0, 2).Value & " at " & _ Format(.Offset(0, 1).Value, "hh:mm") & _ " time." End If End With Next cell End Sub In article , (Ross) wrote: Hi there, I wonder if someone can help me with code to deal with a particular issue: I have two columns of cells which capture two different pieces of data in real time. Call them X and Y, with an X and a Y in every row (or most rows). One of the pieces of data comes with a time stamp in the data source. The other piece of data I time stamp through VBA code (i.e. checking when that part of the worksheet changes and saving that time). So both X and Y have a column saying what time they arrived. What I need to do is, checking the entire column of individual X and Y values on a by-row comparison is "If X Y and the time X arrived is after the time Y arrived then pop up a message box saying 'X exceeded Y at zz:zz time." Obviously it would be easy to make a cell which could compare X and Y and the times, but I need to do this through code because I need the message box to have the detailed info about X and Y and the times at the moment the message is triggered. And also because X and Y will change frequently so a cell without a persistent message (until someone closes it) is likely to be missed. any help would be greatly appreciated! if you can, please e-mail me directly in addition to responding to the group. thanks in advance, Ross |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically generating a city name based on entered zip code | Excel Discussion (Misc queries) | |||
Listing info from Annual, to a more detailed worksheet by Month | Excel Discussion (Misc queries) | |||
code to 'put' calc displayed in message box in cells | Excel Discussion (Misc queries) | |||
comparing a column of cell and then copy info to other cells | Excel Discussion (Misc queries) | |||
My cell formula is generating an unwanted e-mail message. | Excel Worksheet Functions |