Macro to Count how many recordsbetween each hour interval?
 

I'm looking to add to my macro, some code so that it will search down
specific column and count how many records there are between each hou
interval.

For example, how many calls between 10AM and 11AM, 12PM-1PM. I jus
wanted to know if there was a way to put code in so that it woul
search down the column and come back with how many automatically.

Here is the thing. The data I'm working with is exported from
program we have. If I go to format cells I can see that it is unde
the "custom" format, using "m/d/yyyy h:mm" If I switch it to general
it comes back as a weird number such as 38916.23. I can change it to
"Date" frmat, but that does not change the contents of the actual cel
which is something like "7/18/2006 5:24:36 AM" I can visibly see i
as I want by formatting it different ways.

Any help would be great!

Thanks,

~

--
nbaj2
-----------------------------------------------------------------------
nbaj2k's Profile: http://www.excelforum.com/member.php...fo&userid=3648
View this thread: http://www.excelforum.com/showthread.php?threadid=56282

Macro to Count how many recordsbetween each hour interval?
 

Hi J

The "weird number" is date-time, see
http://www.cpearson.com/excel/datetime.htm
for some theory.

As for the interval count, and all other kinds of summaries, a Pivot table
will do it for you. See
http://www.mrexcel.com/tip047.shtml

(If you haven't used Pivot tables before, it may look too foreign and scary
and you'll post back for another solution. But take my word for it: Spend
half an hour learning Pivot tables and you can't imagine how you ever
managed without them.)

HTH. best wishes Harald

"nbaj2k" skrev i
melding ...

I'm looking to add to my macro, some code so that it will search down a
specific column and count how many records there are between each hour
interval.

For example, how many calls between 10AM and 11AM, 12PM-1PM. I just
wanted to know if there was a way to put code in so that it would
search down the column and come back with how many automatically.

Here is the thing. The data I'm working with is exported from a
program we have. If I go to format cells I can see that it is under
the "custom" format, using "m/d/yyyy h:mm" If I switch it to general,
it comes back as a weird number such as 38916.23. I can change it to a
"Date" frmat, but that does not change the contents of the actual cell
which is something like "7/18/2006 5:24:36 AM" I can visibly see it
as I want by formatting it different ways.

Any help would be great!

Thanks,

~J


--
nbaj2k
------------------------------------------------------------------------
nbaj2k's Profile:
http://www.excelforum.com/member.php...o&userid=36480
View this thread: http://www.excelforum.com/showthread...hreadid=562821

Macro to Count how many recordsbetween each hour interval?
 

It worked perfectly, I didn't know you could do that with Pivot tables.

I have one more question. what I do is have a Macro and make a Pivot
table for the dates on a new worksheet. After I make it I want to take
out all of the headings and everything so it just looks like regular
data. What I have been doing is in the macro selecting the region
after I have it set the way I want then copying and pasting the values
onto another worksheet and deleting the sheet with the Pivot table on
it. It seems like there has to be an easier way. Plus when I delete
the page with the Pivot table on it, even when I automate it, it stops
and asks me to delete the page since there are values in the Pivot
table, I have to click yes everytime. I'm trying to make this process
for someone and I really don't want to have that in there.

If I can change it so I don't have to delete the table that would be
great, or if I can make it so that it does not ask me to confirm
anytime that would be be fine too.

Any ideas?

Thanks a whole lot!

~J


--
nbaj2k
------------------------------------------------------------------------
nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480
View this thread: http://www.excelforum.com/showthread...hreadid=562821

Macro to Count how many recordsbetween each hour interval?
 

Good work then !
I don't know your setup good enough to suggest how to deal with creation and
deletion of objects. But you can avoid all kinds of "do you really want to
?"-messages by doing

Application.DisplayAlerts = False
' code goes here
Application.DisplayAlerts = True

it means "I know what I do so don't ask".

HTH. Best wishes Harald

"nbaj2k" skrev i
melding ...

It worked perfectly, I didn't know you could do that with Pivot tables.

