Week Help

[zoobr]

I have data which is broken up into days. I need this to be segregated into weeks. Is there an easy way to use VB or a macro to do this?

Here is a sample of what I am doing (I want to insert a column that says "week 1" after the first week, "week2" after the second, and so on). The weeks are designated sat-fri.

Tuesday Wednesday Thursday Saturday Monday Friday
100 100 100 100 100 100

So, I'd like it to say:
Tuesday Wednesday Thursday Week1 Saturday Monday Friday Week2
100 100 100 300 100 100 100 300

I have no idea how to do this without manually looking at all 730 columns. Any help would be greatly appreciated!!

 

[RalphA]

Since Excel only has 256 columns, and you speak of 730 columns, there seems to be some confusion here. Perhaps, after you clarify this, someone will be able to help you.

 

[patrickmuldoon99]

Unless he's using Excel 2007

 

[RalphA]

Hmm, that would put me, with my Excel 97, a mere ten years in back of him! But, that would explain the difference!

 

[zoobr]

Assume then, that I have 20 columns of data and I need to insert "week#" in the manner described above. I am just looking for some VB help.

 

[RalphA]

Looks pretty awesome to me, and, without a doubt, will reuire a VBA guru. I am not that, so, I bow out!

 

[patrickmuldoon99]

Hi there

I've pulled together a little sub routine that should do what you require. You need to insert the following code into a module in your VBA editor (Accessed by pressing Alt + F11, going to Insert > Module)

Option Base 1
Option Explicit
Dim week_array(7) As String
Const row_index As String = 1   'change this to reflect row number where days go across from

Sub splitweeks()
Dim col_index As Integer, wnum As Integer
col_index = 2                   'change this to reflect column number where *SECOND* day begins
week_array(1) = "Saturday": week_array(2) = "Sunday": week_array(3) = "Monday"
week_array(4) = "Tuesday": week_array(5) = "Wednesday": week_array(6) = "Thursday": week_array(7) = "Friday"
wnum = 1
'Cells(row_index, (col_index-1)) = "Week 1"
Do While Cells(row_index, col_index) <> ""
    If get_week_index(Cells(row_index, (col_index - 1))) > 1 And get_week_index(Cells(row_index, (col_index))) < get_week_index(Cells(row_index, (col_index - 1))) Then
            Cells(row_index, col_index).Select
            Selection.EntireColumn.Insert
            Cells(row_index, col_index) = "Week " & wnum
            wnum = wnum + 1
            col_index = col_index + 1
    End If
    col_index = col_index + 1
Loop
End Sub
Function get_week_index(day As String) As Integer
Dim x As Integer
x = 1
Do While x < (UBound(week_array) + 1)
    If week_array(x) = day Then
        get_week_index = x
        Exit Do
    End If
    x = x + 1
Loop
If x = UBound(week_array) + 1 Then
    get_week_index = -1
End If
End Function

You should be able to see from the comments where you can change either the row or column variables to reflect where the days of the week are in your spreadsheet. At the moment, it presumes they start from cell A1, but if you alter col_index and row_index to reflet where they start in your book it should work. Note, you should refer to the second day with col_index, as Week 1 is automatically inserted in A1 of the sheet as the first few days should belong to that week.


Hope this helps

Patrick


**Edit** Sorry, just read your question again, and it seems you want week 1 to appear after the first week has been displayed. As such I have edited the code so that it doesn't start off with week 1 in the first cell, and instead waits until the first week has finished before it inputs 'Week 1' - we can change it back if you change your mind.