Loop Select Case

[cityanangelstx2004]

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 o1328. Once this is achieved how would I combine the two select statements under the same sub routine.

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 o1328
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.

 

[lozzablake]

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?

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]

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]

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]

Ok I remembered you can turn events off. So now try this:

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

 

[cityanangelstx2004]

You are awesome. It worked. You did it. I appreciate the help.