I have one more question. what I do is have a Macro and make a Pivot
table for the dates on a new worksheet. After I make it I want to take
out all of the headings and everything so it just looks like regular
data. What I have been doing is in the macro selecting the region
after I have it set the way I want then copying and pasting the values
onto another worksheet and deleting the sheet with the Pivot table on
it. It seems like there has to be an easier way. Plus when I delete
the page with the Pivot table on it, even when I automate it, it stops
and asks me to delete the page since there are values in the Pivot
table, I have to click yes everytime. I'm trying to make this process
for someone and I really don't want to have that in there.

If I can change it so I don't have to delete the table that would be
great, or if I can make it so that it does not ask me to confirm
anytime that would be be fine too.

Any ideas?

Thanks a whole lot!

~J


--
nbaj2k
------------------------------------------------------------------------
nbaj2k's Profile:
http://www.excelforum.com/member.php...o&userid=36480
View this thread: http://www.excelforum.com/showthread...hreadid=562821

Macro to Count how many recordsbetween each hour interval?
 

That worked! Thanks. Unfortunately I ran into one last problem with this
that I can't seem to solve. I thought everything was running perfectly
but its not. Basically what I'm trying to do is take the date from
column D3 on the Visible sheet (not knowing how many different records
there are),
make a PivotTable from it grouping the hours together,
removing the Grand Total line from the Pivot Table
copy the pivot table and paste the values of it on a new sheet
Then take the table and make a Graph out of it

Somehow since this is a macro, I have to pick a variable range.

This is the code I am using as of now, but I am getting an error (I'm
not sure how to set the variable range)

The ByHour sheet is the one that the pivot table is being copied to and
the graph is on.

I'm doing this at work, thought I had all the kinks out of it and my
boss wants me to show it soon, if you can help out please do!


Thanks,

~J

Sub CallHours()

Range("D3").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
_
"Visible!R2C1:R22C11").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3,
1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").AddFields
RowFields:="created"

ActiveSheet.PivotTables("PivotTable1").PivotFields ("created").Orientation
= _
xlDataField
With ActiveSheet.PivotTables("PivotTable1")
ColumnGrand = False
RowGrand = False
End With
Range("A5").Select
Selection.Group Start:=True, End:=True, Periods:=Array(False,
False, True, _
False, False, False, False)
Selection.CurrentRegion.Select
Selection.Copy
Sheets("Sheet2").Select
Sheets.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Ticket Hour Interval"
Range("A3").Select
ActiveCell.FormulaR1C1 = "Hour"
Columns("B:B").Select
With Selection
HorizontalAlignment = xlCenter
VerticalAlignment = xlBottom
WrapText = False
Orientation = 0
AddIndent = False
IndentLevel = 0
ShrinkToFit = False
ReadingOrder = xlContext
MergeCells = False
End With
Rows("3:3").Select
Selection.Font.Bold = True
Range("D1").Select
ActiveCell.FormulaR1C1 = "From:"
Range("D2").Select
ActiveCell.FormulaR1C1 = "To:"
Range("D1:D2").Select
Selection.Font.Bold = True
Range("E1").Select
ActiveCell.FormulaR1C1 = "=TSC!R[1]C[-4]"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=TSC!RC[-3]"
Range("A1").Select
Selection.Font.Bold = True
Range("A3").Select
Selection.CurrentRegion.Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)=0"
Selection.FormatConditions(1).Interior.ColorIndex = 33
Range("A3").Select
Selection.CurrentRegion.Select
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("By
Hour").Range("A3:B14"), PlotBy _
:=xlColumns
ActiveChart.Location Whe=xlLocationAsObject, Name:="By Hour"
With ActiveChart
HasTitle = True
ChartTitle.Characters.Text = "Tickets by Hour Interval"
Axes(xlCategory, xlPrimary).HasTitle = True
Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Hour
Interval"
Axes(xlValue, xlPrimary).HasTitle = True
Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "# of
Tickets"
End With
ActiveChart.HasLegend = False
ActiveWindow.Visible = False
Windows("TSCMainLookup.xls").Activate
Range("E1:E2").Select
Selection.Font.Bold = True
Sheets("Sheet3").Select
Range("A1").Select
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "By Hour"
Range("A1").Select
Sheets("Sheet2").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Range("A1").Select
Sheets("By Hour").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 1").IncrementLeft -39.75
ActiveSheet.Shapes("Chart 1").IncrementTop -60#
ActiveWindow.Visible = False
Windows("TSCMainLookup.xls").Activate
Range("F20").Select
ActiveCell.FormulaR1C1 = "Total Tickets = "
Range("F20").Select
Columns("F:F").EntireColumn.AutoFit
Range("G20").Select
ActiveCell.FormulaR1C1 = "=SUM(C[-5])"
Range("F20:G20").Select
Selection.Font.Bold = True
Range("A1").Select
End Sub


--
nbaj2k
------------------------------------------------------------------------
nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480
View this thread: http://www.excelforum.com/showthread...hreadid=562821

Macro to Count how many recordsbetween each hour interval?
 

Question is variable data range ? CurrentRegion is the safest way, Excel
does a qualified guess then:

Sub DataArea()
Dim MyData As Range
Set MyData = Sheets("Visible").Range("B3").CurrentRegion
MsgBox MyData.Address
End Sub

but you can merge the last row into your code instead if you prefer mode
hardcoded material:

Sub LastRow()
Dim L As Long
L = Sheets("Visible").Cells(Rows.Count, 2).End(xlUp).Row
MsgBox "Visible!R2C1:R" & L & "C11"
End Sub

HTH. Best wishes Harald

"nbaj2k" skrev i
melding ...

That worked! Thanks. Unfortunately I ran into one last problem with this
that I can't seem to solve. I thought everything was running perfectly
but its not. Basically what I'm trying to do is take the date from
column D3 on the Visible sheet (not knowing how many different records
there are),
make a PivotTable from it grouping the hours together,
removing the Grand Total line from the Pivot Table
copy the pivot table and paste the values of it on a new sheet
Then take the table and make a Graph out of it

Somehow since this is a macro, I have to pick a variable range.

This is the code I am using as of now, but I am getting an error (I'm
not sure how to set the variable range)

The ByHour sheet is the one that the pivot table is being copied to and
the graph is on.

I'm doing this at work, thought I had all the kinks out of it and my
boss wants me to show it soon, if you can help out please do!


Thanks,

~J

Sub CallHours()

Range("D3").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
_
"Visible!R2C1:R22C11").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3,
1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").AddFields
RowFields:="created"

