Thread: Macros for multiple files

Hello,
I run a website that displays data for users. I am in the process of adding new years of data to update existing indicators, but the new files are broken down in a way that is inconsistent with the old ones. For example, I received a file with Alcohol Use (Current) by grade and gender 2008-2010 data (which will be an update to the 2006-2008 data already in there). It is structured like this:
Column 1: Grade Level (i.e., 7th, 9th, 11th grade, or Non-Traditional)
Column 2: Gender (i.e., Male or Female)
Column 3: Number of Days (See below)
Column 4: Data (value)
Column 5: Data Format (Percent)
Column 6: Location (California)
Column 7: Location ID
Column 8: Location_Code
Column 9: TimeFrame (2008-2010)

Each row is an individual data point.

While the grade level and gender columns mirror what we already have on the site, this file breaks down the days into the following categories:
0 days
1 day
2 days
3-9 days
10-19 days
20 days or more

However, the previous year’s data has the ‘1 day’ and ‘2 days’ data combined into a ‘1-2 days’ category:
0 days
1 -2 days
3-9 days
10-19 days
20 days or more

Therefore, what I need to do is create a macro what would insert a new row after every row with ‘2 days’ data (every 6 rows after the initial insert), copy all of the ‘2 days’ information into it, change the label to be ‘1-2 days,’ and in the data cell, insert a formula that sums the data for ‘1 day’ and ‘2 days’. Then, I need to get rid of the formula and erase all ‘1 day’ and ‘2 days’ rows.

In all, I have 68 files I need to which I need to apply this. However, only 18 have that exact file structure. Another 9 have about the same, but the new row needs to be inserted every 6, but the number and placement of the columns is different, and the formula needs to be adjusted to it will sum only the values of the ‘1 day’ and ‘2 days’. Therefore, I need to have a macro that can be employed across many data sets, and be edited to match the characteristics of the dataset. Do you know how to build something like that or have any suggestions for how I can?
Any guidance would be much appreciated!

Why dont you attach a sample workbook with some dummy data? It will be good if you attach the different types of datasets so we can get a better picture of the layout and what needs to be done.

Hi reganfoust
This code is in the attached and appears to do as you require

Option Explicit
Sub Combine_Days()
    Dim LR As Long
    Dim dayCol As String
    Dim dataCol As String
    Dim i As Long
    Dim Rng As Range

    Application.ScreenUpdating = False
    dayCol = ColumnLetter(WorksheetFunction.Match("Number of Days", Rows("1:1"), 0))
    dataCol = ColumnLetter(WorksheetFunction.Match("Data", Rows("1:1"), 0))

    LR = Range(dayCol & Rows.Count).End(xlUp).Row
    Set Rng = Range(dayCol & "2:" & dayCol & LR)
    With Rng
        For i = LR To 1 Step -1

            If Rng(i).Value = "2 days" And Rng(i).Offset(-1, 0) = "1 day" Then
                Rng(i).Offset(1, 0).EntireRow.Insert
                Rng(i).EntireRow.Copy
                Range("A" & Rng(i).Row).EntireRow.Copy
                Range("A" & Rng(i).Row).Offset(1, 0).PasteSpecial
                Range(dayCol & Rng(i).Row).Offset(1, 0).Value = "1-2 days"
                Range(dataCol & Rng(i).Row).Offset(1, 0).Value = Application.WorksheetFunction.Sum _
                        (Range(dataCol & Rng(i).Row).Offset(0, 0).Value, Range(dataCol & Rng(i).Row).Offset(-1, 0).Value)
                Rng(i).EntireRow.Delete
                Rng(i).Offset(-1, 0).EntireRow.Delete
            End If
        Next
    End With
    Application.ScreenUpdating = True

End Sub

Function ColumnLetter(ColumnNumber As Long) As String
' From http://www.craigmurphy.com/blog/?p=150
' Works in Excel 2007
    Dim ColNum As Integer
    Dim ColLetters As String
    ColNum = ColumnNumber
    ColLetters = ""
    Do
        ColLetters = Chr(((ColNum - 1) Mod 26) + 65) & ColLetters
        ColNum = Int((ColNum - ((ColNum - 1) Mod 26)) / 26)
    Loop While ColNum > 0
    ColumnLetter = ColLetters
End Function

The code searches for the two target columns (Number of Days and Data) such that, if all your worksheets use these two column headings it should work on all. If the headings are different, either change them or change the search string. Let me know of issues.

Hi reganfoust
What error message are you getting? I just this moment ran the procedure on the sample file and it performed as expected. Are you running on the sample file or a different file?

Hi reganfoust
What error message are you getting?

We'll try to debug it from long distance. If this doesn't work I'll need a copy of the offending file. Put a Break Point at this line of code

With Rng
        For i = LR To 1 Step -1

            If Rng(i).Value = "2 days" And Rng(i).Offset(-1, 0) = "1 day" Then
                Rng(i).Offset(1, 0).EntireRow.Insert    
                Rng(i).EntireRow.Copy
                Range("A" & Rng(i).Row).EntireRow.Copy
                Range("A" & Rng(i).Row).Offset(1, 0).PasteSpecial
                Range(dayCol & Rng(i).Row).Offset(1, 0).Value = "1-2 days"
                Range(dataCol & Rng(i).Row).Offset(1, 0).Value = Application.WorksheetFunction.Sum _
                        (Range(dataCol & Rng(i).Row).Offset(0, 0).Value, Range(dataCol & Rng(i).Row).Offset(-1, 0).Value)
                Rng(i).EntireRow.Delete
                Rng(i).Offset(-1, 0).EntireRow.Delete
            End If
        Next
    End With

Then step through the code (F8). What I'm looking for is to see if it blows at the first record it finds or somewhere higher in the file. If it gets past the first record, leave the Break Point in place and click F5...keep doing this until it breaks. When it breaks see what the value of (i) is. This'll be the row number that's causing the problem. Once we know that perhaps we'll know what's going on. Get back to me.

Shoulda ask and didn't...do you know how to do this?

Put a Break Point at this line of code

Not a big deal if you don't...I'll show you...we all need to learn something new each day.

Hi reganfoust

Well, I didn't use to either. This "stuff" doesn't come native to me...trial and error.

So here we go:
Open your file (the offending file) and enable macros (the macro IS in this book...true?)

Using the keyboard click alt-F11. This will open up a screen that looks like this VBA screen 1.jpg

The left hand side of the screen is the Project Screen...the right hand side of the screen is the VBA Code Screen. Between them is a Gray divider (I'm color blind...looks Gray to me).

Place your cursor in that divider on the line of code I suggested and left click the mouse while in the divider. You'll end up with this VBA screen 2.jpg That's a Break Point.

If you've gotten this far, go back to the workbook (simply click on the TAB) and run the procedure. The procedure will stop at the Break Point and the VBA code screen will open up. Make it smaller by clicking on the middle button in the uppermost right hand corner. You'll see that the Code has stopped at the line on which you set the Break Point.

Now, use F8 to step through the code (this executes the code one line at a time). If you get to the line that says Next then start clicking F5. This causes the procedure to run wild UNTIL it reaches the Break Point.

At some point the procedure will break. When it does click Debug on the screen presented then hover your mouse over (i) and see what it's value is.

Let's get this far...we'll see where we go from here. If you don't get past the FIRST record (the first time through the Break Point) then there's trouble in River City. Keep me posted.

Hi reganfoust

That's a rather large file. However, to troubleshoot, I probably need to see it. I'll PM my email address. Please include the code in the file. Please note, whatever solution we find (assuming we do), I'll post it on the Forum (NOT your file...the code). May not get back to you tonight.