Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> Microsoft ISV Community Center Forums >> Visual Basic for Applications (VBA)

Problem regarding VBA in excel - Combine 52 VBA codes for 52 weeks in a year to one VBA code

I am very new to VBA, hope that anyone can help me on this..
I have some problem with creating a code which can

Please refer to the code down there:

'the listbox will show week 1 to week 52 and let me choose one of the week.
Private Sub OKButton_Click()

If ListBox1.Text = "Week 26" Then Call week26
If ListBox1.Text = "Week 27" Then Call week27

' all together 52 weeks in a year

Unload Me
Sheet7.Activate

End Sub

Sub week26()

With Sheet7
.AutoFilterMode = False
.Range("A1:I1").AutoFilter
.Range("A1:I1").AutoFilter Field:=3, Criteria1:=">=26-Jun-06", _
Operator:=xlAnd, Criteria2:="<=2-Jul-06"
End With

End Sub

Sub week27()

With Sheet7
.AutoFilterMode = False
.Range("A1:I1").AutoFilter
.Range("A1:I1").AutoFilter Field:=3, Criteria1:=">=3-Jul-06", _
Operator:=xlAnd, Criteria2:="<=9-Jul-06"
End With

End Sub

Since there are 52 weeks in a year, i have to type the codes for 52

times for different weeks. can anyone of you help me on some code so

that i don't need to type all these codes 52 times, instead one time

with a loop or something else.

Thanks.

[1301 byte] By [pippen] at [2007-12-23]
«« Checking a userform that is not visible yet
»» adding multiple constraint relationship in Access
# 1
These forums are for VB.NET questions.

The VB within Excel is Visual Basic for Application. VBA is a very different product from VB.NET and there are some other locations where youy will probably get a quicker and better response to your VBA questions.

You may find more assistance in following which specifically deals with VBA development.

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=74&SiteID=1

Or if you are using VBA from within on of the office applications

Office Automation: office.developer.automation newsgroup

http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.office.developer.automation&lang=en&cr=US

Or the Office Newgroups

http://www.microsoft.com/office/community/en-us/default.mspx?d=1

Hope that helps

spotty at 2007-8-30 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
# 2
Moved thread from VB to VBA forum....
DMan1 at 2007-8-30 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
# 3
The basic idea is that you need a procedure (Sub) which will take arguments - the first date (criteria1) and the second date (criteria2).

Here is one way of doing it:

If you store the criteria dates on another sheet (sheet2 below), you can use the ListBox ListIndex property to get the 2 dates to pass into the procedure.

A

B

C

1

Week no

criteria1

criteria2

2

1

26-Jun-06

02-Jul-06

3

2

3-Jul-06

9-Jul-06



Private Sub cmdOK_Click()
MsgBox ListBox1.Text & vbCrLf & "ListIndex: " & ListBox1.ListIndex 'for info only
FilterWeek Sheets("sheet2").Cells(ListBox1.ListIndex + 2, 2), Sheets("sheet2").Cells(ListBox1.ListIndex + 2, 3)
Unload Me
Sheets(1).Activate
End Sub


Sub FilterWeek(strCriteria1 As String, strCriteria2 As String)
With Sheet1
.AutoFilterMode = False
.Range("A1:I1").AutoFilter
.Range("A1:I1").AutoFilter Field:=3, criteria1:=">=" & strCriteria1, _
Operator:=xlAnd, criteria2:="<=" & strCriteria2
End With
End Sub

hth

Navajo at 2007-8-30 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
# 4
Hi, thank you very much for reminding me and giving me so many related links. Thank you very much! :)
pippen at 2007-8-30 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
# 5
DMan1 wrote:
Moved thread from VB to VBA forum....

Hi thanks for moving the thread to the correct forum for me. Thanks!. :)

pippen at 2007-8-30 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
# 6
Hi.. thanks you for replying me.. however.. due to my limite knowledge. i still couldn't get what i want..although i tried very hard to manipulate the code. so.. i still need your help. i would be glad that you could help me once more. maybe i didn't describe my situation very clearly so that it ended up like this. let me simpify it a bit.
now i have an array of data in sheet1 as below:

