help with VBA code re comparing cells and generating message with detailed info
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 |
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 |
All times are GMT +1. The time now is 02:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com