Select Case help

[Ramthebuffs]

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

 

[PaulD]

"Ramthebuffs" <[email protected]>
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

 

[Dave Peterson]

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

 

[Gary Keramidas]

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" <[email protected]>
wrote in message
news:[email protected]...

 

[Gary Keramidas]

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" <[email protected]>
wrote in message