| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simulation
Hi ,
I am trying to run a simulation the data that I have are in the following tables. A3 (A) J3 k3 Profile Area ix P Cond. Cond. L 80x8 12.27 2.43 2.43 TRUE TRUE (B) Profile Area ix p L 70x7 9.4 2.12 7.38 L 80x8 12.27 2.43 9.63 L 90x9 15.52 2.73 12.18 L 100x10 19.15 3.04 15.04 L 110X10 21.15 3.36 16.61 L120X11 25.37 3.66 19.92 1. In table (A) cell (A3) I have a dropdown list that contains 6 choices from (L70*7 to L120*11) 2. Depending on the choice I select from the dropdown list 2 logical arguments are effected Cell (J3) (=IF(I3<=H3,TRUE,FALSE)) and Cell (K3) (=IF(G3<=200,TRUE,FALSE)). 3. In this case the choice of (L80*8) from the dropdown list gives the correct answer as both arguments in cell (J3 & K3) return with the answer (TRUE) which what I want. 4. Is there a way where I can run the 6 choices in the dropdown list like I would in a simulation? 5. Where the simulation starts with the choice (L70*7) and ends with (L120*11). 6. When Both Cells (J3 & K3) gives the answer (TRUE) than the simulation ends. 7. In the example I have given in (3) the simulation will end at (L80*8). The first choice the gives (TRUE) answer. 8. If (False) answer is found in the 6 choices than the simulation ends with L120*11. Best Regards, Sherif |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Simulation
The only way I found to do the simulation is to actually put the data into
A3. the code below gets the drop down list data and puts it into A3 Sub test() Set a = Range("A3") ValidationRange = Range("A3").Validation.Formula1 'remove equal sign ValidationRange = Mid(ValidationRange, 2) Set VRange = Range(ValidationRange) For Each cell In VRange Range("A3") = cell If Range("J3") = True And _ Range("K3") = True Then Exit For End If Next cell End Sub "Sherif" wrote: Hi , I am trying to run a simulation the data that I have are in the following tables. A3 (A) J3 k3 Profile Area ix P Cond. Cond. L 80x8 12.27 2.43 2.43 TRUE TRUE (B) Profile Area ix p L 70x7 9.4 2.12 7.38 L 80x8 12.27 2.43 9.63 L 90x9 15.52 2.73 12.18 L 100x10 19.15 3.04 15.04 L 110X10 21.15 3.36 16.61 L120X11 25.37 3.66 19.92 1. In table (A) cell (A3) I have a dropdown list that contains 6 choices from (L70*7 to L120*11) 2. Depending on the choice I select from the dropdown list 2 logical arguments are effected Cell (J3) (=IF(I3<=H3,TRUE,FALSE)) and Cell (K3) (=IF(G3<=200,TRUE,FALSE)). 3. In this case the choice of (L80*8) from the dropdown list gives the correct answer as both arguments in cell (J3 & K3) return with the answer (TRUE) which what I want. 4. Is there a way where I can run the 6 choices in the dropdown list like I would in a simulation? 5. Where the simulation starts with the choice (L70*7) and ends with (L120*11). 6. When Both Cells (J3 & K3) gives the answer (TRUE) than the simulation ends. 7. In the example I have given in (3) the simulation will end at (L80*8). The first choice the gives (TRUE) answer. 8. If (False) answer is found in the 6 choices than the simulation ends with L120*11. Best Regards, Sherif |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Simulation
Thank you very much the code worked perfectly
I would like to run this code 3 times for cell A3, AA3 and AAA3 in the same worksheet could you please explain how to do it. Best Regards Sherif "Joel" wrote: The only way I found to do the simulation is to actually put the data into A3. the code below gets the drop down list data and puts it into A3 Sub test() Set a = Range("A3") ValidationRange = Range("A3").Validation.Formula1 'remove equal sign ValidationRange = Mid(ValidationRange, 2) Set VRange = Range(ValidationRange) For Each cell In VRange Range("A3") = cell If Range("J3") = True And _ Range("K3") = True Then Exit For End If Next cell End Sub "Sherif" wrote: Hi , I am trying to run a simulation the data that I have are in the following tables. A3 (A) J3 k3 Profile Area ix P Cond. Cond. L 80x8 12.27 2.43 2.43 TRUE TRUE (B) Profile Area ix p L 70x7 9.4 2.12 7.38 L 80x8 12.27 2.43 9.63 L 90x9 15.52 2.73 12.18 L 100x10 19.15 3.04 15.04 L 110X10 21.15 3.36 16.61 L120X11 25.37 3.66 19.92 1. In table (A) cell (A3) I have a dropdown list that contains 6 choices from (L70*7 to L120*11) 2. Depending on the choice I select from the dropdown list 2 logical arguments are effected Cell (J3) (=IF(I3<=H3,TRUE,FALSE)) and Cell (K3) (=IF(G3<=200,TRUE,FALSE)). 3. In this case the choice of (L80*8) from the dropdown list gives the correct answer as both arguments in cell (J3 & K3) return with the answer (TRUE) which what I want. 4. Is there a way where I can run the 6 choices in the dropdown list like I would in a simulation? 5. Where the simulation starts with the choice (L70*7) and ends with (L120*11). 6. When Both Cells (J3 & K3) gives the answer (TRUE) than the simulation ends. 7. In the example I have given in (3) the simulation will end at (L80*8). The first choice the gives (TRUE) answer. 8. If (False) answer is found in the 6 choices than the simulation ends with L120*11. Best Regards, Sherif |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Simulation
I wasn't sure if J3 & K3 were fixed or need to be change to AJ3, AAJ3, AAJ3
so I did both cases Sub testA() Set ValidationGroup = Range("A3", "AA3", "AAA3") For Each GroupCell In ValidationGroup ValidationRange = GroupCell.Validation.Formula1 'remove equal sign ValidationRange = Mid(ValidationRange, 2) Set VRange = Range(ValidationRange) For Each cell In VRange GroupCell = cell If Range("J3") = True And _ Range("K3") = True Then Exit For End If Next cell Next GroupCell End Sub Sub testB() Set ValidationGroup = Range("", "A", "AA") For Each Group In ValidationGroup ValidationRange = Range(Group & "A3").Validation.Formula1 'remove equal sign ValidationRange = Mid(ValidationRange, 2) Set VRange = Range(ValidationRange) For Each cell In VRange Range(Group & "A3") = cell If Range(Group & "J3") = True And _ Range(Group & "K3") = True Then Exit For End If Next cell Next Group End Sub "Sherif" wrote: Thank you very much the code worked perfectly I would like to run this code 3 times for cell A3, AA3 and AAA3 in the same worksheet could you please explain how to do it. Best Regards Sherif "Joel" wrote: The only way I found to do the simulation is to actually put the data into A3. the code below gets the drop down list data and puts it into A3 Sub test() Set a = Range("A3") ValidationRange = Range("A3").Validation.Formula1 'remove equal sign ValidationRange = Mid(ValidationRange, 2) Set VRange = Range(ValidationRange) For Each cell In VRange Range("A3") = cell If Range("J3") = True And _ Range("K3") = True Then Exit For End If Next cell End Sub "Sherif" wrote: Hi , I am trying to run a simulation the data that I have are in the following tables. A3 (A) J3 k3 Profile Area ix P Cond. Cond. L 80x8 12.27 2.43 2.43 TRUE TRUE (B) Profile Area ix p L 70x7 9.4 2.12 7.38 L 80x8 12.27 2.43 9.63 L 90x9 15.52 2.73 12.18 L 100x10 19.15 3.04 15.04 L 110X10 21.15 3.36 16.61 L120X11 25.37 3.66 19.92 1. In table (A) cell (A3) I have a dropdown list that contains 6 choices from (L70*7 to L120*11) 2. Depending on the choice I select from the dropdown list 2 logical arguments are effected Cell (J3) (=IF(I3<=H3,TRUE,FALSE)) and Cell (K3) (=IF(G3<=200,TRUE,FALSE)). 3. In this case the choice of (L80*8) from the dropdown list gives the correct answer as both arguments in cell (J3 & K3) return with the answer (TRUE) which what I want. 4. Is there a way where I can run the 6 choices in the dropdown list like I would in a simulation? 5. Where the simulation starts with the choice (L70*7) and ends with (L120*11). 6. When Both Cells (J3 & K3) gives the answer (TRUE) than the simulation ends. 7. In the example I have given in (3) the simulation will end at (L80*8). The first choice the gives (TRUE) answer. 8. If (False) answer is found in the 6 choices than the simulation ends with L120*11. Best Regards, Sherif |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Simulation
Thank you very much could you please help me with the following:
1. The cells containing the dropdown list will be A3 to A223 and the cells containing the TRUE argument corresponding to that are J3 to J223 & K3 to K223. 2. This same thing will be repeated for cells AA3 to AA223, AJ3 to AJ223 & AK3 to AK223. 3. And repeated again for cells BA3 to BA223, BJ3 to BJ223 & BK3 to BK223. 4. My workbook contains 8 worksheets is there is a way to apply the code to all the worksheets. Best Regards, Sherif "Joel" wrote: I wasn't sure if J3 & K3 were fixed or need to be change to AJ3, AAJ3, AAJ3 so I did both cases Sub testA() Set ValidationGroup = Range("A3", "AA3", "AAA3") For Each GroupCell In ValidationGroup ValidationRange = GroupCell.Validation.Formula1 'remove equal sign ValidationRange = Mid(ValidationRange, 2) Set VRange = Range(ValidationRange) For Each cell In VRange GroupCell = cell If Range("J3") = True And _ Range("K3") = True Then Exit For End If Next cell Next GroupCell End Sub Sub testB() Set ValidationGroup = Range("", "A", "AA") For Each Group In ValidationGroup ValidationRange = Range(Group & "A3").Validation.Formula1 'remove equal sign ValidationRange = Mid(ValidationRange, 2) Set VRange = Range(ValidationRange) For Each cell In VRange Range(Group & "A3") = cell If Range(Group & "J3") = True And _ Range(Group & "K3") = True Then Exit For End If Next cell Next Group End Sub "Sherif" wrote: Thank you very much the code worked perfectly I would like to run this code 3 times for cell A3, AA3 and AAA3 in the same worksheet could you please explain how to do it. Best Regards Sherif "Joel" wrote: The only way I found to do the simulation is to actually put the data into A3. the code below gets the drop down list data and puts it into A3 Sub test() Set a = Range("A3") ValidationRange = Range("A3").Validation.Formula1 'remove equal sign ValidationRange = Mid(ValidationRange, 2) Set VRange = Range(ValidationRange) For Each cell In VRange Range("A3") = cell If Range("J3") = True And _ Range("K3") = True Then Exit For End If Next cell End Sub "Sherif" wrote: Hi , I am trying to run a simulation the data that I have are in the following tables. A3 (A) J3 k3 Profile Area ix P Cond. Cond. L 80x8 12.27 2.43 2.43 TRUE TRUE (B) Profile Area ix p L 70x7 9.4 2.12 7.38 L 80x8 12.27 2.43 9.63 L 90x9 15.52 2.73 12.18 L 100x10 19.15 3.04 15.04 L 110X10 21.15 3.36 16.61 L120X11 25.37 3.66 19.92 1. In table (A) cell (A3) I have a dropdown list that contains 6 choices from (L70*7 to L120*11) 2. Depending on the choice I select from the dropdown list 2 logical arguments are effected Cell (J3) (=IF(I3<=H3,TRUE,FALSE)) and Cell (K3) (=IF(G3<=200,TRUE,FALSE)). 3. In this case the choice of (L80*8) from the dropdown list gives the correct answer as both arguments in cell (J3 & K3) return with the answer (TRUE) which what I want. 4. Is there a way where I can run the 6 choices in the dropdown list like I would in a simulation? 5. Where the simulation starts with the choice (L70*7) and ends with (L120*11). 6. When Both Cells (J3 & K3) gives the answer (TRUE) than the simulation ends. 7. In the example I have given in (3) the simulation will end at (L80*8). The first choice the gives (TRUE) answer. 8. If (False) answer is found in the 6 choices than the simulation ends with L120*11. Best Regards, Sherif |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Simulation
I don't know if each simulation is seperate or combined. You may need to
remove one or two of the following statement If Found = True Then Exit For Sub sim_list() Found = False For Each sht In ThisWorkbook.Sheets With sht ValidationGroup = Array("", "A", "B") For Each Group In ValidationGroup ValidationRange = _ .Range(Group & "A3").Validation.Formula1 'remove equal sign ValidationRange = Mid(ValidationRange, 2) Set VRange = .Range(ValidationRange) For Each cell In VRange .Range(Group & "A3") = cell If .Range(Group & "J3") = True And _ .Range(Group & "K3") = True Then Found = True Exit For End If Next cell If Found = True Then Exit For Next Group End With If Found = True Then Exit For Next sht End Sub "Sherif" wrote: Thank you very much could you please help me with the following: 1. The cells containing the dropdown list will be A3 to A223 and the cells containing the TRUE argument corresponding to that are J3 to J223 & K3 to K223. 2. This same thing will be repeated for cells AA3 to AA223, AJ3 to AJ223 & AK3 to AK223. 3. And repeated again for cells BA3 to BA223, BJ3 to BJ223 & BK3 to BK223. 4. My workbook contains 8 worksheets is there is a way to apply the code to all the worksheets. Best Regards, Sherif "Joel" wrote: I wasn't sure if J3 & K3 were fixed or need to be change to AJ3, AAJ3, AAJ3 so I did both cases Sub testA() Set ValidationGroup = Range("A3", "AA3", "AAA3") For Each GroupCell In ValidationGroup ValidationRange = GroupCell.Validation.Formula1 'remove equal sign ValidationRange = Mid(ValidationRange, 2) Set VRange = Range(ValidationRange) For Each cell In VRange GroupCell = cell If Range("J3") = True And _ Range("K3") = True Then Exit For End If Next cell Next GroupCell End Sub Sub testB() Set ValidationGroup = Range("", "A", "AA") For Each Group In ValidationGroup ValidationRange = Range(Group & "A3").Validation.Formula1 'remove equal sign ValidationRange = Mid(ValidationRange, 2) Set VRange = Range(ValidationRange) For Each cell In VRange Range(Group & "A3") = cell If Range(Group & "J3") = True And _ Range(Group & "K3") = True Then Exit For End If Next cell Next Group End Sub "Sherif" wrote: Thank you very much the code worked perfectly I would like to run this code 3 times for cell A3, AA3 and AAA3 in the same worksheet could you please explain how to do it. Best Regards Sherif "Joel" wrote: The only way I found to do the simulation is to actually put the data into A3. the code below gets the drop down list data and puts it into A3 Sub test() Set a = Range("A3") ValidationRange = Range("A3").Validation.Formula1 'remove equal sign ValidationRange = Mid(ValidationRange, 2) Set VRange = Range(ValidationRange) For Each cell In VRange Range("A3") = cell If Range("J3") = True And _ Range("K3") = True Then Exit For End If Next cell End Sub "Sherif" wrote: Hi , I am trying to run a simulation the data that I have are in the following tables. A3 (A) J3 k3 Profile Area ix P Cond. Cond. L 80x8 12.27 2.43 2.43 TRUE TRUE (B) Profile Area ix p L 70x7 9.4 2.12 7.38 L 80x8 12.27 2.43 9.63 L 90x9 15.52 2.73 12.18 L 100x10 19.15 3.04 15.04 L 110X10 21.15 3.36 16.61 L120X11 25.37 3.66 19.92 1. In table (A) cell (A3) I have a dropdown list that contains 6 choices from (L70*7 to L120*11) 2. Depending on the choice I select from the dropdown list 2 logical arguments are effected Cell (J3) (=IF(I3<=H3,TRUE,FALSE)) and Cell (K3) (=IF(G3<=200,TRUE,FALSE)). 3. In this case the choice of (L80*8) from the dropdown list gives the correct answer as both arguments in cell (J3 & K3) return with the answer (TRUE) which what I want. 4. Is there a way where I can run the 6 choices in the dropdown list like I would in a simulation? 5. Where the simulation starts with the choice (L70*7) and ends with (L120*11). 6. When Both Cells (J3 & K3) gives the answer (TRUE) than the simulation ends. 7. In the example I have given in (3) the simulation will end at (L80*8). The first choice the gives (TRUE) answer. 8. If (False) answer is found in the 6 choices than the simulation ends with L120*11. Best Regards, Sherif |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I need to set up a simulation | Excel Programming | |||
| I need to set up a simulation | Setting up and Configuration of Excel | |||
| How can I speed up my VBA simulation?? | Excel Programming | |||
| Car assign simulation | Excel Worksheet Functions | |||
| Car assign simulation | Excel Programming | |||
Hybrid Mode
