ExcelBanter - Mods to macro

Thanks Tom,
Works a treat.
Regards,
Richard
-----Original Message-----
Sub BestTest()
ActiveSheet.Unprotect

Range("d3.d7").Select
Selection.ClearContents
Range("c9.c9").Select
Selection.ClearContents
Range("d3").Select
ActiveCell.FormulaR1C1 = "Highest"

Dim MaxVal As Double
Dim i As Long, j As Long, k As Long
Dim ii As Long, jj As Long, kk As Long
Dim i1 As Long, i2 As Long, iii As Long
Dim j1 As Long, j2 As Long
Dim k1 As Long, k2 As Long
Dim varr(1 To 7)

varr(1) = "Y"
varr(2) = "N"
For i = 1 To 5
varr(i + 2) = i
Next i
l = 0
MaxVal = -100000
For iii = 1 To 3
Select Case iii
Case 1
i1 = 1: i2 = 2
j1 = 1: j2 = 7
k1 = 1: k2 = 7
Case 2
i1 = 3: i2 = 7
j1 = 1: j2 = 2
k1 = 1: k2 = 7
Case 3
i1 = 3: i2 = 7
j1 = 3: j2 = 7
k1 = 1: k2 = 2
End Select

For i = i1 To i2
For j = j1 To j2
For k = k1 To k2
If Not (i = 2 And j = 2 And k = 2) Then
Range("C3").Value = varr(i)
Range("C4").Value = varr(j)
Range("C5").Value = varr(k)
If Range("Q8").Value MaxVal Then
MaxVal = Range("Q8").Value
ii = i
jj = j
kk = k
End If
End If
Next
Next
Next
Next
Range("C3").Value = varr(ii)
Range("C4").Value = varr(jj)
Range("C5").Value = varr(kk)

Application.ScreenUpdating = True
ActiveSheet.Protect DrawingObjects:=True, _
Contents:=True, Scenarios _
:=True

End Sub

--
Regards,
Tom Ogilvy

Richard wrote in message
...
Hello,

Below is code which does the following.
Inserts a "Y" or an "N" in cells C3, C4 and C5 and tests
all combinations to find the maximum value which is
calculated by the spreadsheet and the result found in
Q8.

The macro then completes when the Maximum value is found
leaving the Y's and N's in C3,C4 and C5 as a visual to
see
which combination produced the maximum. There can be all
Y's in the input cells but there cannot be all N's. It
all
works fine as is.

My problem is this:-
What changes to the code are needed to not only use Y
and
N as the input, but numbers 1,2,3,4 and 5 as well. In
this
case C3 to C5 can have any combinations of Y, N and the
numbers to find the maximum, calculated and shown in Q8
(Target). Again cannot have all N's OR all Numbers. All
other combinations are acceptable.

Could anyone help with this please.
Thankyou
Richard

Sub BestTest()
ActiveSheet.Unprotect

Range("d3.d7").Select
Selection.ClearContents
Range("c9.c9").Select
Selection.ClearContents
Range("d3").Select
ActiveCell.FormulaR1C1 = "Highest"

Dim MaxVal As Double
Dim i As Long, j As Long, k As Long
Dim ii As Long, jj As Long, kk As Long

Dim varr(1 To 2, 1 To 2, 1 To 2)

MaxVal = -100000
For i = 1 To 2
For j = 1 To 2
For k = 1 To 2
If Not (i = 2 And j = 2 And k = 2) Then

'Only this code works in my version of Excel
If i = 1
Then
Range("C3").Value = "Y"
Else
Range("C3").Value = "N"
End If
If j = 1 Then
Range("C4").Value = "Y"
Else
Range("C4").Value = "N"
End If
If k = 1 Then
Range("C5").Value = "Y"
Else
Range("C5").Value = "N"
End If

' Range("C3").Value = IIf(i =
1, "Y", "N") 'Crashes Sub or Function not defined
' Range("C4").Value = IIf(j = 1, "Y", "N")
' Range("C5").Value = IIf(k = 1, "Y", "N")

varr(i, j, k) = Range("Q8").Value
If Range("Q8").Value MaxVal Then
MaxVal = Range("Q8").Value
ii = i
jj = j
kk = k
End If
End If
Next
Next
Next

If ii = 1 Then
Range("C3").Value = "Y"
Else
Range("C3").Value = "N"
End If
If jj = 1 Then
Range("C4").Value = "Y"
Else
Range("C4").Value = "N"
End If
If kk = 1 Then
Range("C5").Value = "Y"
Else
Range("C5").Value = "N"
End If

Application.ScreenUpdating = True
ActiveSheet.Protect DrawingObjects:=True,
Contents:=True,
Scenarios _
:=True

End Sub

.