Case Select

[Eric]

I am using the following macro error message. Here is the macro:

Dim Mix_Size As Integer

Mix_Size = Range("Mix_Size").Value


Sheets("JMF Changes").unprotect "1dickson"

Dim numone As Double
Dim numtwo As Double
Dim numthree As Double
Dim numfour As Double
Dim numfive As Double
Dim numsix As Double
Dim numseven As Double
Dim numeight As Double
Dim numnine As Double
Dim numten As Double
Dim numeleven As Double
Dim numtwelve As Double
Dim numthirteen As Double
Dim numfourteen As Double
Dim numfifteen As Double
Dim numsixteen As Double
Dim numseventeen As Double
Dim numeighteen As Double
Dim numninteen As Double
Dim numtwenty As Double
Dim numtwentyone As Double
Dim numtwentytwo As Double
Dim numtwentythree As Double
Dim numtwentyfour As Double
Dim numtwentyfive As Double
Dim numtwentysix As Double
Dim numtwentyseven As Double



numone = Range("JMF_Revision_Date")
numtwo = Range("_50mm")
numthree = Range("_37.5mm")
numfour = Range("_25.0mm")
numfive = Range("_19.0mm")
numsix = Range("_12.5mm")
numseven = Range("_9.5mm")
numeight = Range("_4.75mm")
numnine = Range("_2.36mm")
numten = Range("E19")
numeleven = Range("_0.600mm")
numtwelve = Range("_0.300mm")
numthirteen = Range("_0.150mm")
numfourteen = Range("_0.075mm")
numfifteen = Range("JMF_total_Pb")
numsixteen = Range("pba")
numseventeen = Range("JMF___Vtm")
numeighteen = Range("Gmm_meas.__rice")
numnineteen = Range("Agg._Gsa")
numtwenty = Range("Agg._Gsb")
numtwentyone = Range("acf")
numtwentytwo = Range("gse")
numtwentythree = Range("pwa")
numtwentyfour = Range("_0.075mm___Pb")
numtwentyfive = Range("Minimum__vma")
numtwentysix = Range("vfa_range")
numtwentyseven = Range("Gmm_nini")

Select Case Mix_Size

Case 1 '4052


For i = Sheets("test Database").Range("BD27") + 11 To 500

Sheets("JMF Changes").Cells(i, 4) = numone
Sheets("JMF Changes").Cells(i, 9) = numtwo
Sheets("JMF Changes").Cells(i, 10) = numthree
Sheets("JMF Changes").Cells(i, 22) = numfour
Sheets("JMF Changes").Cells(i, 11) = numfive
Sheets("JMF Changes").Cells(i, 12) = numsix
Sheets("JMF Changes").Cells(i, 13) = numseven
Sheets("JMF Changes").Cells(i, 14) = numeight
Sheets("JMF Changes").Cells(i, 15) = numnine
Sheets("JMF Changes").Cells(i, 16) = numten
Sheets("JMF Changes").Cells(i, 17) = numeleven
Sheets("JMF Changes").Cells(i, 18) = numtwelve
Sheets("JMF Changes").Cells(i, 19) = numthirteen
Sheets("JMF Changes").Cells(i, 20) = numfourteen
Sheets("JMF Changes").Cells(i, 5) = numfifteen
Sheets("JMF Changes").Cells(i, 28) = numsixteen
Sheets("JMF Changes").Cells(i, 6) = numseventeen
Sheets("JMF Changes").Cells(i, 24) = numeighteen
Sheets("JMF Changes").Cells(i, 25) = numnineteen
Sheets("JMF Changes").Cells(i, 26) = numtwenty
Sheets("JMF Changes").Cells(i, 27) = numtwentyone
Sheets("JMF Changes").Cells(i, 23) = numtwentytwo
Sheets("JMF Changes").Cells(i, 29) = numtwentythree
Sheets("JMF Changes").Cells(i, 30) = numtwentyfour
Sheets("JMF Changes").Cells(i, 7) = numtwentyfive
Sheets("JMF Changes").Cells(i, 8) = numtwentysix
Sheets("JMF Changes").Cells(i, 21) = numtwentyseven

Case Else
End Select

Select Case Mix_Size


Case 2 '4053

