Ariel,

Thanks again for your help! I incorporated your new code and had to edit
just a few things.

1) I needed to make Dim = ReDim
2) I needed to add Range() within the Average and Max formulas. For
example, Application.WorksheetFunction.Average(Range(active cell.offset....))

Other than that, the code is MUCH MUCH faster now! AND I learned some new
stuff! THANK YOU SO MUCH!

Matt


"Ariel Dugan" wrote:

Hi Matt,

The code below is untested. If it doesn't compile, please send me your
entire workbook to test it on, as some of the procedures that your function
calls weren't present. Basically I just put the data in an array as you
suggested.

Function Label_Av(Cycles As Variant)

'''Declare array
Dim arrValues(1 To Cycles, 1 To 8) As Variant

CellsShifted = 0

For i = 1 To Cycles

If Not ActiveCell.Offset(60 * (i - 1), -1).Value = """" Then

'''''''''''''''''
'Label Modes '
'''''''''''''''''

ActiveCell.Range("A" & 60 * (i - 1) + 1 - CellsShifted &
":A" & 60 * (i - 1) + 40 - CellsShifted).Value = "Mode 1"
ActiveCell.Range("A" & 60 * (i - 1) + 41 - CellsShifted &
":A" & 60 * (i - 1) + 46 - CellsShifted).Value = "Mode 2"
ActiveCell.Range("A" & 60 * (i - 1) + 47 - CellsShifted &
":A" & 60 * (i - 1) + 56 - CellsShifted).Value = "Mode 3"
ActiveCell.Range("A" & 60 * (i - 1) + 57 - CellsShifted &
":A" & 60 * (i - 1) + 60 - CellsShifted).Value = "Mode 4"

'Check to see if time values match up to labels and fix
ifnot correct

Time_Diff = ActiveCell.Offset(60 * (i - 1) + 60 -
CellsShifted, -23).Value - ActiveCell.Offset(60 * (i - 1) + 1 -
CellsShifted, -23).Value

If Not Time_Diff < 60.9 Then '<60.9 because some
valuesrecorded are not exactly 1 second
CellsShifted = CellsShifted + Fix_Time(i)
Else
End If

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''
' Perform calcs on four modes and place in separate table '
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''

'Time at end of Mode 4
arrValues(i + 9, 1) = ActiveCell.Offset(0, -23).Range("A" &
60 * (i - 1) + 60 - CellsShifted)
'Range("Y" & i + 9).Value =
ActiveCell.Offset(0, -23).Range("A" & 60 * (i - 1) + 60 - CellsShifted)

'Control Oxygen for Modes 3 and 4 - 5 sec after start ofMode
3
arrValues(i + 9, 2) =
Application.WorksheetFunction.Average(ActiveCell.O ffset(0, -20).Range("A" &
60 * (i - 1) + 47 + 5 - CellsShifted & ":A" & 60 * (i - 1) + 60 -
CellsShifted).Address)
'Range("Z" & i + 9).FormulaR1C1 = "=Average(" &
ActiveCell.Offset(0, -20).Range("A" & 60 * (i - 1) + 47 + 5 - CellsShifted &
":A" & 60 * (i - 1) + 60 - CellsShifted).Address(ReferenceStyle:=xlR1C1) &
")"

'Front UEGO averaged for Mode 2 and 3 - 3 sec after start
ofMode 2
arrValue(i + 9, 3) =
Application.WorksheetFunction.Average(ActiveCell.O ffset(0, -8).Range("A" &
60 * (i - 1) + 41 + 3 - CellsShifted & ":A" & 60 * (i - 1) + 56 -
CellsShifted).Address)
'Range("AA" & i + 9).FormulaR1C1 = "=Average(" &
ActiveCell.Offset(0, -8).Range("A" & 60 * (i - 1) + 41 + 3 - CellsShifted &
":A" & 60 * (i - 1) + 56 - CellsShifted).Address(ReferenceStyle:=xlR1C1) &
")"

'Inlet Temp is average temperature at mode 1 - 10
lastseconds averaged
arrValue(i + 9, 4) =
Application.WorksheetFunction.Average(ActiveCell.O ffset(0, -11).Range("A" &
60 * (i - 1) + 1 + 30 - CellsShifted & ":A" & 60 * (i - 1) + 40 -
CellsShifted).Address)
'Range("AB" & i + 9).FormulaR1C1 = "=Average(" &
ActiveCell.Offset(0, -11).Range("A" & 60 * (i - 1) + 1 + 30 - CellsShifted &
":A" & 60 * (i - 1) + 40 - CellsShifted).Address(ReferenceStyle:=xlR1C1) &
")"

'Average Bed T for Mode 1 - 10 last seconds
arrValue(i + 9, 5) =
Application.WorksheetFunction.Average(ActiveCell.O ffset(0, -10).Range("A" &
60 * (i - 1) + 1 + 30 - CellsShifted & ":A" & 60 * (i - 1) + 40 -
CellsShifted).Address)
'Range("AC" & i + 9).FormulaR1C1 = "=Average(" &
ActiveCell.Offset(0, -10).Range("A" & 60 * (i - 1) + 1 + 30 - CellsShifted &
":A" & 60 * (i - 1) + 40 - CellsShifted).Address(ReferenceStyle:=xlR1C1) &
")"
'T Max Bed T for all modes
arrValue(i + 9, 6) =
Application.WorksheetFunction.Max(ActiveCell.Offse t(0, -10).Range("A" & 60 *
(i - 1) + 1 - CellsShifted & ":A" & 60 * (i - 1) + 60 -
CellsShifted).Address)
'Range("AD" & i + 9).FormulaR1C1 = "=Max(" &
ActiveCell.Offset(0, -10).Range("A" & 60 * (i - 1) + 1 - CellsShifted & ":A"
& 60 * (i - 1) + 60 - CellsShifted).Address(ReferenceStyle:=xlR1C1) & ")"
'R UEGO peak during Modes 2 and 3

arrValue(i + 9, 7) =
Application.WorksheetFunction.Max(ActiveCell.Offse t(0, -6).Range("A" & 60 *
(i - 1) + 41 - CellsShifted & ":A" & 60 * (i - 1) + 56 -
CellsShifted).Address)
'Range("AE" & i + 9).FormulaR1C1 = "=Max(" &
ActiveCell.Offset(0, -6).Range("A" & 60 * (i - 1) + 41 - CellsShifted & ":A"
& 60 * (i - 1) + 56 - CellsShifted).Address(ReferenceStyle:=xlR1C1) & ")"
'R UEGO peak during Modes 4 and the next cycle's 1
arrValue(i + 9, 8) =
Application.WorksheetFunction.Max(ActiveCell.Offse t(0, -6).Range("A" & 60 *
(i - 1) + 57 - CellsShifted & ":A" & 60 * (i - 1) + 60 + 40 -
CellsShifted).Address)
'Range("AF" & i + 9).FormulaR1C1 = "=Max(" &
ActiveCell.Offset(0, -6).Range("A" & 60 * (i - 1) + 57 - CellsShifted & ":A"
& 60 * (i - 1) + 60 + 40 - CellsShifted).Address(ReferenceStyle:=xlR1C1) &
")"

End If
Next
ActiveSheet.Range("Y" & i + 9, "AF" & i + 9).Value = arrValue
Label_Av = CellsShifted

End Function

Thanks
Ariel



"Matt S" wrote in message
...
yes, I have. It is just the 50 hours second-by-second data is the reason
it's so slow. Maybe some type of array method would be better? My array
knowledge is a little weak.

"Tim Williams" wrote:

Have you tried turning off screen updating and setting calculation to
manual
before running ?
(Don't forget to set them back when done.)

Tim


"Matt S" wrote in message
...
Hi all! The following code works, but takes forever to finish. The
background is that I have LabView recording data from a reactor that
has
four
modes. Sometimes the modes don't exactly go as planned, so some
seconds
are
skipped in a mode. I therefore need to correct the data and correctly
label
the modes.

Once the four modes are labeled correctly, I then need to do several
calculations and place the results in a separate table. The problem is
that
the data is 50 hours long, so doing the calculations takes at least 5
minutes.

If there is a better way to do this, please point me in the right
direction.

Thanks!
Matt


Function Label_Av(Cycles As Variant)

CellsShifted = 0

For i = 1 To Cycles

If Not ActiveCell.Offset(60 * (i - 1), -1).Value = """" Then

'''''''''''''''''
'Label Modes '
'''''''''''''''''

ActiveCell.Range("A" & 60 * (i - 1) + 1 - CellsShifted &
":A" & 60 * (i - 1) + 40 - CellsShifted).Value = "Mode 1"
ActiveCell.Range("A" & 60 * (i - 1) + 41 - CellsShifted
&
":A" & 60 * (i - 1) + 46 - CellsShifted).Value = "Mode 2"
ActiveCell.Range("A" & 60 * (i - 1) + 47 - CellsShifted
&
":A" & 60 * (i - 1) + 56 - CellsShifted).Value = "Mode 3"
ActiveCell.Range("A" & 60 * (i - 1) + 57 - CellsShifted
&
":A" & 60 * (i - 1) + 60 - CellsShifted).Value = "Mode 4"

'Check to see if time values match up to labels and fix
if
not correct
Time_Diff = ActiveCell.Offset(60 * (i - 1) + 60 -
CellsShifted, -23).Value - ActiveCell.Offset(60 * (i - 1) + 1 -
CellsShifted,
-23).Value

If Not Time_Diff < 60.9 Then '<60.9 because some values
recorded are not exactly 1 second
CellsShifted = CellsShifted + Fix_Time(i)
Else
End If


'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''
' Perform calcs on four modes and place in separate
table '

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''

'Time at end of Mode 4
Range("Y" & i + 9).Value = ActiveCell.Offset(0,
-23).Range("A" & 60 * (i - 1) + 60 - CellsShifted)

'Control Oxygen for Modes 3 and 4 - 5 sec after start of
Mode 3
Range("Z" & i + 9).FormulaR1C1 = "=Average(" &
ActiveCell.Offset(0, -20).Range("A" & 60 * (i - 1) + 47 + 5 -
CellsShifted
&
":A" & 60 * (i - 1) + 60 -
CellsShifted).Address(ReferenceStyle:=xlR1C1) &
")"

'Front UEGO averaged for Mode 2 and 3 - 3 sec after
start
of
Mode 2
Range("AA" & i + 9).FormulaR1C1 = "=Average(" &
ActiveCell.Offset(0, -8).Range("A" & 60 * (i - 1) + 41 + 3 -
CellsShifted
&
":A" & 60 * (i - 1) + 56 -
CellsShifted).Address(ReferenceStyle:=xlR1C1) &
")"

'Inlet Temp is average temperature at mode 1 - 10 last
seconds averaged
Range("AB" & i + 9).FormulaR1C1 = "=Average(" &
ActiveCell.Offset(0, -11).Range("A" & 60 * (i - 1) + 1 + 30 -
CellsShifted
&
":A" & 60 * (i - 1) + 40 -
CellsShifted).Address(ReferenceStyle:=xlR1C1) &
")"

'Average Bed T for Mode 1 - 10 last seconds
Range("AC" & i + 9).FormulaR1C1 = "=Average(" &
ActiveCell.Offset(0, -10).Range("A" & 60 * (i - 1) + 1 + 30 -
CellsShifted
&
":A" & 60 * (i - 1) + 40 -
CellsShifted).Address(ReferenceStyle:=xlR1C1) &
")"

'T Max Bed T for all modes
Range("AD" & i + 9).FormulaR1C1 = "=Max(" &
ActiveCell.Offset(0, -10).Range("A" & 60 * (i - 1) + 1 - CellsShifted &
":A"
& 60 * (i - 1) + 60 - CellsShifted).Address(ReferenceStyle:=xlR1C1) &
")"

'R UEGO peak during Modes 2 and 3
Range("AE" & i + 9).FormulaR1C1 = "=Max(" &
ActiveCell.Offset(0, -6).Range("A" & 60 * (i - 1) + 41 - CellsShifted &
":A"
& 60 * (i - 1) + 56 - CellsShifted).Address(ReferenceStyle:=xlR1C1) &
")"

'R UEGO peak during Modes 4 and the next cycle's 1
Range("AF" & i + 9).FormulaR1C1 = "=Max(" &
ActiveCell.Offset(0, -6).Range("A" & 60 * (i - 1) + 57 - CellsShifted &
":A"
& 60 * (i - 1) + 60 + 40 -
CellsShifted).Address(ReferenceStyle:=xlR1C1) &
")"

End If
Next

Label_Av = CellsShifted

End Function