Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Select Case help
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Select Case help

I'm running a macro that has a select case in it. Some of the cases
have the same procedures. I was thinking I could just create another
sub to call from the case to decrease repetitveness. The problem is I
use cells from the case row in the calculations. Any ideas on how I
can work this out?

Dim cell As Range
Lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

For Each cell In Range("M1:M" & Lastrow)
Select Case cell.Value

Case 5
Call Calculatecells

Case 5.5
Call Calculatecells

Case 6 7 8 10 etc etc
Call Calculatecells

end select
next


Sub calculatecells

(1500+('cell AB value *10))/ 'cell W value


--
Ramthebuffs
------------------------------------------------------------------------
Ramthebuffs's Profile: http://www.excelforum.com/member.php...o&userid=16429
View this thread: http://www.excelforum.com/showthread...hreadid=475631

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 92
Default Select Case help
"Ramthebuffs"
wrote
: I'm running a macro that has a select case in it. Some of the cases
: have the same procedures. I was thinking I could just create another
: sub to call from the case to decrease repetitveness. The problem is I
: use cells from the case row in the calculations. Any ideas on how I
: can work this out?
:
: Dim cell As Range
: Lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
:
: For Each cell In Range("M1:M" & Lastrow)
: Select Case cell.Value
:
: Case 5
: Call Calculatecells
:
: Case 5.5
: Call Calculatecells
:
: Case 6 7 8 10 etc etc
: Call Calculatecells
:
: end select
: next
:
: Sub calculatecells
:
: (1500+('cell AB value *10))/ 'cell W value
:
: --
: Ramthebuffs

Just pass the cell.row value to your sub
Dim cell As Range
Lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For Each cell In Range("M1:M" & Lastrow)
Select Case cell.Value
Case 5
CalculateCells cell.Row
Case 5.5
....

Sub CalculateCells(myRow as Integer)
....cells(myRow, 28).Value

Paul D




Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Select Case help
Take a look at VBA's for Select Case.

You'll see you could use:

select case cell.value
case is 5, 5.5, 6, 7, 8, 10
call calculatecells


I'm not sure if you can use this, but one of the samples from Help is:
Case 1 To 4, 7 To 9, 11, 13, Is MaxNumber

----

But you could pass the cell to that subroutine, too:

select case cell.value
case is 5, 5.5, 6, 7, 8, 10
call calculatecells(cell)

.....

Sub calculatecells(cell as range)

dim ABCell as range
set ABCell = cell.parent.cells(cell.row,"AB")

'not sure what this does, though
(1500+(ABCell.value *10))/ cell.value

end sub

There's no reason you have to use Cell as the passed parm, but there's no reason
why not to, either. And I bet your code will be easier to modify


Ramthebuffs wrote:

I'm running a macro that has a select case in it. Some of the cases
have the same procedures. I was thinking I could just create another
sub to call from the case to decrease repetitveness. The problem is I
use cells from the case row in the calculations. Any ideas on how I
can work this out?

Dim cell As Range
Lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

For Each cell In Range("M1:M" & Lastrow)
Select Case cell.Value

Case 5
Call Calculatecells

Case 5.5
Call Calculatecells

Case 6 7 8 10 etc etc
Call Calculatecells

end select
next

Sub calculatecells

(1500+('cell AB value *10))/ 'cell W value

--
Ramthebuffs
------------------------------------------------------------------------
Ramthebuffs's Profile: http://www.excelforum.com/member.php...o&userid=16429
View this thread: http://www.excelforum.com/showthread...hreadid=475631

--

Dave Peterson
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,494
Default Select Case help
i didn't want to write the whole thing, but give this a try:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
If Not Intersect(Target, Range("c7:dj52")) Is Nothing Then
If UCase(Target.Value) = "AL" Then
Target.Interior.ColorIndex = 3
ElseIf UCase(Target.Value) = "SL" Then
Target.Interior.ColorIndex = 3
Target.Font.ColorIndex = 2

End If
End If
End If
End Sub

--


Gary


"Ramthebuffs"
wrote in message
...

I'm running a macro that has a select case in it. Some of the cases
have the same procedures. I was thinking I could just create another
sub to call from the case to decrease repetitveness. The problem is I
use cells from the case row in the calculations. Any ideas on how I
can work this out?

Dim cell As Range
Lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

For Each cell In Range("M1:M" & Lastrow)
Select Case cell.Value

Case 5
Call Calculatecells

Case 5.5
Call Calculatecells

Case 6 7 8 10 etc etc
Call Calculatecells

end select
next


Sub calculatecells

(1500+('cell AB value *10))/ 'cell W value


--
Ramthebuffs
------------------------------------------------------------------------
Ramthebuffs's Profile:
http://www.excelforum.com/member.php...o&userid=16429
View this thread: http://www.excelforum.com/showthread...hreadid=475631



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,494
Default Select Case help
did a search and replace and this seems to be ok. give it a try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
If Not Intersect(Target, Range("c7:dj52")) Is Nothing Then
If UCase(Target.Value) = "AL" Then
Target.Interior.ColorIndex = 3
ElseIf UCase(Target.Value) = "SL" Then
Target.Interior.ColorIndex = 3
Target.Font.ColorIndex = 2

ElseIf UCase(Target.Value) = "FL" Then
Target.Interior.ColorIndex = 3
Target.Font.ColorIndex = 2

ElseIf UCase(Target.Value) = "ML" Then
Target.Interior.ColorIndex = 3
Target.Font.ColorIndex = 2

ElseIf UCase(Target.Value) = "DL" Then
Target.Interior.ColorIndex = 3
Target.Font.ColorIndex = 2

ElseIf UCase(Target.Value) = "WL" Then
Target.Interior.ColorIndex = 3
Target.Font.ColorIndex = 2

ElseIf UCase(Target.Value) = "OL" Then
Target.Interior.ColorIndex = 3
Target.Font.ColorIndex = 2

ElseIf UCase(Target.Value) = "CL" Then
Target.Interior.ColorIndex = 3
Target.Font.ColorIndex = 2

ElseIf UCase(Target.Value) = "PL" Then
Target.Interior.ColorIndex = 3
Target.Font.ColorIndex = 2

ElseIf UCase(Target.Value) = "JD" Then
Target.Interior.ColorIndex = 3
Target.Font.ColorIndex = 2

ElseIf UCase(Target.Value) = "X" Then
Target.Interior.ColorIndex = 15
Target.Font.ColorIndex = 1

ElseIf UCase(Target.Value) = "HO" Then
Target.Interior.ColorIndex = 15
Target.Font.ColorIndex = 1

ElseIf UCase(Target.Value) = "00" Then
Target.Interior.ColorIndex = 20
Target.Font.ColorIndex = 1

ElseIf UCase(Target.Value) = "01" Then
Target.Interior.ColorIndex = 20
Target.Font.ColorIndex = 1

ElseIf UCase(Target.Value) = "02" Then
Target.Interior.ColorIndex = 20
Target.Font.ColorIndex = 1

ElseIf UCase(Target.Value) = "03" Then
Target.Interior.ColorIndex = 20
Target.Font.ColorIndex = 1

ElseIf UCase(Target.Value) = "04" Then
Target.Interior.ColorIndex = 19
Target.Font.ColorIndex = 1

ElseIf UCase(Target.Value) = "05" Then
Target.Interior.ColorIndex = 19
Target.Font.ColorIndex = 1

ElseIf UCase(Target.Value) = "06" Then
Target.Interior.ColorIndex = 19
Target.Font.ColorIndex = 1

ElseIf UCase(Target.Value) = "07" Then
Target.Interior.ColorIndex = 19
Target.Font.ColorIndex = 1

ElseIf UCase(Target.Value) = "08" Then
Target.Interior.ColorIndex = 19
Target.Font.ColorIndex = 1

ElseIf UCase(Target.Value) = "09" Then
Target.Interior.ColorIndex = 19
Target.Font.ColorIndex = 1

ElseIf UCase(Target.Value) = 10 Then
Target.Interior.ColorIndex = 19
Target.Font.ColorIndex = 1

ElseIf UCase(Target.Value) = 11 Then
Target.Interior.ColorIndex = 19
Target.Font.ColorIndex = 1

ElseIf UCase(Target.Value) = 12 Then
Target.Interior.ColorIndex = 17
Target.Font.ColorIndex = 2

ElseIf UCase(Target.Value) = 13 Then
Target.Interior.ColorIndex = 17
Target.Font.ColorIndex = 2

ElseIf UCase(Target.Value) = 14 Then
Target.Interior.ColorIndex = 17
Target.Font.ColorIndex = 2

ElseIf UCase(Target.Value) = 15 Then
Target.Interior.ColorIndex = 17
Target.Font.ColorIndex = 2

ElseIf UCase(Target.Value) = 16 Then
Target.Interior.ColorIndex = 17
Target.Font.ColorIndex = 2

ElseIf UCase(Target.Value) = 17 Then
Target.Interior.ColorIndex = 17
Target.Font.ColorIndex = 2

ElseIf UCase(Target.Value) = 18 Then
Target.Interior.ColorIndex = 17
Target.Font.ColorIndex = 2

ElseIf UCase(Target.Value) = 19 Then
Target.Interior.ColorIndex = 20
Target.Font.ColorIndex = 1

ElseIf UCase(Target.Value) = 20 Then
Target.Interior.ColorIndex = 20
Target.Font.ColorIndex = 1

ElseIf UCase(Target.Value) = 21 Then
Target.Interior.ColorIndex = 20
Target.Font.ColorIndex = 1

ElseIf UCase(Target.Value) = 22 Then
Target.Interior.ColorIndex = 20
Target.Font.ColorIndex = 1

ElseIf UCase(Target.Value) = 23 Then
Target.Interior.ColorIndex = 20
Target.Font.ColorIndex = 1


ElseIf UCase(Target.Value) = "HO" Then
Target.Interior.ColorIndex = 15
Target.Font.ColorIndex = 2

ElseIf UCase(Target.Value) = "T" Then
Target.Interior.ColorIndex = 4
Target.Font.ColorIndex = 3

ElseIf UCase(Target.Value) = "<T" Then
Target.Interior.ColorIndex = 4
Target.Font.ColorIndex = 3

ElseIf UCase(Target.Value) = "OP" Then
Target.Interior.ColorIndex = 4
Target.Font.ColorIndex = 3

ElseIf UCase(Target.Value) = "TR" Then
Target.Interior.ColorIndex = 4
Target.Font.ColorIndex = 3

ElseIf UCase(Target.Value) = "AD" Then
Target.Interior.ColorIndex = 4
Target.Font.ColorIndex = 3

ElseIf UCase(Target.Value) = "MS" Then
Target.Interior.ColorIndex = 4
Target.Font.ColorIndex = 3

ElseIf UCase(Target.Value) = "TD" Then
Target.Interior.ColorIndex = 4
Target.Font.ColorIndex = 3

ElseIf UCase(Target.Value) = "Null" Then
Target.Interior.ColorIndex = 16
Target.Font.ColorIndex = 1

Else
Target.Interior.ColorIndex = 0
Target.Font.ColorIndex = 1

End If


End If
End If
End Sub



--


Gary


"Ramthebuffs"
wrote in message
...

I'm running a macro that has a select case in it. Some of the cases
have the same procedures. I was thinking I could just create another
sub to call from the case to decrease repetitveness. The problem is I
use cells from the case row in the calculations. Any ideas on how I
can work this out?

Dim cell As Range
Lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

For Each cell In Range("M1:M" & Lastrow)
Select Case cell.Value

Case 5
Call Calculatecells

Case 5.5
Call Calculatecells

Case 6 7 8 10 etc etc
Call Calculatecells

end select
next


Sub calculatecells

(1500+('cell AB value *10))/ 'cell W value


--
Ramthebuffs
------------------------------------------------------------------------
Ramthebuffs's Profile:
http://www.excelforum.com/member.php...o&userid=16429
View this thread: http://www.excelforum.com/showthread...hreadid=475631



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Select Case ignored Preschool Mike Excel Worksheet Functions 4 September 9th 09 08:12 PM
Select Case jlclyde Excel Discussion (Misc queries) 5 January 6th 09 09:05 PM
Case without Select Case error problem Ayo Excel Discussion (Misc queries) 2 May 16th 08 03:48 PM
Select Case Jeff Excel Discussion (Misc queries) 1 February 27th 06 02:56 PM
Select Case Susan Hayes Excel Programming 1 November 4th 04 08:37 PM


All times are GMT +1. The time now is 01:35 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement