Capturing data only when a column "Value" changes
Thanks -- I considered it before and your comments just reinforced not using
"Time" as a name.
Thanks again..
"Dave Peterson" wrote:
I don't think I'd use Time as a variable name. It's way to close in spelling to
the Time keyword that VBA uses.
Dim myTime as date
???
Joel wrote:
I think this ius a formating issue. try formating the summary sheet and see
what happens.
You may want to change theswe two statements
from
Time = .Cells(RowCount, ColCount).Offset(0, 1)
.Cells(NewRowCount, ColCount).Offset(0, 1) = Time
to
Time = .Cells(RowCount, ColCount).Offset(0, 1).value
.Cells(NewRowCount, ColCount).Offset(0, 1).value = Time
Also delare time
Dim Time as Date
Someplace the Time value is being truncated. time is really a DOUBLE
varianle which is 32 bits. It is probably getting converted to a long which
is 16 bits. declaring Time as Date wil solve this problem.
"ShagNasty" wrote:
Sorry to bother again...
I get 01/00/00 in all the date section of the time fields. The time section
is OK..
Ideas??
"Joel" wrote:
You are right that I left out a pedriod. I also used real dates and times in
my testing. I took your posted data and ran my code with out any errors.
the format of the data and time will not matter when running this code.
I think the problem is with a header row. I did not run my tests with any
headers. So try changing this line of code if you have a header row
from
RowCount = 1
to
RowCount = 2
Also check you sheet names. My code is using Sheet1 and Sheet2. change as
required.
"ShagNasty" wrote:
Thanks for the help Joel..
The blanks were issues that my initial attempt at "programming" (using
=if(or(or(c2=c1, etc.,etc) would see as a change and the next non-blank VALUE
would be captured.
I'm sure what you gave me is a Macro; however; when I attempt to run it I
get a "Run-time error '13';" "Type mismatch". I figure it's an issue with
the TIME column which is a date & time field instead of a text field. (Ln 12,
Col1)
Also -- should their be a "." before Cells in the Value=Cells(xxx)? (Ln13,
Col1)
Again, I appreciate the help and all the magic you and your peers can do
with this stuff...
Shag...
"Joel" wrote:
You have some entries where the VALUE was blank. Not sure what to do.
Sub FixWidget()
With Sheets("Sheet1")
ColCount = 1
'loop 3 columns at a time until all data is processed
Do While .Cells(1, ColCount) < ""
RowCount = 1
NewRowCount = 1
'loop until no more data in column
Do While .Cells(RowCount, ColCount) < ""
Tag = .Cells(RowCount, ColCount)
Time = .Cells(RowCount, ColCount).Offset(0, 1)
Value = Cells(RowCount, ColCount).Offset(0, 2)
With Sheets("sheet2")
If RowCount = 1 Then
'Keep track of last state
State = Value
.Cells(NewRowCount, ColCount) = Tag
.Cells(NewRowCount, ColCount).Offset(0, 1) = Time
.Cells(NewRowCount, ColCount).Offset(0, 2) = Value
NewRowCount = NewRowCount + 1
Else
'if value changes then add new row
If Value < State Then
State = Value
.Cells(NewRowCount, ColCount) = Tag
.Cells(NewRowCount, ColCount).Offset(0, 1) = Time
.Cells(NewRowCount, ColCount).Offset(0, 2) = Value
NewRowCount = NewRowCount + 1
End If
End If
End With
RowCount = RowCount + 1
Loop
ColCount = ColCount + 3
Loop
End With
End Sub
"ShagNasty" wrote:
What I need to accomplish is to get three items; tag, time, and value,
automatically copied into a âœcleanâ worksheet every time the Widgets value
changes -- ON/OFF. As you can see from my mess below, I ainât got the
faintest cue what Iâm doingâ¦.
Phase 1
I retrieve history on âœTagâ, âœTimeâ, and âœValueâ every minute during a
startup/shutdown process event â I may have to go back 30 days. Raw data is
exported from a large db. I probably have 90 columns with around 1000 rows
Tag Time Value Tag Time Value
Widget A 09/30/2008 11:55:00 ON Widget B 09/30/2008 11:55:00 ON
Widget A 09/30/2008 11:56:00 ON Widget B 09/30/2008 11:56:00 ON
Widget A 09/30/2008 11:57:00 OFF Widget B 09/30/2008 11:57:00 ON
Widget A 09/30/2008 11:58:00 OFF Widget B 09/30/2008 11:58:00 OFF
Widget A 09/30/2008 11:59:00 OFF Widget B 09/30/2008 11:59:00 OFF
Widget A 10/01/2008 00:00:00 OFF Widget B 10/01/2008 00:00:00
Widget A 10/01/2008 00:01:00 ON Widget B 10/01/2008 00:01:00
Widget A 10/01/2008 00:02:00 OFF Widget B 10/01/2008 00:02:00 ON
Widget A 10/01/2008 00:03:00 ON Widget B 10/01/2008 00:03:00 ON
Widget A 10/01/2008 00:04:00 ON Widget B 10/01/2008 00:04:00 ON
Widget A 10/01/2008 00:05:00 OFF Widget B 10/01/2008 00:05:00 OFF
Widget A 10/01/2008 00:06:00 ON Widget B 10/01/2008 00:06:00 ON
Widget A 10/01/2008 00:07:00 ON Widget B 10/01/2008 00:07:00 ON
Widget A 10/01/2008 00:08:00 Widget B 10/01/2008 00:08:00
Widget A 10/01/2008 00:09:00 Widget B 10/01/2008 00:09:00
Widget A 10/01/2008 00:10:00 ON Widget B 10/01/2008 00:10:00 ON
Widget A 10/01/2008 00:11:00 OFF Widget B 10/01/2008 00:11:00 OFF
Widget A 10/01/2008 00:12:00 ON Widget B 10/01/2008 00:12:00 OFF
Widget A 10/01/2008 00:13:00 ON Widget B 10/01/2008 00:13:00 OFF
Widget A 10/01/2008 00:14:00 OFF Widget B 10/01/2008 00:14:00 ON
Widget A 10/01/2008 00:15:00 OFF Widget B 10/01/2008 00:15:00 OFF
Widget A 10/01/2008 00:16:00 ON Widget B 10/01/2008 00:16:00 ON
-----------------------------------------------------------------------------------------------------------------------------------------------
Phase 2
Tag Time Value Tag Time Value
Widget A 09/30/2008 11:55:00 ON Widget B 09/30/2008 11:55:00 ON
Widget A 09/30/2008 11:57:00 OFF Widget B 09/30/2008 11:58:00 OFF
Widget A 10/01/2008 00:01:00 ON
Widget A 10/01/2008 00:02:00 OFF Widget B 10/01/2008 00:02:00 ON
Widget A 10/01/2008 00:03:00 ON
Widget A 10/01/2008 00:05:00 OFF Widget B 10/01/2008 00:05:00 OFF
Widget A 10/01/2008 00:06:00 ON Widget B 10/01/2008 00:06:00 ON
Widget A 10/01/2008 00:11:00 OFF Widget B 10/01/2008 00:11:00 OFF
Widget A 10/01/2008 00:12:00 ON
Widget A 10/01/2008 00:14:00 OFF Widget B 10/01/2008 00:14:00 ON
Widget A 10/01/2008 00:16:00 ON Widget B 10/01/2008 00:16:00 ON
-----------------------------------------------------------------------------------------------------------------------------------------------------
Phase 3
This is what I need to have at the end of the day -- but slowly getting
here, however using a few manual tasks.â¦
Tag Time Value Tag Time Value
Widget A 09/30/2008 11:55:00 ON Widget B 09/30/2008 11:55:00 ON
Widget A 09/30/2008 11:57:00 OFF Widget B 09/30/2008 11:58:00 OFF
Widget A 10/01/2008 00:01:00 ON Widget B 10/01/2008 00:02:00 ON
Widget A 10/01/2008 00:02:00 OFF Widget B 10/01/2008 00:05:00 OFF
Widget A 10/01/2008 00:03:00 ON Widget B 10/01/2008 00:06:00 ON
Widget A 10/01/2008 00:05:00 OFF Widget B 10/01/2008 00:11:00 OFF
Widget A 10/01/2008 00:06:00 ON Widget B 10/01/2008 00:14:00 ON
Widget A 10/01/2008 00:11:00 OFF Widget B 10/01/2008 00:16:00 OFF
Widget A 10/01/2008 00:12:00 ON
Widget A 10/01/2008 00:14:00 OFF
Widget A 10/01/2008 00:16:00 ON
--
Dave Peterson
|