Using Case Select On Range With Multiple Conditions?

Hey all,

Need some assistance with a macro I am currently trying to formulate. I ususally use AutoFilter or IF Statements, so I am totally new to Case Select and need some basic help. To give some background info on what I'm working on, I have a file with various data. I need to create a Case Select to go through each row so to go from A2 (Since A1 is header) till the the last cell (End(xlDown) but Idk how to do that with Case Select) and then do something like the following:

•If Q2 = "Cash" AND J2 = "GC" then change cell A2 to "Capital Activity"
•If Q2 = "Bond Deals" AND B2 contains "CLO" (don't know how to do this in case select either) then change cell A2 to "CLO Subtype"
•If Q2 = "Bond Deals" AND R2 has 9 or 12 characters (don't know how to do this in case select either..use LEN?) then change cell A2 to "Bond"

If anyone can provide me with that I can figure out the rest of my macro on my own. I have provided my previous macro which got too messy/big/complicated which I tried to do the same thing using AutoFilter. After talking to one of my peers, he advised I look into using Case Select so that is why I am asking. Thanks in advance. I have also provided the file for viewing/using.

For The Q2 = Cash & J2 = "GC" > A2: Capital Activity

Please Login or Register  to view this content.

For The Q2 = Bond Deals & B2 containing "CLO" > A2: CLO Subtype

Please Login or Register  to view this content.

As you can see, my code is very messy and complicated. I would really appreciate help, and am more than ready to provide more information if needed. Here is the example file. Thanks In Advance!

https://www.dropbox.com/s/21xhnd8dz5...electEG.xlsx?m

Also, have posted problem on friend site OzGrid: http://www.ozgrid.com/forum/showthre...371#post672371

Your criteria aren't really suited for Select Case on it's own.

Select Case works best when you are checking a single value, eg a cell value, against a set of criteria.

You could use it for Q2 and nest some Ifs, but you could also just use a set of If statements.

Please Login or Register  to view this content.

With Select Case that would look something like this.

Please Login or Register  to view this content.

Originally Posted by Norie

Your criteria aren't really suited for Select Case on it's own.

Select Case works best when you are checking a single value, eg a cell value, against a set of criteria.

You could use it for Q2 and nest some Ifs, but you could also just use a set of If statements.

Thank you Norie! I've been thinking about using If Statements, but do you think they'd provide for what I'm trying to do? I am going to give If statements a try and post back here. Thanks for your quick reply though!

EDIT: It is currently giving me an error on "If cl.Offset(, 16).Value = "Bond Deals".....

and also it went through my first line and did not change A2 to "Capital Activity" even though both values were met? I am confused...

Well they should work for the criteria you laid out.

Oops, didn't spot the 'contains' bit.

Change ="CLO" to Like "*CLO*"

Please Login or Register  to view this content.

Originally Posted by Norie

Oops, didn't spot the 'contains' bit.

Change ="CLO" to Like "*CLO*"

Please Login or Register  to view this content.

Ah! That makes sense, I changed it and it works for when there is a CLO but if the Comment is "#N/A" it comes up to an error saying " Run-time error '13': Type mismatch". Any idea how to change this? Thanks again.

Also, you know how you showed me how to make it "Like "*CLO*"? How would I do it if I wanted to do it only if it begun with CLO?

#N/A is actually an error value and that's why you get the type mismatch.

Which column(s) do you have that in?

To see if CLO is at the beginning use "CLO*".

What you could do is get rid of the #NA by wrapping the VLOOKUPs, and any other formulas in ISERROR.

=ISERROR(VLOOKUP(...), "N/A")

That returns the string "N/A" not the error #NA, so you can still see there's an error but it won't cause a problem in the code.

Yes, you could do a copy, paste special values.

However you would still have the #NA values and you would still get the type mismatch error.

By the way, every row in column B has the #NA error.

If you do convert to values you'll still have the error values, and you'll still get the data type mismatch error.

B2 = "N/A" will generate a type mismatch error if B2 is #N/A because you are trying to compare a string and an error value.

You could add If Not IsError() throughout the code.

Please Login or Register  to view this content.

When you copy and paste the result of the formula, the error value #NA, will still be the error value #NA, it won't be converted to a string.

You've got 2 options I suppose.

1 Eliminate the errors at source.

2 Write code to deal with them.

I would normally go for 1, but if that's not possible then 2.

Something like this, change the sheet name as required.

Please Login or Register  to view this content.