For j = Sheets("test Database").Range("BD28") + 504 To 1005

Sheets("JMF Changes").Cells(j, 4) = numone
Sheets("JMF Changes").Cells(j, 9) = numtwo
Sheets("JMF Changes").Cells(j, 10) = numthree
Sheets("JMF Changes").Cells(j, 22) = numfour
Sheets("JMF Changes").Cells(j, 11) = numfive
Sheets("JMF Changes").Cells(j, 12) = numsix
Sheets("JMF Changes").Cells(j, 13) = numseven
Sheets("JMF Changes").Cells(j, 14) = numeight
Sheets("JMF Changes").Cells(j, 15) = numnine
Sheets("JMF Changes").Cells(j, 16) = numten
Sheets("JMF Changes").Cells(j, 17) = numeleven
Sheets("JMF Changes").Cells(j, 18) = numtwelve
Sheets("JMF Changes").Cells(j, 19) = numthirteen
Sheets("JMF Changes").Cells(j, 20) = numfourteen
Sheets("JMF Changes").Cells(j, 5) = numfifteen
Sheets("JMF Changes").Cells(j, 28) = numsixteen
Sheets("JMF Changes").Cells(j, 6) = numseventeen
Sheets("JMF Changes").Cells(j, 24) = numeighteen
Sheets("JMF Changes").Cells(j, 25) = numnineteen
Sheets("JMF Changes").Cells(j, 26) = numtwenty
Sheets("JMF Changes").Cells(j, 27) = numtwentyone
Sheets("JMF Changes").Cells(j, 23) = numtwentytwo
Sheets("JMF Changes").Cells(j, 29) = numtwentythree
Sheets("JMF Changes").Cells(j, 30) = numtwentyfour
Sheets("JMF Changes").Cells(j, 7) = numtwentyfive
Sheets("JMF Changes").Cells(j, 8) = numtwentysix
Sheets("JMF Changes").Cells(j, 21) = numtwentyseven

Case Else
End Select


' Next

Sheets("JMF Changes").protect "1dickson"

End Sub

When I run this is the message I get "Case Else Outside Select Case"

Can anyone Help please..... Eric

 

[Dave Peterson]

You have a couple of "For/Next" loops in your code. Both are missing the "Next"
line.

 

[Gary''s Student]

Make sure you have a Next for each For in the appropriate places in your code.

 

[Don Guillett]

It's quite possible that your number is greater than the sum of the
cell+11+500? In any case, I took the liberty of cleaning up your code a bit.
Modify to suit

Sub selectcasefix()
Select Case Range("Mix_Size").Value
Case 1: x = Range("d27") + 11: y = 500
Case 2: x = Range("d28") + 504: y = 1005
Case Else
exit sub
End Select
'MsgBox x
'MsgBox y
For i = x To y
'MsgBox i
With Sheets("JMF Changes")
.Cells(i, 4) = numtwo 'or Range("JMF_Revision_Date")
.Cells(i, 9) = numtwo
.Cells(i, 10) = numthree
'.etc
End With
Next i
End Sub
'1 For i = Sheets("test Database").Range("BD27") + 11 To 500
' 2 For j = Sheets("test Database").Range("BD28") + 504 To 1005

 

[Dana DeLouis]

Just an idea. Instead of declaring all those variables, another option
might be:

Sheets("JMF Changes").Cells(i, 4) = Range("JMF_Revision_Date")

Another option might be to sort the code of your output range:

Sheets("JMF Changes").Cells(i, 4) = numone
Sheets("JMF Changes").Cells(i, 5) = numfifteen
Sheets("JMF Changes").Cells(i, 6) = numseventeen
Sheets("JMF Changes").Cells(i, 7) = numtwentyfive
Sheets("JMF Changes").Cells(i, 8) = numtwentysix

'...etc

Since the outputs are together, you could do a larger piece per line.
Perhaps 5 at at time: (Broken down here)

Sub Demo()
Dim M
'// Input: 1,15,17,25,26
M = Array([JMF_Revision_Date], [JMF_total_Pb], _
[JMF___Vtm], [Minimum__vma], [vfa_range])

'// Output Columns: 4,5,6,7,8
[D20:H20] = M
End Sub

Just an idea.