ActiveSheet.PivotTables("PivotTable1").PivotFields ("created").Orientation
= _
xlDataField
With ActiveSheet.PivotTables("PivotTable1")
ColumnGrand = False
RowGrand = False
End With
Range("A5").Select
Selection.Group Start:=True, End:=True, Periods:=Array(False,
False, True, _
False, False, False, False)
Selection.CurrentRegion.Select
Selection.Copy
Sheets("Sheet2").Select
Sheets.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Ticket Hour Interval"
Range("A3").Select
ActiveCell.FormulaR1C1 = "Hour"
Columns("B:B").Select
With Selection
HorizontalAlignment = xlCenter
VerticalAlignment = xlBottom
WrapText = False
Orientation = 0
AddIndent = False
IndentLevel = 0
ShrinkToFit = False
ReadingOrder = xlContext
MergeCells = False
End With
Rows("3:3").Select
Selection.Font.Bold = True
Range("D1").Select
ActiveCell.FormulaR1C1 = "From:"
Range("D2").Select
ActiveCell.FormulaR1C1 = "To:"
Range("D1:D2").Select
Selection.Font.Bold = True
Range("E1").Select
ActiveCell.FormulaR1C1 = "=TSC!R[1]C[-4]"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=TSC!RC[-3]"
Range("A1").Select
Selection.Font.Bold = True
Range("A3").Select
Selection.CurrentRegion.Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)=0"
Selection.FormatConditions(1).Interior.ColorIndex = 33
Range("A3").Select
Selection.CurrentRegion.Select
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("By
Hour").Range("A3:B14"), PlotBy _
:=xlColumns
ActiveChart.Location Whe=xlLocationAsObject, Name:="By Hour"
With ActiveChart
HasTitle = True
ChartTitle.Characters.Text = "Tickets by Hour Interval"
Axes(xlCategory, xlPrimary).HasTitle = True
Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Hour
Interval"
Axes(xlValue, xlPrimary).HasTitle = True
Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "# of
Tickets"
End With
ActiveChart.HasLegend = False
ActiveWindow.Visible = False
Windows("TSCMainLookup.xls").Activate
Range("E1:E2").Select
Selection.Font.Bold = True
Sheets("Sheet3").Select
Range("A1").Select
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "By Hour"
Range("A1").Select
Sheets("Sheet2").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Range("A1").Select
Sheets("By Hour").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 1").IncrementLeft -39.75
ActiveSheet.Shapes("Chart 1").IncrementTop -60#
ActiveWindow.Visible = False
Windows("TSCMainLookup.xls").Activate
Range("F20").Select
ActiveCell.FormulaR1C1 = "Total Tickets = "
Range("F20").Select
Columns("F:F").EntireColumn.AutoFit
Range("G20").Select
ActiveCell.FormulaR1C1 = "=SUM(C[-5])"
Range("F20:G20").Select
Selection.Font.Bold = True
Range("A1").Select
End Sub


--
nbaj2k
------------------------------------------------------------------------
nbaj2k's Profile:
http://www.excelforum.com/member.php...o&userid=36480
View this thread: http://www.excelforum.com/showthread...hreadid=562821

Macro to Count how many recordsbetween each hour interval?
 

I'm just learning this so be nice lol

This is not vaild code?

I tried to implement MyData, but wanted the Pivot Table to include th
current region of cell D3. I wanted to do this because there may be
lot of records, and might not be.

It gives me an error saying "Reference not valid" so I assume I'
writing this wrong.

Thanks,

~J

Dim MyData As Range
Set MyData = Sheets("Visible").Range("D3").CurrentRegion

Range("D3").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:
_
"!MyData").CreatePivotTable TableDestination:="", TableName:
_
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3
1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").AddField
RowFields:="created"

ActiveSheet.PivotTables("PivotTable1").PivotFields ("created").Orientatio
= _
xlDataField
Range("A4").Select
Selection.Group Start:=True, End:=True, Periods:=Array(False
False, True, _
False, False, False, False

--
nbaj2
-----------------------------------------------------------------------
nbaj2k's Profile: http://www.excelforum.com/member.php...fo&userid=3648
View this thread: http://www.excelforum.com/showthread.php?threadid=56282

Macro to Count how many recordsbetween each hour interval?
 

When you put quotes around things, it's just text, not a variable. Instead
of

SourceData:="!MyData"
try
SourceData:=MyData

I'm walking you through this project and you ask me to be nice ? Next week
I'll be nice.

Best wishes Harald

"nbaj2k" skrev i
melding ...

I'm just learning this so be nice lol

This is not vaild code?

I tried to implement MyData, but wanted the Pivot Table to include the
current region of cell D3. I wanted to do this because there may be a
lot of records, and might not be.

It gives me an error saying "Reference not valid" so I assume I'm
writing this wrong.

Thanks,

~J

Dim MyData As Range
Set MyData = Sheets("Visible").Range("D3").CurrentRegion

Range("D3").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
_
"!MyData").CreatePivotTable TableDestination:="", TableName:=
_
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3,
1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").AddFields
RowFields:="created"

ActiveSheet.PivotTables("PivotTable1").PivotFields ("created").Orientation
= _
xlDataField
Range("A4").Select
Selection.Group Start:=True, End:=True, Periods:=Array(False,
False, True, _
False, False, False, False)


--
nbaj2k
------------------------------------------------------------------------
nbaj2k's Profile:
http://www.excelforum.com/member.php...o&userid=36480
View this thread: http://www.excelforum.com/showthread...hreadid=562821