Loop Select Case

[brorick]

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


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:o28
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.

[ALe]

It isn't easy to understand what the loop must do. Anyway have a look to this example and adapt this piece of code to your needs
For i = 13 To 28
Select Case Range("s13").Value
Case Is > 0
Range("g" & i).Value = Range("t" & i).Value
Case Else
Range("g" & i).Value = Range("u" & i).Value
End Select
next i

[brorick]

ALe thank you for the quick reply. This is exactly what I am looking for, but when I attempt this my spreadsheet freezes up. I am attaching a copy of the timesheet so that you can see the end result. Because I cleaned up the timesheet you will notice that the code I referenced earlier now begins on cell s3 instead of s13. Thanks for your help. :bow:

[mdmackillop]

Hi Brorick,
Don't forget to use the VBA tabs.:thumb

[ALe]

brorick.
It would be great if you could describe what you are trying to do and which kind of error you get.

I can't see why you change the values of column G with values of T or U that are always null.

[brorick]

The following code references the correct cells based on the cleaner copy of the timesheet I included. I am sorry for the confusion. Please let me know if you want me to elaborate further. Thank you.

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

'Regular Hours for the range e13:e28
Select Case Range("q13").Value
Case Is > 0
Range("e13").Value = Range("r13").Value
Case Else
Range("e13").Value = Range("s13").Value
End Select

'Total Hours for the range m13:m28
Select Case Range("q13").Value
Case Is > 0
Range("m13").Value = Range("u13").Value
End Select

[Cyberdude]

It's not clear to me why you are even using Select Case. Why not use:
For i = 13 To 28
If Range("Q" & i) > 0 _
Then Range("E" & i) = Range("R" & i) _
Else Range("E" & i) = Range("S" & i)
If Range("Q" & i) > 0 _
Then Range("M" & i) = Range("U" & i)
Next i I probably have your columns all wrong, but the style is what I'm trying to illustrate.

[ALe]

where's the problem?

[johnske]

It looks like this may be part of a Worksheet_Change event procedure. If this is so, use Application.EnableEvents = False before the start of the loop and Application.EnableEvents = True after it... :)

[geekgirlau]

Also, your code should occur between "Private Sub" and "End Sub" ...


Private Sub Worksheet_Change(ByVal Target As Range)
'Regular Hours for the range e13:e28
Select Case Range("q13").Value
Case Is > 0
Range("e13").Value = Range("r13").Value
Case Else
Range("e13").Value = Range("s13").Value
End Select

'Total Hours for the range m13:m28
Select Case Range("q13").Value
Case Is > 0
Range("m13").Value = Range("u13").Value
End Select
End Sub