| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Urgent Help!
I'm writing a code but seems too long for me.
Is there a way to shorten it out? Partial of code is written below. Thank you very much and any input would be greatly appreciated. Private Sub GetSawn201Prop() If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Co Then Range("C12") = "675" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Co Then Range("C13") = "300" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Co Then Range("C14") = "135" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Co Then Range("E12") = "350" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Co Then Range("E13") = "1050" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Co Then Range("E14") = "1.000000" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Std Then Range("C12") = "375" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Std Then Range("C13") = "175" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Std Then Range("C14") = "135" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Std Then Range("E12") = "350" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Std Then Range("E13") = "850" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Std Then Range("E14") = ".900000" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Ut Then Range("C12") = "175" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Ut Then Range("C13") = "75" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Ut Then Range("C14") = "135" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Ut Then Range("E12") = "350" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Ut Then Range("E13") = "550" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Ut Then Range("E14") = ".800000" End Sub Ed517 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need Urgent Help!
You might try something like this:
Public Sub GetSawn201Prop() If Range("F8") = TF And Range("I7") = EWP Then Select Case Range("I8").Value Case Is = Co: Range("C12") = "675" Range("C13") = "300" Range("C14") = "135" Range("E12") = "350" Range("E13") = "1050" Range("E14") = "1.000000" Case Is = Std: Range("C12") = "375" Range("C13") = "175" Range("C14") = "135" Range("E12") = "350" Range("E13") = "850" Range("E14") = ".900000" Case Is = Ut: Range("C12") = "175" Range("C13") = "75" Range("C14") = "135" Range("E12") = "350" Range("E13") = "550" Range("E14") = ".800000" End Select End If End Sub Does that help? -- Regards, Ron |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need Urgent Help!
If Range("F8") = TF And Range("I7") = EWP _
And Range("I8") = Co Then Range("C12") = "675" Range("C13") = "300" Range("C14") = "135" Range("E12") = "350" 'etc. End If If Range("F8") = TF And Range("I7") = EWP _ And Range("I8") = Std Then Range("C12") = "375" Range("C13") = "175" 'etc. End If 'do the same for the last condition and assignments -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , Ed517 @discussions.microsoft.com says... I'm writing a code but seems too long for me. Is there a way to shorten it out? Partial of code is written below. Thank you very much and any input would be greatly appreciated. Private Sub GetSawn201Prop() If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Co Then Range("C12") = "675" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Co Then Range("C13") = "300" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Co Then Range("C14") = "135" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Co Then Range("E12") = "350" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Co Then Range("E13") = "1050" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Co Then Range("E14") = "1.000000" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Std Then Range("C12") = "375" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Std Then Range("C13") = "175" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Std Then Range("C14") = "135" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Std Then Range("E12") = "350" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Std Then Range("E13") = "850" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Std Then Range("E14") = ".900000" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Ut Then Range("C12") = "175" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Ut Then Range("C13") = "75" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Ut Then Range("C14") = "135" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Ut Then Range("E12") = "350" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Ut Then Range("E13") = "550" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Ut Then Range("E14") = ".800000" End Sub Ed517 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need Urgent Help!
Also, Since we're streamlining......
There's no need to enclose the numeric values in quotes: Private Sub GetSawn201Prop() If Range("F8") = TF And Range("I7") = EWP Then Select Case Range("I8").Value Case Is = Co: Range("C12") = 675 Range("C13") = 300 Range("C14") = 135 Range("E12") = 350 Range("E13") = 1050 Range("E14") = 1 Case Is = Std: Range("C12") = 375 Range("C13") = 175 Range("C14") = 135 Range("E12") = 350 Range("E13") = 850 Range("E14") = 0.9 Case Is = Ut: Range("C12") = 175 Range("C13") = 75 Range("C14") = 135 Range("E12") = 350 Range("E13") = 550 Range("E14") = 0.8 End Select End If End Sub Helping yet? ----------- Regards, Ron |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need Urgent Help!
Where Sheets("Settings") is a hidden sheet used to store the table values.
The values Co, Std and Ut are assumed to be constants or variables and not text values contained in cell I8. To test, name a sheet "Settings" and type the table values respectively into ranges A1:C3, A5:C7, A8:C10 of this sheet. It is assumed that the worksheet you refer to is named "Main". Change the name in the code to suit. Dim ws1 As Worksheet, ws2 As Worksheet Dim r As Range Dim Co, Std, Ut Set ws1 = Sheets("Settings") Set ws2 = Sheets("Main") Set r = ws2.Range("C12:E14") Select Case ws2.Range("I8").Value Case Co r.Value = ws1.Range("A1:C3").Value Case Std r.Value = ws1.Range("A5:C7").Value Case Ut r.Value = ws1.Range("A8:C10").Value Case Else r.Value = "N/A" End Select Regards, Greg "Ed517" wrote: I'm writing a code but seems too long for me. Is there a way to shorten it out? Partial of code is written below. Thank you very much and any input would be greatly appreciated. Private Sub GetSawn201Prop() If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Co Then Range("C12") = "675" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Co Then Range("C13") = "300" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Co Then Range("C14") = "135" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Co Then Range("E12") = "350" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Co Then Range("E13") = "1050" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Co Then Range("E14") = "1.000000" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Std Then Range("C12") = "375" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Std Then Range("C13") = "175" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Std Then Range("C14") = "135" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Std Then Range("E12") = "350" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Std Then Range("E13") = "850" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Std Then Range("E14") = ".900000" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Ut Then Range("C12") = "175" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Ut Then Range("C13") = "75" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Ut Then Range("C14") = "135" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Ut Then Range("E12") = "350" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Ut Then Range("E13") = "550" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Ut Then Range("E14") = ".800000" End Sub Ed517 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need Urgent Help!
A minor typo:
I had meant the last range to be A9:C11 so that there's a gap between it and the preceeding table. Type the Ut values into it instead. Code should be: Case Ut r.Value = ws1.Range("A9:C11").Value Regards, Greg "Greg Wilson" wrote: Where Sheets("Settings") is a hidden sheet used to store the table values. The values Co, Std and Ut are assumed to be constants or variables and not text values contained in cell I8. To test, name a sheet "Settings" and type the table values respectively into ranges A1:C3, A5:C7, A8:C10 of this sheet. It is assumed that the worksheet you refer to is named "Main". Change the name in the code to suit. Dim ws1 As Worksheet, ws2 As Worksheet Dim r As Range Dim Co, Std, Ut Set ws1 = Sheets("Settings") Set ws2 = Sheets("Main") Set r = ws2.Range("C12:E14") Select Case ws2.Range("I8").Value Case Co r.Value = ws1.Range("A1:C3").Value Case Std r.Value = ws1.Range("A5:C7").Value Case Ut r.Value = ws1.Range("A8:C10").Value Case Else r.Value = "N/A" End Select Regards, Greg "Ed517" wrote: I'm writing a code but seems too long for me. Is there a way to shorten it out? Partial of code is written below. Thank you very much and any input would be greatly appreciated. Private Sub GetSawn201Prop() If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Co Then Range("C12") = "675" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Co Then Range("C13") = "300" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Co Then Range("C14") = "135" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Co Then Range("E12") = "350" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Co Then Range("E13") = "1050" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Co Then Range("E14") = "1.000000" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Std Then Range("C12") = "375" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Std Then Range("C13") = "175" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Std Then Range("C14") = "135" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Std Then Range("E12") = "350" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Std Then Range("E13") = "850" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Std Then Range("E14") = ".900000" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Ut Then Range("C12") = "175" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Ut Then Range("C13") = "75" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Ut Then Range("C14") = "135" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Ut Then Range("E12") = "350" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Ut Then Range("E13") = "550" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Ut Then Range("E14") = ".800000" End Sub Ed517 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need Urgent Help!
Greg:
The OP didn't mention anything about writing in column D, right? So, I think if you're going that route...you may want to try this (using your example): Dim ws1 As Worksheet, ws2 As Worksheet Dim Co, Std, Ut Set ws1 = Sheets("Settings") Set ws2 = Sheets("Main") Set r = Union(ws2.Range("C12:C14"), ws2.Range("E12:E14")) Select Case ws2.Range("I8").Value Case Co r.Value = ws1.Range("A1:A3", "C1:C3").Value Case Std r.Value = ws1.Range("A5:A7", "C5:C7").Value Case Ut r.Value = ws1.Range("A9:A11", "C9:C11").Value Case Else r.Value = "N/A" End Select Am I on the right track here? -- Regards, Ron |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need Urgent Help!
Thanks for pointing that out Ron. It was a quick read on my part. I do
believe, that since the OP said the code was just a portion of that required, that my approach is a good one since it allows for much simpler code as well as easy maintenance should any of the values require change. I concur completely with your correction. Best regards, Greg "Ron Coderre" wrote: Greg: The OP didn't mention anything about writing in column D, right? So, I think if you're going that route...you may want to try this (using your example): Dim ws1 As Worksheet, ws2 As Worksheet Dim Co, Std, Ut Set ws1 = Sheets("Settings") Set ws2 = Sheets("Main") Set r = Union(ws2.Range("C12:C14"), ws2.Range("E12:E14")) Select Case ws2.Range("I8").Value Case Co r.Value = ws1.Range("A1:A3", "C1:C3").Value Case Std r.Value = ws1.Range("A5:A7", "C5:C7").Value Case Ut r.Value = ws1.Range("A9:A11", "C9:C11").Value Case Else r.Value = "N/A" End Select Am I on the right track here? -- Regards, Ron |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need Urgent Help!
Thank you very much, Guys!
I tried your suggestions and they worked perfectly. Ed517 "Ed517" wrote: I'm writing a code but seems too long for me. Is there a way to shorten it out? Partial of code is written below. Thank you very much and any input would be greatly appreciated. Private Sub GetSawn201Prop() If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Co Then Range("C12") = "675" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Co Then Range("C13") = "300" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Co Then Range("C14") = "135" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Co Then Range("E12") = "350" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Co Then Range("E13") = "1050" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Co Then Range("E14") = "1.000000" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Std Then Range("C12") = "375" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Std Then Range("C13") = "175" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Std Then Range("C14") = "135" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Std Then Range("E12") = "350" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Std Then Range("E13") = "850" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Std Then Range("E14") = ".900000" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Ut Then Range("C12") = "175" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Ut Then Range("C13") = "75" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Ut Then Range("C14") = "135" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Ut Then Range("E12") = "350" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Ut Then Range("E13") = "550" If Range("F8") = TF And Range("I7") = EWP And Range("I8") = Ut Then Range("E14") = ".800000" End Sub Ed517 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IME MODE FOR EXCEL 2007 (URGENT URGENT) | Excel Discussion (Misc queries) | |||
| Urgent-Urgent VBA LOOP | Excel Discussion (Misc queries) | |||
| Please help..urgent ! | Excel Programming | |||
| Macro help urgent urgent | Excel Programming | |||
| Macro help urgent urgent | Excel Programming | |||
Linear Mode
