Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code to long - needs help on "if" statements
Hello,
Can someone help me - here is the code that I have but it is to long. I haev attached the first part - as you can see it goes for date=31 days. Its the if-then statements that are to long (i haev the first 3 dates below). Can someone help make a shorter if statement that will scroll throught the dates and change the location on the ranges? Thanks Private Sub CommandButton1_Click() Worksheets("73").Activate Set dateCurrent = ActiveSheet.Cells(38, 1) Worksheets("73-1 Data").Activate Set date1 = ActiveSheet.Cells(14, 1) Set date2 = ActiveSheet.Cells(15, 1) Set date3 = ActiveSheet.Cells(16, 1) Set date4 = ActiveSheet.Cells(17, 1) Set date5 = ActiveSheet.Cells(18, 1) Set date6 = ActiveSheet.Cells(19, 1) Set date7 = ActiveSheet.Cells(20, 1) Set date8 = ActiveSheet.Cells(21, 1) Set date9 = ActiveSheet.Cells(22, 1) Set date10 = ActiveSheet.Cells(23, 1) Set date11 = ActiveSheet.Cells(24, 1) Set date12 = ActiveSheet.Cells(25, 1) Set date13 = ActiveSheet.Cells(26, 1) Set date14 = ActiveSheet.Cells(27, 1) Set date15 = ActiveSheet.Cells(28, 1) Set date16 = ActiveSheet.Cells(29, 1) Set date17 = ActiveSheet.Cells(30, 1) Set date18 = ActiveSheet.Cells(31, 1) Set date19 = ActiveSheet.Cells(32, 1) Set date20 = ActiveSheet.Cells(33, 1) Set date21 = ActiveSheet.Cells(34, 1) Set date22 = ActiveSheet.Cells(35, 1) Set date23 = ActiveSheet.Cells(36, 1) Set date24 = ActiveSheet.Cells(37, 1) Set date25 = ActiveSheet.Cells(38, 1) Set date26 = ActiveSheet.Cells(39, 1) Set date27 = ActiveSheet.Cells(40, 1) Set date28 = ActiveSheet.Cells(41, 1) Set date29 = ActiveSheet.Cells(42, 1) Set date30 = ActiveSheet.Cells(43, 1) Set date31 = ActiveSheet.Cells(44, 1) Let Worksheets("73").Range("b42") = Worksheets("73").Range("j12") Let Worksheets("73").Range("b56") = Worksheets("73").Range("u14") Let Worksheets("73").Range("b70") = Worksheets("73").Range("j33") Let Worksheets("73").Range("b84") = Worksheets("73").Range("u28") If dateCurrent = date1 Then Worksheets("73").Range("b40").Copy Destination:=Worksheets("73-1 Data").Range("b14") Worksheets("73").Range("b41").Copy Destination:=Worksheets("73-1 Data").Range("c14") Worksheets("73").Range("b42").Copy Destination:=Worksheets("73-1 Data").Range("d14") Worksheets("73").Range("b43").Copy Destination:=Worksheets("73-1 Data").Range("e14") Worksheets("73").Range("b44").Copy Destination:=Worksheets("73-1 Data").Range("f14") Worksheets("73").Range("b54").Copy Destination:=Worksheets("73-2 Data").Range("b14") Worksheets("73").Range("b55").Copy Destination:=Worksheets("73-2 Data").Range("c14") Worksheets("73").Range("b56").Copy Destination:=Worksheets("73-2 Data").Range("d14") Worksheets("73").Range("b57").Copy Destination:=Worksheets("73-2 Data").Range("e14") Worksheets("73").Range("b58").Copy Destination:=Worksheets("73-2 Data").Range("f14") Worksheets("73").Range("b68").Copy Destination:=Worksheets("73-3 Data").Range("b14") Worksheets("73").Range("b69").Copy Destination:=Worksheets("73-3 Data").Range("c14") Worksheets("73").Range("b70").Copy Destination:=Worksheets("73-3 Data").Range("d14") Worksheets("73").Range("b71").Copy Destination:=Worksheets("73-3 Data").Range("e14") Worksheets("73").Range("b72").Copy Destination:=Worksheets("73-3 Data").Range("f14") Worksheets("73").Range("b82").Copy Destination:=Worksheets("73-4 Data").Range("b14") Worksheets("73").Range("b83").Copy Destination:=Worksheets("73-4 Data").Range("c14") Worksheets("73").Range("b84").Copy Destination:=Worksheets("73-4 Data").Range("d14") Worksheets("73").Range("b85").Copy Destination:=Worksheets("73-4 Data").Range("e14") Worksheets("73").Range("b86").Copy Destination:=Worksheets("73-4 Data").Range("f14") ElseIf dateCurrent = date2 Then Worksheets("73").Range("b40").Copy Destination:=Worksheets("73-1 Data").Range("b15") Worksheets("73").Range("b41").Copy Destination:=Worksheets("73-1 Data").Range("c15") Worksheets("73").Range("b42").Copy Destination:=Worksheets("73-1 Data").Range("d15") Worksheets("73").Range("b43").Copy Destination:=Worksheets("73-1 Data").Range("e15") Worksheets("73").Range("b44").Copy Destination:=Worksheets("73-1 Data").Range("f15") Worksheets("73").Range("b54").Copy Destination:=Worksheets("73-2 Data").Range("b15") Worksheets("73").Range("b55").Copy Destination:=Worksheets("73-2 Data").Range("c15") Worksheets("73").Range("b56").Copy Destination:=Worksheets("73-2 Data").Range("d15") Worksheets("73").Range("b57").Copy Destination:=Worksheets("73-2 Data").Range("e15") Worksheets("73").Range("b58").Copy Destination:=Worksheets("73-2 Data").Range("f15") Worksheets("73").Range("b68").Copy Destination:=Worksheets("73-3 Data").Range("b15") Worksheets("73").Range("b69").Copy Destination:=Worksheets("73-3 Data").Range("c15") Worksheets("73").Range("b70").Copy Destination:=Worksheets("73-3 Data").Range("d15") Worksheets("73").Range("b71").Copy Destination:=Worksheets("73-3 Data").Range("e15") Worksheets("73").Range("b72").Copy Destination:=Worksheets("73-3 Data").Range("f15") Worksheets("73").Range("b82").Copy Destination:=Worksheets("73-4 Data").Range("b15") Worksheets("73").Range("b83").Copy Destination:=Worksheets("73-4 Data").Range("c15") Worksheets("73").Range("b84").Copy Destination:=Worksheets("73-4 Data").Range("d15") Worksheets("73").Range("b85").Copy Destination:=Worksheets("73-4 Data").Range("e15") Worksheets("73").Range("b86").Copy Destination:=Worksheets("73-4 Data").Range("f15") ElseIf dateCurrent = date3 Then Worksheets("73").Range("b40").Copy Destination:=Worksheets("73-1 Data").Range("b16") Worksheets("73").Range("b41").Copy Destination:=Worksheets("73-1 Data").Range("c16") Worksheets("73").Range("b42").Copy Destination:=Worksheets("73-1 Data").Range("d16") Worksheets("73").Range("b43").Copy Destination:=Worksheets("73-1 Data").Range("e16") Worksheets("73").Range("b44").Copy Destination:=Worksheets("73-1 Data").Range("f16") Worksheets("73").Range("b54").Copy Destination:=Worksheets("73-2 Data").Range("b16") Worksheets("73").Range("b55").Copy Destination:=Worksheets("73-2 Data").Range("c16") Worksheets("73").Range("b56").Copy Destination:=Worksheets("73-2 Data").Range("d16") Worksheets("73").Range("b57").Copy Destination:=Worksheets("73-2 Data").Range("e16") Worksheets("73").Range("b58").Copy Destination:=Worksheets("73-2 Data").Range("f16") Worksheets("73").Range("b68").Copy Destination:=Worksheets("73-3 Data").Range("b16") Worksheets("73").Range("b69").Copy Destination:=Worksheets("73-3 Data").Range("c16") Worksheets("73").Range("b70").Copy Destination:=Worksheets("73-3 Data").Range("d16") Worksheets("73").Range("b71").Copy Destination:=Worksheets("73-3 Data").Range("e16") Worksheets("73").Range("b72").Copy Destination:=Worksheets("73-3 Data").Range("f16") Worksheets("73").Range("b82").Copy Destination:=Worksheets("73-4 Data").Range("b16") Worksheets("73").Range("b83").Copy Destination:=Worksheets("73-4 Data").Range("c16") Worksheets("73").Range("b84").Copy Destination:=Worksheets("73-4 Data").Range("d16") Worksheets("73").Range("b85").Copy Destination:=Worksheets("73-4 Data").Range("e16") Worksheets("73").Range("b86").Copy Destination:=Worksheets("73-4 Data").Range("f16") Else: ActiveSheet.Cells(51, 1) = "no" End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Need more than 7 nested "IF" statements in Excel" | Excel Discussion (Misc queries) | |||
"Reading"the logic behind too-long command statements | Excel Worksheet Functions | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |