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 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

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" <nbaj2k.2b71mr_1153314607.7104@excelforum-nospam.com> skrev i
melding news:nbaj2k.2b71mr_1153314607.7104@excelforum-nospam.com...
>
> 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
>

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" <nbaj2k.2b71mr_1153314607.7104@excelforum-nospam.com> skrev i
melding news:nbaj2k.2b71mr_1153314607.7104@excelforum-nospam.com...
>
> 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
>

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" <nbaj2k.2b71mr_1153314607.7104@excelforum-nospam.com> skrev i
melding news:nbaj2k.2b71mr_1153314607.7104@excelforum-nospam.com...
>
> 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
>

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

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" <nbaj2k.2b791h_1153324378.667@excelforum-nospam.com> skrev i
melding news:nbaj2k.2b791h_1153324378.667@excelforum-nospam.com...
>
> 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
>

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" <nbaj2k.2b8tis_1153397407.2361@excelforum-nospam.com> skrev i
melding news:nbaj2k.2b8tis_1153397407.2361@excelforum-nospam.com...
>
> 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:=xlDatabase, 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 Where:=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
>

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" <nbaj2k.2b8xot_1153402808.042@excelforum-nospam.com> skrev i
melding news:nbaj2k.2b8xot_1153402808.042@excelforum-nospam.com...
>
> 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:=xlDatabase, 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
>