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

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

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 <5553730B-27B6-453C-BF7E-D519B304769B@microsoft.com>, 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
>

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

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

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

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

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
>

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