Start TimeAmount1Amount2
26-Jun-06776.95720.82
28-Jun-06747.84729.43
30-Jun-06755.49745.8
3-Jul-06800.12775.75
4-Jul-06794.96775.03
7-Jul-06787.62758.65
9-Jul-06800.77783.94
10-Jul-06786.89750.06
12-Jul-06769.86762.24
14-Jul-06811.5761.3
14-Jul-06783.47727.94
18-Jul-06758.03727.25
20-Jul-06825.73753.2
20-Jul-06825.73753.2
20-Jul-06825.73753.2
20-Jul-06825.73753.2
20-Jul-06825.73753.2
20-Jul-06825.73753.2

and as you told me to to create another sheet for the weeks and dates which is sheet2 as below: (i name the column of criteria1 as criteria1 and this also applies to column2):

Weekcriteria1criteria2
Week 12-Jan-068-Jan-06
Week 29-Jan-0615-Jan-06
Week 316-Jan-0622-Jan-06
Week 423-Jan-0629-Jan-06
Week 530-Jan-065-Feb-06
Week 66-Feb-0612-Feb-06
Week 713-Feb-0619-Feb-06
Week 820-Feb-0626-Feb-06
Week 927-Feb-065-Mar-06
Week 106-Mar-0612-Mar-06
Week 1113-Mar-0619-Mar-06
Week 1220-Mar-0626-Mar-06
Week 1327-Mar-062-Apr-06
Week 143-Apr-069-Apr-06
Week 1510-Apr-0616-Apr-06
Week 1617-Apr-0623-Apr-06
Week 1724-Apr-0630-Apr-06
Week 181-May-067-May-06
Week 198-May-0614-May-06
Week 2015-May-0621-May-06
Week 2122-May-0628-May-06
Week 2229-May-064-Jun-06
Week 235-Jun-0611-Jun-06
Week 2412-Jun-0618-Jun-06
Week 2519-Jun-0625-Jun-06
Week 2626-Jun-062-Jul-06
Week 273-Jul-069-Jul-06
Week 2810-Jul-0616-Jul-06
Week 2917-Jul-0623-Jul-06
Week 3024-Jul-0630-Jul-06
Week 3131-Jul-066-Aug-06
Week 327-Aug-0613-Aug-06
Week 3314-Aug-0620-Aug-06
Week 3421-Aug-0627-Aug-06
Week 3528-Aug-063-Sep-06
Week 364-Sep-0610-Sep-06
Week 3711-Sep-0617-Sep-06
Week 3818-Sep-0624-Sep-06
Week 3925-Sep-061-Oct-06
Week 402-Oct-068-Oct-06
Week 419-Oct-0615-Oct-06
Week 4216-Oct-0622-Oct-06
Week 4323-Oct-0629-Oct-06
Week 4430-Oct-065-Nov-06
Week 456-Nov-0612-Nov-06
Week 4613-Nov-0619-Nov-06
Week 4720-Nov-0626-Nov-06
Week 4827-Nov-063-Dec-06
Week 494-Dec-0610-Dec-06
Week 5011-Dec-0617-Dec-06
Week 5118-Dec-0624-Dec-06
Week 5225-Dec-0631-Dec-06

What i want is i created a listbox (in a userform) which shows me all the weeks in a year, from week1 till week 52. and after i clicked one of the week in the listbox and ok. After that the macro will autofilter the data in sheet 1 into only showing data of that specific week, for example week26, according to the start and end dates of that week which specified in sheet2. The macro i have tried (follow the way you taught me) is as below:

Private Sub OptionButton1_Click()
ListBox1.RowSource = "Sheet2!Week1"
End Sub

Private Sub CancelButton_Click()
Unload Me
End Sub

Private Sub OKButton_Click()
MsgBox ListBox1.Text & vbCrLf & "ListIndex: " & ListBox1.ListIndex 'for info only
FilterWeek Sheets("Sheet2").Cells(ListBox1.ListIndex + 2, 2), Sheets("Sheet2").Cells(ListBox1.ListIndex + 2, 3)
Unload Me
Sheets("Sheet1").Activate
Unload Me
Sheet1.Activate
End Sub

