AikiScott
08-26-2005, 06:15 AM
Hi all,
I'm importing a CSV table of travel arrangements for people (hundreds of rows), doing some data mining, and creating a summary report on another sheet. I've got most of it figured out, but there are a few really ugly things I'd like to get some suggestions on improving. Here's some relevant portions...
First, I create some intermediate functions (or temporary variables, if you prefer) to test who is present, arriving, and/or departing during a given period of time:
'Label the new columns
Cells(1, NewCol).Value = "Present Week1"
Cells(1, NewCol + 1).Value = "Arriving Week1"
Cells(1, NewCol + 2).Value = "Departing Week1"
Cells(1, NewCol + 3).Value = "Present Week2"
Cells(1, NewCol + 4).Value = "Arriving Week2"
Cells(1, NewCol + 5).Value = "Departing Week2"
' Put one copy of each new formula at the top of each new column
Cells(2, NewCol).Formula = "=AND((H2<=Instructions!Week1End), (I2>=Instructions!Week1Start))"
Cells(2, NewCol + 1).Formula = "=AND((H2>=Instructions!Week1Start), (H2<=Instructions!Week1End))"
Cells(2, NewCol + 2).Formula = "=AND((I2>=Instructions!Week1Start), (I2<=Instructions!Week1End))"
Cells(2, NewCol + 3).Formula = "=AND((H2<=Instructions!Week2End), (I2>=Instructions!Week2Start))"
Cells(2, NewCol + 4).Formula = "=AND((H2>=Instructions!Week2Start), (H2<=Instructions!Week2End))"
Cells(2, NewCol + 5).Formula = "=AND((I2>=Instructions!Week2Start), (I2<=Instructions!Week2End))"
' Autofill these initial formulas down the entire list of all people
ActiveCell.Cells(2, NewCol).Select ' first/leftmost formula
With Range(ActiveCell, ActiveCell.Offset(0, 5)) ' stretch range 5 cells to right
.AutoFill .Resize(LastRow - 1) ' autofill to bottom
End With
Later on, I need to check for specific text in the destination column. I create another temporary column of data, as so:
' Indentify all plans with a specific destination
Range("V1").Value = "Destination Found?"
Range("V2").Formula = "=ISNUMBER(SEARCH(""*text to find*"",C2:C500))"
' Fill formula down the column
Range("V2").Select ' grab formula
With Range("V2")
.AutoFill .Resize(LastRow - 1) ' autofill to bottom
End With
First question: Note that I arbitrarily made column V search down to row 500. How do I set that to a length determined at run-time? (I ahve already found the last row with data, and saved that row number as LastRow.)
Now here's where it really gets ugly. I need to sum all the people who's travel is approved, are going to the destination in question, and fall into each of the 6 buckets of present/arriving/departing. I'm using this:
Cells(5, 27).Formula = "=SUMPRODUCT(--(P2:P500), --(V2:V500), " & _
"--(ISNUMBER(SEARCH(""*Approved*"",K2:K500))))"
Cells(6, 27).Formula = "=SUMPRODUCT(--(Q2:Q500), --(V2:V500), " & _
"--(ISNUMBER(SEARCH(""*Approved*"",K2:K500))))"
Cells(7, 27).Formula = "=SUMPRODUCT(--(R2:R500), --(V2:V500), " & _
"--(ISNUMBER(SEARCH(""*Approved*"",K2:K500))))"
Cells(5, 28).Formula = "=SUMPRODUCT(--(S2:S500), --(V2:V500), " & _
"--(ISNUMBER(SEARCH(""*Approved*"",K2:K500))))"
Cells(6, 28).Formula = "=SUMPRODUCT(--(T2:T500), --(V2:V500), " & _
"--(ISNUMBER(SEARCH(""*Approved*"",K2:K500))))"
Cells(7, 28).Formula = "=SUMPRODUCT(--(U2:U500), --(V2:V500), " & _
"--(ISNUMBER(SEARCH(""*Approved*"",K2:K500))))"
Man, is that ugly.http://vbaexpress.com/forum/images/smilies/banghead.gif
Other than the hard-coded 500s (which I've already asked about), I know I could create yet another temporary column which holds the search for "*Approved*", and that would speed things up -- I'd be doing that only once rather than 6 times.
Second question: Is there anything else I could do differently to make this less of a kludge? This is some seriously ugly code, and I'm just not happy with it.
Sorry for the long first post, but this is the first thing I've done in VBA, and I have no references other than what I can glean from the web. It's been a totally seat-of-the-pants hack job, and I'd really like to clean it up some before I put it into general use.
Thanks in advance for your thoughts and suggestions!
Scott
I'm importing a CSV table of travel arrangements for people (hundreds of rows), doing some data mining, and creating a summary report on another sheet. I've got most of it figured out, but there are a few really ugly things I'd like to get some suggestions on improving. Here's some relevant portions...
First, I create some intermediate functions (or temporary variables, if you prefer) to test who is present, arriving, and/or departing during a given period of time:
'Label the new columns
Cells(1, NewCol).Value = "Present Week1"
Cells(1, NewCol + 1).Value = "Arriving Week1"
Cells(1, NewCol + 2).Value = "Departing Week1"
Cells(1, NewCol + 3).Value = "Present Week2"
Cells(1, NewCol + 4).Value = "Arriving Week2"
Cells(1, NewCol + 5).Value = "Departing Week2"
' Put one copy of each new formula at the top of each new column
Cells(2, NewCol).Formula = "=AND((H2<=Instructions!Week1End), (I2>=Instructions!Week1Start))"
Cells(2, NewCol + 1).Formula = "=AND((H2>=Instructions!Week1Start), (H2<=Instructions!Week1End))"
Cells(2, NewCol + 2).Formula = "=AND((I2>=Instructions!Week1Start), (I2<=Instructions!Week1End))"
Cells(2, NewCol + 3).Formula = "=AND((H2<=Instructions!Week2End), (I2>=Instructions!Week2Start))"
Cells(2, NewCol + 4).Formula = "=AND((H2>=Instructions!Week2Start), (H2<=Instructions!Week2End))"
Cells(2, NewCol + 5).Formula = "=AND((I2>=Instructions!Week2Start), (I2<=Instructions!Week2End))"
' Autofill these initial formulas down the entire list of all people
ActiveCell.Cells(2, NewCol).Select ' first/leftmost formula
With Range(ActiveCell, ActiveCell.Offset(0, 5)) ' stretch range 5 cells to right
.AutoFill .Resize(LastRow - 1) ' autofill to bottom
End With
Later on, I need to check for specific text in the destination column. I create another temporary column of data, as so:
' Indentify all plans with a specific destination
Range("V1").Value = "Destination Found?"
Range("V2").Formula = "=ISNUMBER(SEARCH(""*text to find*"",C2:C500))"
' Fill formula down the column
Range("V2").Select ' grab formula
With Range("V2")
.AutoFill .Resize(LastRow - 1) ' autofill to bottom
End With
First question: Note that I arbitrarily made column V search down to row 500. How do I set that to a length determined at run-time? (I ahve already found the last row with data, and saved that row number as LastRow.)
Now here's where it really gets ugly. I need to sum all the people who's travel is approved, are going to the destination in question, and fall into each of the 6 buckets of present/arriving/departing. I'm using this:
Cells(5, 27).Formula = "=SUMPRODUCT(--(P2:P500), --(V2:V500), " & _
"--(ISNUMBER(SEARCH(""*Approved*"",K2:K500))))"
Cells(6, 27).Formula = "=SUMPRODUCT(--(Q2:Q500), --(V2:V500), " & _
"--(ISNUMBER(SEARCH(""*Approved*"",K2:K500))))"
Cells(7, 27).Formula = "=SUMPRODUCT(--(R2:R500), --(V2:V500), " & _
"--(ISNUMBER(SEARCH(""*Approved*"",K2:K500))))"
Cells(5, 28).Formula = "=SUMPRODUCT(--(S2:S500), --(V2:V500), " & _
"--(ISNUMBER(SEARCH(""*Approved*"",K2:K500))))"
Cells(6, 28).Formula = "=SUMPRODUCT(--(T2:T500), --(V2:V500), " & _
"--(ISNUMBER(SEARCH(""*Approved*"",K2:K500))))"
Cells(7, 28).Formula = "=SUMPRODUCT(--(U2:U500), --(V2:V500), " & _
"--(ISNUMBER(SEARCH(""*Approved*"",K2:K500))))"
Man, is that ugly.http://vbaexpress.com/forum/images/smilies/banghead.gif
Other than the hard-coded 500s (which I've already asked about), I know I could create yet another temporary column which holds the search for "*Approved*", and that would speed things up -- I'd be doing that only once rather than 6 times.
Second question: Is there anything else I could do differently to make this less of a kludge? This is some seriously ugly code, and I'm just not happy with it.
Sorry for the long first post, but this is the first thing I've done in VBA, and I have no references other than what I can glean from the web. It's been a totally seat-of-the-pants hack job, and I'd really like to clean it up some before I put it into general use.
Thanks in advance for your thoughts and suggestions!
Scott