Alternative to multipe ELSE IF statements?

**Winterreise** · 05-16-2012, 01:38 PM

Currently working on a script for indexing a hard drive, and entering data into a "Document Type" column (Column I in this case) based on keywords in the documents. What I currently have works perfectly, but takes forever as it sometimes can be upwards of 10,000 files. Is there an easier way that provides the same results?

Please Login or Register  to view this content.

I've changed the names for example purposes, haha.

**Bob Phillips** · 05-16-2012, 01:46 PM

Is this any quicker

Please Login or Register  to view this content.

**Winterreise** · 05-16-2012, 02:01 PM

I get a "Run-time error 13 Type Mismatch" on the first Case objFile.Name line of that code? I appreciate the help, by the way!

EDIT: It doesn't appear as though the Select Case statement lets you have multiple "likes" for each case. Dumping it down to a Case each for "*cheese*", "*gouda*", and "*havarti*" works, but having "*Cheese*", "*Gouda*", "*Havarti*" in one Case Like statement doesn't. If that makes sense...I'm fairly new to this.

Unfortunately, I don't think having a separate Case statement for each keyword will be any faster than having a separate Else If statement, but I'm willing to give it a try.

**Bob Phillips** · 05-17-2012, 04:02 AM

I think you are correct on both counts, you cannot use Like in multi-case statements, and it wouldn't be any faster anyway.

Assuming your source data is on a worksheet, a quicker way might be to add a formula to that sheet to calculate the answer. The formula can probably be applied in one step, and would be much faster than a VBA loop.

**Winterreise** · 05-17-2012, 08:15 AM

That sounds intriguing...would you happen to know of an example? As I said I'm fairly new to this, and tend to learn best from reading examples. Either way, I'm off to try Googling up some stuff. Thank you!

**Bob Phillips** · 05-17-2012, 08:37 AM

Can you post a workbook? I can then knock up a solution based upon your code to date.

**Winterreise** · 05-17-2012, 09:39 AM

That would be fantastic, and well above anything you have to do! I've attached it...but be warned, as I said I'm new, it's pretty bad to look at, I think. I've removed about half of the Else If statements since just a few gives you the idea!

Thank you again, I appreciate it!