Sub FilterWeek(strCriteria1 As String, strCriteria2 As String)
With Sheet1
.AutoFilterMode = False
.Range("A1:I1").AutoFilter
.Range("A1:I1").AutoFilter Field:=1, Criteria1:=">=" & strCriteria1, _
Operator:=xlAnd, Criteria2:="<=" & strCriteria2
End With
End Sub

It didn't work in the end. Could you please tell me what went wrong? Thanks. (erm.. do you understand what i meant? :) )
Thanks again.

pippen at 2007-8-30 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
# 7
Navajo wrote:
The basic idea is that you need a procedure (Sub) which will take arguments - the first date (criteria1) and the second date (criteria2).

Here is one way of doing it:

If you store the criteria dates on another sheet (sheet2 below), you can use the ListBox ListIndex property to get the 2 dates to pass into the procedure.

A

B

C

1

Week no

criteria1

criteria2

2

1

26-Jun-06

02-Jul-06

3

2

3-Jul-06

9-Jul-06



Private Sub cmdOK_Click()
MsgBox ListBox1.Text & vbCrLf & "ListIndex: " & ListBox1.ListIndex 'for info only
FilterWeek Sheets("sheet2").Cells(ListBox1.ListIndex + 2, 2), Sheets("sheet2").Cells(ListBox1.ListIndex + 2, 3)
Unload Me
Sheets(1).Activate
End Sub


Sub FilterWeek(strCriteria1 As String, strCriteria2 As String)
With Sheet1
.AutoFilterMode = False
.Range("A1:I1").AutoFilter
.Range("A1:I1").AutoFilter Field:=3, criteria1:=">=" & strCriteria1, _
Operator:=xlAnd, criteria2:="<=" & strCriteria2
End With
End Sub

hth

Hi Navajo,

I don't quite understand this line:
FilterWeek Sheets("sheet2").Cells(ListBox1.ListIndex + 2, 2), Sheets("sheet2").Cells(ListBox1.ListIndex + 2, 3)
Can you explain to me? Thanks.
Thanks.
:)

pippen at 2007-8-30 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
# 8
ListIndex is the index number of the currently selected item in the listbox. If the listbox is filled with week1 through week52 and you select week1 ListIndex will be 0, and 1 for week2 etc.

.Cells(row, column) is used as a lookup for both criteria.

In .Cells(ListBox1.ListIndex + 2, 2), ListBox1.ListIndex + 2 is the row argument (add 2 because weeks start at row 2) and 2 is the column argument.

So .Cells(ListBox1.ListIndex + 2, 2) reads criteria1 and .Cells(ListBox1.ListIndex + 2, 3) reads criteria2.


I assumed you'd already filled the listbox with week numbers, but if not you could use this:

Private Sub UserForm_Initialize()
For Each wkcell In Sheets(2).Range("a2:a53")
ListBox1.AddItem wkcell.Text
Next
End Sub

I'm not very familiar with AutoFilter and criterias, but I found that it didn't like (in this case) criteria dates in the format "dd-mmm-yy", but appears to like them in the format "dd-mmm-yyyy". I used the Format function to get them into the 'correct' format then it worked fine:

FilterWeek Format(Sheets("sheet2").Cells(ListBox1.ListIndex + 2, 2), "dd-mmm-yyyy"), _
Format(Sheets("sheet2").Cells(ListBox1.ListIndex + 2, 3), "dd-mmm-yyyy")

You can always use variables to break this statement up. hth

Navajo at 2007-8-30 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
# 9
Great!!! It works very very well!! You are genius!! Thanks a lot of giving me so much information and also thank you for your time. THANKS a TRILLION!!!
pippen at 2007-8-30 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
Microsoft ISV Community Center Forums
Visual Basic for Applications (VBA)

Microsoft ISV Community Center Forums Hot Topic

Microsoft ISV Community Center Forums New Topic

Microsoft ISV Community Center Forums

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified