PDA

View Full Version : Solved: Assist in Converting IF-Then to Select Case



YellowLabPro
09-24-2007, 09:32 AM
I have the beginnings here of a Select Case.
I don't know how to convert this for the existing IF Then Conditions.
If the rest of the code is required plz. let me know and I will post.


With wsDp
lrwSource = LR(wsDp, 1)
For Each c In rng("AbbColorTerms")
Select Case True
Case NoSlash
If Len(c.Value) - Len(Replace(c.Value, "/", "")) = 0 Then
.Columns(3).Replace what:=Chr(32) & c & Chr(32), replacement:=Chr(32) & c.Offset(, 1) & Chr(32), lookat:=xlPart, _
searchorder:=xlByRows, MatchCase:=False, searchformat:=False, _
ReplaceFormat:=False
End If
Case WrappedSlash
If Len(c.Value) - Len(Replace(c.Value, "/", "")) = 2 Then
.Columns(3).Replace what:=c, replacement:=c.Offset(, 1), lookat:=xlPart, _
searchorder:=xlByRows, MatchCase:=False, searchformat:=False, _
ReplaceFormat:=False
End If
Case TrailingSlash
If Len(c.Value) - Len(Replace(c.Value, "/", "")) = 1 Then
.Columns(3).Replace what:=Chr(32) & c, replacement:=Chr(32) & c.Offset(, 1), lookat:=xlPart, _
searchorder:=xlByRows, MatchCase:=False, searchformat:=False, _
ReplaceFormat:=False
End If
Case LeadingSlash
If Len(c.Value) - Len(Replace(c.Value, "/", "")) = 1 Then
.Columns(3).Replace what:=c & Chr(32), replacement:=c.Offset(, 1) & Chr(32), lookat:=xlPart, _
searchorder:=xlByRows, MatchCase:=False, searchformat:=False, _
ReplaceFormat:=False
End If
Next c
End Select
End With

Norie
09-24-2007, 09:45 AM
Doug

I really think it would help if we actually knew the If conditions you want help converting.

YellowLabPro
09-24-2007, 09:51 AM
Norie,
Are not the IF conditions you want in the current code in the post?

RonMcK
09-24-2007, 10:04 AM
Doug,

I believe that your formula in the IF statement needs to be pulled out, assigned to a variable, and placed outside (before) the SELECT CASE / END CASE section.

FOR NEXT needs to come after END CASE

You need a way to distinquish LeadingSlash from Trailing Slash; right now, they both evaluate to one, so, whichever CASE is first in the SELECT CASE will be selected everytime. This is a little like the old vaudeville routine where the MC announces 'T'onight we have 4 lovely ladies and 3 beautiful costumes.' :)

So, you might incorporate InStr() in your formula so TrailingSlash is assigned a different value, maybe, 3?

Norie
09-24-2007, 10:07 AM
Doug

I didn't realise you meant those Ifs.

I thought you meant some others.:oops:

If you do mean those if conditions I'm unsure on how/why you would convert them to Select Case.

Bob Phillips
09-24-2007, 10:18 AM
With wsDp
lrwSource = LR(wsDp, 1)
For Each c In rng("AbbColorTerms")
Select Case True
'NoSlash
Case Len(c.Value) - Len(Replace(c.Value, "/", "")) = 0
.Columns(3).Replace what:=Chr(32) & c & Chr(32), replacement:=Chr(32) & c.Offset(, 1) & Chr(32), lookat:=xlPart, _
searchorder:=xlByRows, MatchCase:=False, searchformat:=False, _
ReplaceFormat:=False
'WrappedSlash
Case Len(c.Value) - Len(Replace(c.Value, "/", "")) = 2
.Columns(3).Replace what:=c, replacement:=c.Offset(, 1), lookat:=xlPart, _
searchorder:=xlByRows, MatchCase:=False, searchformat:=False, _
ReplaceFormat:=False
'TrailingSlash
Case Right(c.Value, 1) = "/"
.Columns(3).Replace what:=Chr(32) & c, replacement:=Chr(32) & c.Offset(, 1), lookat:=xlPart, _
searchorder:=xlByRows, MatchCase:=False, searchformat:=False, _
ReplaceFormat:=False
'LeadingSlash
Case Left(c.Value, 1) = "/"
.Columns(3).Replace what:=c & Chr(32), replacement:=c.Offset(, 1) & Chr(32), lookat:=xlPart, _
searchorder:=xlByRows, MatchCase:=False, searchformat:=False, _
ReplaceFormat:=False
End Select
Next c
End With

Tommy
09-24-2007, 10:41 AM
I could be missing something here but how do the statements get executed after
Select Case True

Anyway my 2 cents


