Help parameterizing some VBA code

purceld2

Well-known Member
Joined
Aug 18, 2005
Messages
549
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]
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

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

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,949
Messages
5,508,321
Members
408,678
Latest member
ripperbolt

This Week's Hot Topics

Top