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

Using SELECT CASE would help a lot. See the vba help index

select case mycell.Offset(-25) = 3 And mycell.Offset(-5) -
mycell.Offset(-23)
case 1:x="normal"
'etc
case else
end select
msgbox x

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Sandy" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>

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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>