Hi. Long time reader, first time poster.... :)

First, let me say that I am a complete newbie. Now that that's out of
the way. I recorded my first macro today. It is simple, but it gave
me great joy. I apply the macro to determine on/off phone time. What
I would like it to do is to move down the data columns and stop once no
data is found. For example, if the last call occurs on row 323, the
the macro should stop then, without inserting a bunch of zeros because
of the missing values. I hope I explained this properly and that
someone can help this really green dude. I am pasting the macro as is,
below. Thanks in advance.

K.

Range("F1").Select
ActiveCell.FormulaR1C1 = "total time"
Range("G1").Select
ActiveCell.FormulaR1C1 = "on time"
Range("H1").Select
ActiveCell.FormulaR1C1 = "off time"
Range("A2").Select
ActiveWindow.FreezePanes = True
Range("F3").Select
ActiveCell.FormulaR1C1 = "=RC[-5]-R[-1]C[-5]+(RC[-5]R[-1]C[-5])"
Range("G3").Select
ActiveCell.FormulaR1C1 = "=R[-1]C[-3]/(24*3600)"
Range("H3").Select
ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
Range("I3,F:F,G:G,H:H").Select
Range("H1").Activate
Selection.NumberFormat = "h:mm:ss;@"

Recording a macro will give you code that works. But it's usually difficult to
follow when you want to make further changes--but it's very nice to get quick
examples for syntax/properties/methods.

Option Explicit
Sub testme01()

Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

.Range("F1").Value = "total time"
.Range("G1").Value = "on time"
.Range("H1").Value = "off time"

.Range("A1").Select 'make sure row 1 is visible
.Range("a2").Select
ActiveWindow.FreezePanes = True

.Range("F3:F" & LastRow).FormulaR1C1 _
= "=RC[-5]-R[-1]C[-5]+(RC[-5]R[-1]C[-5])"
.Range("G3:G" & LastRow).FormulaR1C1 = "=R[-1]C[-3]/(24*3600)"
.Range("H3:H" & LastRow).FormulaR1C1 = "=RC[-2]-RC[-1]"

.Range("H1").EntireColumn.NumberFormat = "h:mm:ss;@"
End With

End Sub

I used the last row used in column A as the last row to fill with the formulas.


hombreazul wrote:

Hi. Long time reader, first time poster.... :)

First, let me say that I am a complete newbie. Now that that's out of
the way. I recorded my first macro today. It is simple, but it gave
me great joy. I apply the macro to determine on/off phone time. What
I would like it to do is to move down the data columns and stop once no
data is found. For example, if the last call occurs on row 323, the
the macro should stop then, without inserting a bunch of zeros because
of the missing values. I hope I explained this properly and that
someone can help this really green dude. I am pasting the macro as is,
below. Thanks in advance.

K.

Range("F1").Select
ActiveCell.FormulaR1C1 = "total time"
Range("G1").Select
ActiveCell.FormulaR1C1 = "on time"
Range("H1").Select
ActiveCell.FormulaR1C1 = "off time"
Range("A2").Select
ActiveWindow.FreezePanes = True
Range("F3").Select
ActiveCell.FormulaR1C1 = "=RC[-5]-R[-1]C[-5]+(RC[-5]R[-1]C[-5])"
Range("G3").Select
ActiveCell.FormulaR1C1 = "=R[-1]C[-3]/(24*3600)"
Range("H3").Select
ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
Range("I3,F:F,G:G,H:H").Select
Range("H1").Activate
Selection.NumberFormat = "h:mm:ss;@"

--

Dave Peterson

Thanks a bunch. Worked like a charm when I did it properly. Weird,
though, the columns aren't formatting to time format. I see the code,
but it's just not doing it. It's like potty training a baby.