Need more efficient implementation

[AikiScott]

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.

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

[Norie]

Scott

A little help.

Cells(5, 27).Formula = "=SUMPRODUCT(--(P2:P" & LastRow & "), --(V2:V" & LastRow  & "), " & _ 
"--(ISNUMBER(SEARCH(""*Approved*"",K2:K" & LastRow & "))))"

By the way you shouldn't need to select cells before the autofill.

[Bob Phillips]

Using a syntax like

"=SUMPRODUCT(--(T2:T500), --(V2:V500), " & _
"--(ISNUMBER(SEARCH(""*Approved*"",K2:K500))))"

is very inefficient, using the double unary -- on just a range is unn ecessary. If they are numbers, coercing them to numbers is pointless. If they are text, it will force an error.

"=SUMPRODUCT(T2:T500,V2:V500, " & _
"--(ISNUMBER(SEARCH(""*Approved*"",K2:K500))))"

is sufficient.

[AikiScott]

Thanks to you both!

Xld, the double unary is necessary. The values in columns T and V are neither numbers nor text -- they're booleans.

So, I believe I either have to coerce them to numbers with the double unary minus operator like this:

"=SUMPRODUCT(--(T2:T500), --(V2:V500), " & _ 
"--(ISNUMBER(SEARCH(""*Approved*"",K2:K500))))"

Or I have to use the * operator inside of SUMPRODUCT (instead of commas) like this:

"=SUMPRODUCT(T2:T500*V2:V500* " & _ 
"--(ISNUMBER(SEARCH(""*Approved*"",K2:K500))))"

Norie, thanks for the trick on LastRow. I've been wracking my little brain trying to figure out a decent way to do that. I would have never thought about string concatenation.

What a great group this is! Thx!

Scott

[Bob Phillips]

Xld, the double unary is necessary. The values in columns T and V are neither numbers nor text -- they're booleans.

Of course, stupid of me, I forgot that possibility.

So, I believe I either have to coerce them to numbers with the double unary minus operator like this:

"=SUMPRODUCT(--(T2:T500), --(V2:V500), " & _ 
--(ISNUMBER(SEARCH(""*Approved*"",K2:K500))))

Or I have to use the * operator inside of SUMPRODUCT (instead of commas) like this:

"=SUMPRODUCT(T2:T500*V2:V500* " & _ 
"--(ISNUMBER(SEARCH(""*Approved*"",K2:K500))))"

I am not a fan of the * operator personally, but there are some cases when you have to use it. One thing you do NOT need is both operators (* --) as you have in the second example, you only need to do it once. You might want to read http://www.xldynamic.com/source/xld.SUMPRODUCT.html

[AikiScott]

Ah, yes. Using * does the coercion, so I wouldn't also need the --. Got it.

Thanks!