|
Code simplification
I have the following code which works fine. My question though is how can it
be simplified (which I am sure it can). For Each mycell In Range("C31:K31,M31:U31") If mycell.Offset(-25) = 3 And mycell.Offset(-5) - mycell.Offset(-23) = 1 Then mycell.Value = "Normal" mycell.Offset(1).Value = 0 ElseIf mycell.Offset(-25) = 3 And mycell.Offset(-5) - mycell.Offset(-23) = 2 Then mycell.Value = "+1" mycell.Offset(1).Value = 1 ElseIf mycell.Offset(-25) = 3 And mycell.Offset(-5) - mycell.Offset(-23) = 3 Then mycell.Value = "+2" mycell.Offset(1).Value = 2 *******Etc******** ElseIf mycell.Offset(-25) = 3 And mycell.Offset(-5) - mycell.Offset(-23) = 10 Then mycell.Value = "+9" mycell.Offset(1).Value = 9 ElseIf mycell.Offset(-25) = 3 And mycell.Offset(-5) - mycell.Offset(-23) = 11 Then mycell.Value = "Too High" mycell.Offset(1).Value = 10 ElseIf mycell.Offset(-25) = 4 And mycell.Offset(-5) - mycell.Offset(-23) = 1 Then mycell.Value = "-1" mycell.Offset(1).Value = -1 ElseIf mycell.Offset(-25) = 4 And mycell.Offset(-5) - mycell.Offset(-23) = 2 Then mycell.Value = "Nomal" mycell.Offset(1).Value = 0 ElseIf mycell.Offset(-25) = 4 And mycell.Offset(-5) - mycell.Offset(-23) = 3 Then mycell.Value = "+1" mycell.Offset(1).Value = 1 *******Etc******** ElseIf mycell.Offset(-25) = 4 And mycell.Offset(-5) - mycell.Offset(-23) = 11 Then mycell.Value = "+9" mycell.Offset(1).Value = 9 ElseIf mycell.Offset(-25) = 4 And mycell.Offset(-5) - mycell.Offset(-23) = 12 Then mycell.Value = "Too High" mycell.Offset(1).Value = 10 ElseIf mycell.Offset(-25) = 5 And mycell.Offset(-5) - mycell.Offset(-23) = 1 Then mycell.Value = "-2" mycell.Offset(1).Value = -2 ElseIf mycell.Offset(-25) = 5 And mycell.Offset(-5) - mycell.Offset(-23) = 2 Then mycell.Value = "-1" mycell.Offset(1).Value = -1 ElseIf mycell.Offset(-25) = 5 And mycell.Offset(-5) - mycell.Offset(-23) = 3 Then mycell.Value = "Normal" mycell.Offset(1).Value = 0 *******Etc******** ElseIf mycell.Offset(-25) = 5 And mycell.Offset(-5) - mycell.Offset(-23) = 13 Then mycell.Value = "Too High" mycell.Offset(1).Value = 10 End If Next mycell Thanks Sandy |
Code simplification
|
Code simplification
You could rewrite it usin Select Case to replace all of those ElseIf's and I
think it becomes more readable and probably a little more efficient. Check out Excel's Help on Select Case for variations of the individual Case evaluations available. For Each mycell In Range("C31:K31,M31:U31") Select Case mycell.Offset(-25) Case Is = 3 Select Case mysell.Offset(-5) - _ mycell.Offset(-23) Case Is = 1 mycell = "Normal" mycell.Offset(1) = 0 Case Is = 2 mycell = "+1" mycell.Offset(1) = 1 Case Is = 3 mycell = "+2" mycell.Offset(1) = 2 'add more Case Is statements as needed Case Else 'do nothing End Select Case Is = 4 Select Case mysell.Offset(-5) - _ mycell.Offset(-23) Case Is = 1 mycell = "-1" mycell.Offset(1) = -1 Case Is = 12 mycell = "Normal" mycell.Offset(1) = 0 Case Is = 3 mycell = "+1" mycell.Offset(1) = 1 'add more Case Is statements as needed Case Is = 11 mycell = "+9" mycell.Offset(1) = 9 Case Is = 12 mycell = "Too High" mycell.Offset(1) = 10 Case Else 'do nothing End Select Case Is = 5 Select Case mysell.Offset(-5) - _ mycell.Offset(-23) Case Is = 1 mycell = "-1" mycell.Offset(1) = -1 Case Is = 2 mycell = "Normal" mycell.Offset(1) = 0 Case Is = 3 mycell = "+1" mycell.Offset(1) = 1 'add more Case Is statements as needed Case Is = 13 mycell = "Too High" mycell.Offset(1) = 10 Case Else 'do nothing End Select Case Else 'if .Offset(-25) value is not 3, 4 or 5, do nothing! End Select Next ' mycell loop end "Sandy" wrote: I have the following code which works fine. My question though is how can it be simplified (which I am sure it can). For Each mycell In Range("C31:K31,M31:U31") If mycell.Offset(-25) = 3 And mycell.Offset(-5) - mycell.Offset(-23) = 1 Then mycell.Value = "Normal" mycell.Offset(1).Value = 0 ElseIf mycell.Offset(-25) = 3 And mycell.Offset(-5) - mycell.Offset(-23) = 2 Then mycell.Value = "+1" mycell.Offset(1).Value = 1 ElseIf mycell.Offset(-25) = 3 And mycell.Offset(-5) - mycell.Offset(-23) = 3 Then mycell.Value = "+2" mycell.Offset(1).Value = 2 *******Etc******** ElseIf mycell.Offset(-25) = 3 And mycell.Offset(-5) - mycell.Offset(-23) = 10 Then mycell.Value = "+9" mycell.Offset(1).Value = 9 ElseIf mycell.Offset(-25) = 3 And mycell.Offset(-5) - mycell.Offset(-23) = 11 Then mycell.Value = "Too High" mycell.Offset(1).Value = 10 ElseIf mycell.Offset(-25) = 4 And mycell.Offset(-5) - mycell.Offset(-23) = 1 Then mycell.Value = "-1" mycell.Offset(1).Value = -1 ElseIf mycell.Offset(-25) = 4 And mycell.Offset(-5) - mycell.Offset(-23) = 2 Then mycell.Value = "Nomal" mycell.Offset(1).Value = 0 ElseIf mycell.Offset(-25) = 4 And mycell.Offset(-5) - mycell.Offset(-23) = 3 Then mycell.Value = "+1" mycell.Offset(1).Value = 1 *******Etc******** ElseIf mycell.Offset(-25) = 4 And mycell.Offset(-5) - mycell.Offset(-23) = 11 Then mycell.Value = "+9" mycell.Offset(1).Value = 9 ElseIf mycell.Offset(-25) = 4 And mycell.Offset(-5) - mycell.Offset(-23) = 12 Then mycell.Value = "Too High" mycell.Offset(1).Value = 10 ElseIf mycell.Offset(-25) = 5 And mycell.Offset(-5) - mycell.Offset(-23) = 1 Then mycell.Value = "-2" mycell.Offset(1).Value = -2 ElseIf mycell.Offset(-25) = 5 And mycell.Offset(-5) - mycell.Offset(-23) = 2 Then mycell.Value = "-1" mycell.Offset(1).Value = -1 ElseIf mycell.Offset(-25) = 5 And mycell.Offset(-5) - mycell.Offset(-23) = 3 Then mycell.Value = "Normal" mycell.Offset(1).Value = 0 *******Etc******** ElseIf mycell.Offset(-25) = 5 And mycell.Offset(-5) - mycell.Offset(-23) = 13 Then mycell.Value = "Too High" mycell.Offset(1).Value = 10 End If Next mycell Thanks Sandy |
Code simplification
If I am not mistaken, I believe the entire For-Each block you posted can be
replaced with this one... Dim mycell As Range For Each mycell In Range("C31:K31,M31:U31") With mycell .Offset(1).Value = .Offset(-5) - .Offset(-23) - .Offset(-25) + 2 If .Offset(1).Value = 0 Then .Value = "Normal" ElseIf .Offset(1).Value = 10 Then .Value = "Too High" Else .Value = Format$(.Offset(1).Value, "\""+0\"";\""-0\""") End If End With Next Rick "Sandy" wrote in message ... I have the following code which works fine. My question though is how can it be simplified (which I am sure it can). For Each mycell In Range("C31:K31,M31:U31") If mycell.Offset(-25) = 3 And mycell.Offset(-5) - mycell.Offset(-23) = 1 Then mycell.Value = "Normal" mycell.Offset(1).Value = 0 ElseIf mycell.Offset(-25) = 3 And mycell.Offset(-5) - mycell.Offset(-23) = 2 Then mycell.Value = "+1" mycell.Offset(1).Value = 1 ElseIf mycell.Offset(-25) = 3 And mycell.Offset(-5) - mycell.Offset(-23) = 3 Then mycell.Value = "+2" mycell.Offset(1).Value = 2 *******Etc******** ElseIf mycell.Offset(-25) = 3 And mycell.Offset(-5) - mycell.Offset(-23) = 10 Then mycell.Value = "+9" mycell.Offset(1).Value = 9 ElseIf mycell.Offset(-25) = 3 And mycell.Offset(-5) - mycell.Offset(-23) = 11 Then mycell.Value = "Too High" mycell.Offset(1).Value = 10 ElseIf mycell.Offset(-25) = 4 And mycell.Offset(-5) - mycell.Offset(-23) = 1 Then mycell.Value = "-1" mycell.Offset(1).Value = -1 ElseIf mycell.Offset(-25) = 4 And mycell.Offset(-5) - mycell.Offset(-23) = 2 Then mycell.Value = "Nomal" mycell.Offset(1).Value = 0 ElseIf mycell.Offset(-25) = 4 And mycell.Offset(-5) - mycell.Offset(-23) = 3 Then mycell.Value = "+1" mycell.Offset(1).Value = 1 *******Etc******** ElseIf mycell.Offset(-25) = 4 And mycell.Offset(-5) - mycell.Offset(-23) = 11 Then mycell.Value = "+9" mycell.Offset(1).Value = 9 ElseIf mycell.Offset(-25) = 4 And mycell.Offset(-5) - mycell.Offset(-23) = 12 Then mycell.Value = "Too High" mycell.Offset(1).Value = 10 ElseIf mycell.Offset(-25) = 5 And mycell.Offset(-5) - mycell.Offset(-23) = 1 Then mycell.Value = "-2" mycell.Offset(1).Value = -2 ElseIf mycell.Offset(-25) = 5 And mycell.Offset(-5) - mycell.Offset(-23) = 2 Then mycell.Value = "-1" mycell.Offset(1).Value = -1 ElseIf mycell.Offset(-25) = 5 And mycell.Offset(-5) - mycell.Offset(-23) = 3 Then mycell.Value = "Normal" mycell.Offset(1).Value = 0 *******Etc******** ElseIf mycell.Offset(-25) = 5 And mycell.Offset(-5) - mycell.Offset(-23) = 13 Then mycell.Value = "Too High" mycell.Offset(1).Value = 10 End If Next mycell Thanks Sandy |
| All times are GMT +1. The time now is 10:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com