With wsDp
lrwSource = LR(wsDp, 1)
For Each c In rng("AbbColorTerms")
MyLen = Len(c.Value) - Len(Replace(c.Value, "/", ""))
Select Case MyLen
'NoSlash
Case 0
.Columns(3).Replace what:=Chr(32) & c & Chr(32), replacement:=Chr(32) & c.Offset(, 1) & Chr(32), _
lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False, searchformat:=False, _
ReplaceFormat:=False
'WrappedSlash
Case 2
.Columns(3).Replace what:=c, replacement:=c.Offset(, 1), lookat:=xlPart, _
searchorder:=xlByRows, MatchCase:=False, searchformat:=False, _
ReplaceFormat:=False
'TrailingSlash
Case 1 And Right(c.Value, 1) = "/"
.Columns(3).Replace what:=Chr(32) & c, replacement:=Chr(32) & c.Offset(, 1), lookat:=xlPart, _
searchorder:=xlByRows, MatchCase:=False, searchformat:=False, _
ReplaceFormat:=False
'LeadingSlash
Case 1 And Left(c.Value, 1) = "/"
.Columns(3).Replace what:=c & Chr(32), replacement:=c.Offset(, 1) & Chr(32), lookat:=xlPart, _
searchorder:=xlByRows, MatchCase:=False, searchformat:=False, _
ReplaceFormat:=False
Next c
End Select
End With

YellowLabPro
09-24-2007, 10:43 AM
Thanks All-

Ron- I started down that path, but as you pointed out there were 4 values and only 3 slots... but your approach helped me think about things. Thanks....

Bob- Chalk another one for the guy wearing the white hat- Thanks.
Malik started me on the Case Select True a while ago, but I could not link it completely. But now I see it.
Previously you recommended to me MZ Tools, which I use daily but not all the power that it possessessssss....
There is a Select Case tool- I was looking through that. I think I will head back there and see if I can get some cursory knowledge of how to implement this.

Thanks Bob-

YellowLabPro
09-24-2007, 10:46 AM
Tommy,
I may fail at accurately answering your question.
If the case is true, meaning there are two slashes in the focus cell, then it finds the values in a list on another page and replaces the match w/ that value, and on down the line....

YellowLabPro
09-24-2007, 10:48 AM
Tommy,
Thanks for your answer, I was working on something like that if Bob had not given me the answer... it was how I was thinking it would go w/ Ron's answer.

Thanks again everyone- learned a lot today

RonMcK
09-24-2007, 10:56 AM
Doug,

Tommy and Xid both have it. In the SELECT CASE line you evaluate something (a variable or a formula) once, then branch to a CASE based on that value, and execute the code specific to the case. The point of using SELECT CASE construct is to avoid using a complicated, nested IF-THEN-ELSEIF-ELSE tree.

Ron
Orlando, FL

Tommy
09-24-2007, 11:01 AM
I noticed an error some one else pointed out (RonMck) and didn't fix it ~ my bad
An alternate is also include for your viewing (not) pleasure :rofl:

With wsDp
lrwSource = LR(wsDp, 1)
For Each c In rng("AbbColorTerms")
MyLen = Len(c.Value) - Len(Replace(c.Value, "/", ""))
'select the operations based on the value of MyLen
Select Case MyLen
'NoSlash
Case 0
.Columns(3).Replace what:=Chr(32) & c & Chr(32), replacement:=Chr(32) & c.Offset(, 1) & Chr(32), _
lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False, searchformat:=False, _
ReplaceFormat:=False
'WrappedSlash
Case 2
.Columns(3).Replace what:=c, replacement:=c.Offset(, 1), lookat:=xlPart, _
searchorder:=xlByRows, MatchCase:=False, searchformat:=False, _
ReplaceFormat:=False
'TrailingSlash
Case 1 'And Right(c.Value, 1) = "/"
If Right(c.Value, 1) = "/" Then
.Columns(3).Replace what:=Chr(32) & c, replacement:=Chr(32) & c.Offset(, 1), lookat:=xlPart, _
searchorder:=xlByRows, MatchCase:=False, searchformat:=False, _
ReplaceFormat:=False
Else
'LeadingSlash
'Case 1 And Left(c.Value, 1) = "/"
.Columns(3).Replace what:=c & Chr(32), replacement:=c.Offset(, 1) & Chr(32), lookat:=xlPart, _
searchorder:=xlByRows, MatchCase:=False, searchformat:=False, _
ReplaceFormat:=False
End If
End Select
Next c
End With

YellowLabPro
09-24-2007, 12:05 PM
Thanks Tommy,
I was wondering about that. I took it at face value. Bob's solution tied directly into mine so I am using that approach, but your's is now in my toolbox.

Thanks again