Loop Select Case

cityanangelstx2004

Board Regular
Joined
Jun 18, 2004
Messages
56
I have two columns in my excel spreadsheet that I would like to include the following select case statement. One is for regular hours and the other is for total hours. Does anyone know how I can loop the select case statement for each column so that this covers cells g13:g28 and the next select statement covers o13:eek:28. Once this is achieved how would I combine the two select statements under the same sub routine. :confused:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

'Regular Hours for the range g13:g28
Select Case Range("s13").Value
Case Is > 0
Range("g13").Value = Range("t13").Value
Case Else
Range("g13").Value = Range("u13").Value
End Select

'Total Hours for the range o13:eek:28
Select Case Range("u13").Value
Case Is > 0
Range("o13").Value = Range("u13").Value
Case Else
Range("o13").Value = ""
End Select

Any help is appreciated. Thank you in advance.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

lozzablake

Well-known Member
Joined
Dec 15, 2005
Messages
818
try this. If you keep it in the change event the macro will run every time the worksheet is changed. Is that what you want?

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim i As Integer
        
    For i = 13 To 28
    'Regular Hours for the range g13:g28
        Select Case Cells(i, 19).Value
            Case Is > 0
                Cells(i, 7) = Cells(i, 20)
            Case Else
                Cells(i, 7) = Cells(i, 21)
        End Select
    Next i
        
    For i = 13 To 28
    'Total Hours for the range o13:o28
        Select Case Cells(i, 21).Value
            Case Is > 0
                Cells(i, 15) = Cells(i, 21)
            Case Else
                 Cells(i, 15) = ""
        End Select
    Next i

End Sub
 

cityanangelstx2004

Board Regular
Joined
Jun 18, 2004
Messages
56
This is exactly what I was looking for. For some reason it goes in a continual loop and I have to press the ESC key to debug. I attempted to set Application.ScreenUpdating = False, but this didn't help. Everytime I update a cell my pointer flashes and the system freezes up. Any thoughts? :wink:
 

lozzablake

Well-known Member
Joined
Dec 15, 2005
Messages
818
Of course you have a macro that fires when you change a value in the worksheet. Each time you run through the case statement you are changing a value so it fires the macro again.

Do you really need this from a worksheet change event?
 

lozzablake

Well-known Member
Joined
Dec 15, 2005
Messages
818
Ok I remembered you can turn events off. So now try this:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Dim i As Integer
        
    For i = 13 To 28
    'Regular Hours for the range g13:g28
        Select Case Cells(i, 19).Value
            Case Is > 0
                Cells(i, 7) = Cells(i, 20)
            Case Else
                Cells(i, 7) = Cells(i, 21)
        End Select
    Next i
        
    For i = 13 To 28
    'Total Hours for the range o13:o28
        Select Case Cells(i, 21).Value
            Case Is > 0
                Cells(i, 15) = Cells(i, 21)
            Case Else
                 Cells(i, 15) = ""
        End Select
    Next i
   
    Application.EnableEvents = True
End Sub
 

Forum statistics

Threads
1,141,018
Messages
5,703,757
Members
421,314
Latest member
Mooncake1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top