Help parameterizing some VBA code

purceld2

Well-known Member
Joined
Aug 18, 2005
Messages
554
Below I have attached some VBA which Summarizes the data below. What i am trying to do is to have the ability to enter a date or a date range which then the code uses to report. So if I entered a date range of 26/07/210 - 02/08/2010 the code would report on these data ranges, is this something easy to do?

Thanks in advance for any help

Excel Workbook
ABCDEFGHIJKLM
1Venues CodeSlot 1Slot 2Slot 3Slot 4Slot 5Slot 6Slot 1Slot 2Slot 3Slot 4Slot 5Slot 6
2Date26/07/201002/08/2010
356878277708444277708444
456879277708222444277708222444
556880277708444277708444
656881277708444277708444
756882277708444277708444
856883277708444277708444
956884277708444277708444
1056885277708444277708444
1156886277708444277708444
1256887588708444588708444
1356888588708222444588708222444
145688958870855555887085555
155689058870855555887085555
165689158870855555887085555
175689258870855555887085555
185689358870855555887085555
195689458870855555887085555
205689511170822255551117082225555
215689611170855551117085555
225689711170855551117085555
235689811170855551117085555
245689911170855551117085555
255690011170855551117085555
265690111170855551117085555
Sheet1



[face=Courier New]Sub Count_Slot_Items()
Dim LastRow As Long

Application.ScreenUpdating = False

With Sheets("Sheet2")

.Columns("A:L").Clear

For c = 0 To 10 Step 2

Sheets("Sheet1").Columns("B:B").Offset(, c / 2).Copy Destination:=.Range("B1").Offset(, c)

.Columns("B:B").Offset(, c).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("A1").Offset(, c), Unique:=True
.Columns("B:B").Offset(, c).Clear
LastRow = .Range("A" & Rows.Count).Offset(, c).End(xlUp).Row

.Range("B1").Offset(, c).Value = "Count"
If LastRow > 1 Then
.Range("B2").Offset(, c).Formula = "=COUNTIF(Sheet1!" & Columns(2 + c / 2).Address & "," & Range("A2").Offset(, c).Address(0, 0) & ")"
If LastRow > 2 Then
.Range("B2").Offset(, c).AutoFill Destination:=.Range("B2:B" & LastRow).Offset(, c)
End If
.Range("B2:B" & LastRow).Offset(, c).Value = .Range("B2:B" & LastRow).Offset(, c).Value
End If

Next c

' Format cells
Range("A1").Copy
Range("A1", Range("A1").End(xlToRight)).PasteSpecial Paste:=xlPasteFormats
.Select

End With

Application.ScreenUpdating = True

End Sub
[/face]
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
purceld2,

Can we have a screenshot of what Sheet2 should look like after the macro?
 

purceld2

Well-known Member
Joined
Aug 18, 2005
Messages
554
Excel Workbook
ABCDEFGHIJKL
1Slot 1CountSlot 2CountSlot 3CountSlot 4CountSlot 5CountSlot 6Count
2277970824044411
358882223555513
41117
Sheet2
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
purceld2,

What i am trying to do is to have the ability to enter a date or a date range which then the code uses to report. So if I entered a date range of 26/07/210 - 02/08/2010 the code would report on these data ranges, is this something easy to do?

Where will you be entering the date, or date range?

What will Sheet2 look like if there were a start and end date?
 

purceld2

Well-known Member
Joined
Aug 18, 2005
Messages
554

ADVERTISEMENT

I had hoped that when you start running the code it would invite you to enter the dates
 

purceld2

Well-known Member
Joined
Aug 18, 2005
Messages
554
Hi Hiker95 thanks for all your help to date, but it seems I did not explain my problem clearly the code you have written works fine and i have been trying to amended so that instead of the code picking from column B:B it picks up a date or date range and performs the summary on that range of the spreadsheet. As I am new to VBA this is proving frustrating. I have attached the example layout which I am trying to get the code to run on. Any help would be greatly appreciated.

Excel Workbook
ABCDEFGHIJKLMNOPQRSTUVWXY
1Venue CodeSlot 1Slot 2Slot 3Slot 4Slot 5Slot 6Slot 1Slot 2Slot 3Slot 4Slot 5Slot 6Slot 1Slot 2Slot 3Slot 4Slot 5Slot 6Slot 1Slot 2Slot 3Slot 4Slot 5Slot 6
2Date26/07/201026/07/201026/07/201026/07/201026/07/201026/07/201002/08/201002/08/201002/08/201002/08/201002/08/201002/08/201009/08/201009/08/201009/08/201009/08/201009/08/201009/08/201016/08/201016/08/201016/08/201016/08/201016/08/201016/08/2010
310013514511514511511511
410023511511511511
510024514511514511511511
610030514511514511511511
710031514511514511511511
810033233233233511233233233511233233233511233233233511
910036514511514511511511
1010044514511514511511511
1110056514511514511511511
1210057514511514511511511
1310066514511514511511511
1410070233233233514233233233514233233233233233233
1510072514511514511511511
Sheet1
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649

ADVERTISEMENT

purceld2,

You supplied a screenshot for one date.

What i am trying to do is to have the ability to enter a date or a date range which then the code uses to report. So if I entered a date range of 26/07/210 - 02/08/2010 the code would report on these data ranges


What will Sheet2 look like for a start date of 26/07/2010, and the end date of 02/08/2010?
 

purceld2

Well-known Member
Joined
Aug 18, 2005
Messages
554
Hi Hiker95, I would expect the output to look something like below, Block 1 (26/07/2010)would repersent the data in column B:B to G:G and Block 2 (02/08/2010)

Excel Workbook
ABCDEFGHIJKL
1Week26/07/2010
21Count2Count3Count4Count5Count6Count
30005146660511743
423355233552335532820
54332
66662
7
8Week02/08/2010
91Count2Count3Count4Count5Count6Count
100005146660511743
1123355233552335532820
124332
136662
Sheet2
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
purceld2,

I am confused.

You have offered two sets of raw data, and their results, but, neither set is similar.


Click on the Post Reply button, and just enter the word BUMP, and click on the Submit Reply button, and someone else will assist you.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,643
Messages
5,660,039
Members
418,544
Latest member
Stefan Braem

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top