Select Case nested in If Else... where's the break?

[ramohse]

Hello all,


Firstly, thank you all very much for your time. I am trying to create a toggle button in Developer that will switch a particular range on a series of worksheets between two sources of information. Independently the Select Case functions work properly (thanks SamT), but when integrated with the If Else statement of the toggle button I get a "Else without If" error. here is the code:



Private Sub ToggleButton1_Click()
Dim Cel As Range
Dim strReplaceWhat As String
Dim strWithWhat As String
Dim Ws As Worksheet


If ToggleButton1.Value Then
ToggleButton1.Caption = "Click for X"
For Each Ws In Worksheets
Select Case Right(Ws.Name, 3)
Case "Qtr"
strReplaceWhat = "Y"
strWithWhat = "X"
Case "5yr"
strReplaceWhat = "Y"
strWithWhat = "X"
Case Else
GoTo WSNEXT
End Select
For Each Cel In Ws.Range("C11:N37")
Cel.Replace strReplaceWhat, strWithWhat

Next Cel
Else:
ToggleButton1.Caption = "Click for Y"
For Each Ws In Worksheets
Select Case Right(Ws.Name, 3)
Case "Qtr"
strReplaceWhat = "X"
strWithWhat = "Y"
Case "5yr"
strReplaceWhat = "X"
strWithWhat = "Y"
Case Else
GoTo WSNEXT
End Select
For Each Cel In Ws.Range("C11:N37")
Cel.Replace strReplaceWhat, strWithWhat

Next Cel
End If

WSNEXT:
Next Ws
End Sub


For the life of me I can't figure out where my "If" breaks. I tried adding a few "End If" statments at the end just in case, and that didn't work. Any assistance that can be provided will be greatly appreciated. Thank you!



BONUS QUESTION: The "Y" database yields numbers as they would appear, the "X" database yields them in millions (Y = 1,000,000.00 vs X = 1.00). So between the two I would also have to divide by 1,000,000. I fooled around with a [C11:N37]/x , where Dim x As Integer x = 1000000, but what I don't want to happen is for every time I toggle the button the numbers compound the division. I can probably figure this out, but if anyone has any shortcuts/advice I'd appreciate it. Thanks!

[SamT]

Try this:

For Each Cel In Ws.Range("C11:N37")
Cel.Replace strReplaceWhat, strWithWhat

Next Cel
Else: Lose the Colon and move the rest of the line down.
ToggleButton1.Caption = "Click for Y"
For Each Ws In Worksheets
Select Case Right(Ws.Name, 3)

[p45cal]

try this:
Private Sub ToggleButton1_Click()
Dim Cel As Range
Dim strReplaceWhat As String
Dim strWithWhat As String
Dim Ws As Worksheet

If ToggleButton1.Value Then
ToggleButton1.Caption = "Click for X"
For Each Ws In Worksheets
Select Case Right(Ws.Name, 3)
Case "Qtr", "5yr"
strReplaceWhat = "Y"
strWithWhat = "X"
For Each Cel In Ws.Range("C11:N37")
Cel.Replace strReplaceWhat, strWithWhat
Next Cel
End Select
Next Ws
Else
ToggleButton1.Caption = "Click for Y"
For Each Ws In Worksheets
Select Case Right(Ws.Name, 3)
Case "Qtr", "5yr"
strReplaceWhat = "X"
strWithWhat = "Y"
For Each Cel In Ws.Range("C11:N37")
Cel.Replace strReplaceWhat, strWithWhat
Next Cel
End Select
Next Ws
End If
End Sub
or this:

Private Sub ToggleButton1_Click2()
Dim Cel As Range
Dim strReplaceWhat As String
Dim strWithWhat As String
Dim Ws As Worksheet

If ToggleButton1.Value Then
strReplaceWhat = "Y"
strWithWhat = "X"
ToggleButton1.Caption = "Click for X"
Else
strReplaceWhat = "X"
strWithWhat = "Y"
ToggleButton1.Caption = "Click for Y"
End If
For Each Ws In Worksheets
Select Case Right(Ws.Name, 3)
Case "Qtr", "5yr"
For Each Cel In Ws.Range("C11:N37")
Cel.Replace strReplaceWhat, strWithWhat
Next Cel
End Select
Next Ws
End If
End Sub

[SamT]

Bonus Q: Format the "x" cells as Custom = "0.0,,\M"

\M adds the M suffix

Physically dividing a number by 1000 (or 10000) in order to report a scaled down figure is bad practice. This method should be chosen instead. Not only is it unnecessary but it also causes numeric change. Using this number format method does not change the value of the figure, only its’ appearance! The accuracy of figures should not be compromised for the sake of reporting!

Thanks to
]http://jonvonderheyden.net/

[ramohse]

Wow! You all are brilliant. Thank you! It totally works now, and saves having to make multiple files (which is... inelegant).

And the bonus question! Duh! I am embarassed to admit I hadn't even thought to change the formatting, which will be easier to code for use in flexible scenarios. Thank you very much.