Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Category Name Mods at Runtime | Charts and Charting in Excel | |||
need help to update macro to office 2007 macro enabled workbook | Excel Discussion (Misc queries) | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
using a cell value to control a counter inside a macro and displaying macro value | Excel Worksheet Functions |