ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Need Urgent Help! (https://www.excelbanter.com/excel-programming/331531-need-urgent-help.html)

Ed517
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

Ron Coderre[_5_]
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

Tushar Mehta
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

Ron Coderre[_5_]
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

Greg Wilson
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

Greg Wilson
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

Ron Coderre[_5_]
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

Greg Wilson
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

Ed517
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


All times are GMT +1. The time now is 04:17 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com