PC Review
Home Forums Reviews Articles Register
Member Login:

PC Review > Forums > Newsgroups > Microsoft Excel > Microsoft Excel Programming > Case Select

Reply
Thread Tools Rate Thread

Case Select

 
 
Eric
Guest
Posts: n/a
 
      18th Feb 2008
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
 
Reply With Quote
 
 
 
 
Dave Peterson
Guest
Posts: n/a
 
      18th Feb 2008
You have a couple of "For/Next" loops in your code. Both are missing the "Next"
line.

Eric wrote:
>
> 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
 
Reply With Quote
 
Gary''s Student
Guest
Posts: n/a
 
      18th Feb 2008
Make sure you have a Next for each For in the appropriate places in your code.
--
Gary''s Student - gsnu200769


"Eric" wrote:

> 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
 
Reply With Quote
 
Don Guillett
Guest
Posts: n/a
 
      18th Feb 2008
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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Eric" <(E-Mail Removed)> wrote in message
news:3300D203-5136-4015-AB9E-(E-Mail Removed)...
>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
 
Reply With Quote
 
Dana DeLouis
Guest
Posts: n/a
 
      18th Feb 2008
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.

--
Dana DeLouis

"Eric" <(E-Mail Removed)> wrote in message
news:3300D203-5136-4015-AB9E-(E-Mail Removed)...
> 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) = Range("JMF_Revision_Date")

> 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
 
Reply With Quote
 
 
 
Reply

« how to use MZTools to get a calltree? | Setting row height to 0 in the visible celss of a range »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
translate VB select case to switch case problem Rich P Microsoft C# .NET 13 8th Aug 2009 06:07 PM
Case without Select Case error problem Ayo Microsoft Excel Misc 2 16th May 2008 03:48 PM
Select case / case is, multiple arguments Werner Rohrmoser Microsoft Excel Programming 2 3rd Jan 2006 10:55 AM
how to case select with case-insensitive string ? Tee Microsoft ASP .NET 3 23rd Jun 2004 08:40 PM
how to case select with case-insensitive string ? Tee Microsoft VB .NET 3 23rd Jun 2004 08:40 PM


Features
Forums Startup Database Reviews Articles FAQ / Guidelines About Us
 

Advertising
 

Newsgroups
Windows Vista Windows XP Microsoft Word Microsoft DotNet Windows 2000 AntiSpyware Microsoft Access Microsoft Excel Microsoft Outlook Hardware
 


All times are GMT +1. The time now is 06:30 AM.
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc.
Contact Us - Archive - Privacy Statement - Top