Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simplify the Macro of Looping
Hi, Anyone can help me to simplify the below macro. I have a lot of repeating until wZ as variable. Thank you. Sub Macro1() Dim wA, wZ As String Windows("SummaryLeakFreq").Activate Sheet1.Select wA = Range("C4").Value wB = Range("C5").Value wC = Range("C6").Value wD = Range("C7").Value wE = Range("C8").Value Sheets("LeakFrequencySummary").Select 'Failure Cases Range("C7").Select ActiveCell.Formula = "=[" & wA & ".xls]LeakFreq!$B$6" 'Freq Range("D7").Select ActiveCell.Formula = "=[" & wA & ".xls]LeakFreq!$D$39" 'Pin Range("E7").Select ActiveCell.Formula = "=[" & wA & ".xls]LeakFreq!$E$39" 'Small Range("F7").Select ActiveCell.Formula = "=[" & wA & ".xls]LeakFreq!$F$39" 'Medium Range("G7").Select ActiveCell.Formula = "=[" & wA & ".xls]LeakFreq!$G$39" 'Large Range("H7").Select ActiveCell.Formula = "=[" & wA & ".xls]LeakFreq!$H$39" 'Failure Cases Range("C8").Select ActiveCell.Formula = "=[" & wB & ".xls]LeakFreq!$B$6" 'Freq Range("D8").Select ActiveCell.Formula = "=[" & wB & ".xls]LeakFreq!$D$39" 'Pin Range("E8").Select ActiveCell.Formula = "=[" & wB & ".xls]LeakFreq!$E$39" 'Small Range("F8").Select ActiveCell.Formula = "=[" & wB & ".xls]LeakFreq!$F$39" 'Medium Range("G8").Select ActiveCell.Formula = "=[" & wB & ".xls]LeakFreq!$G$39" 'Large Range("H8").Select ActiveCell.Formula = "=[" & wB & ".xls]LeakFreq!$H$39" 'Failure Cases Range("C9").Select ActiveCell.Formula = "=[" & wC & ".xls]LeakFreq!$B$6" 'Freq Range("D9").Select ActiveCell.Formula = "=[" & wC & ".xls]LeakFreq!$D$39" 'Pin Range("E9").Select ActiveCell.Formula = "=[" & wC & ".xls]LeakFreq!$E$39" 'Small Range("F9").Select ActiveCell.Formula = "=[" & wC & ".xls]LeakFreq!$F$39" 'Medium Range("G9").Select ActiveCell.Formula = "=[" & wC & ".xls]LeakFreq!$G$39" 'Large Range("H9").Select ActiveCell.Formula = "=[" & wC & ".xls]LeakFreq!$H$39" 'Failure Cases Range("C10").Select ActiveCell.Formula = "=[" & wD & ".xls]LeakFreq!$B$6" 'Freq Range("D10").Select ActiveCell.Formula = "=[" & wD & ".xls]LeakFreq!$D$39" 'Pin Range("E10").Select ActiveCell.Formula = "=[" & wD & ".xls]LeakFreq!$E$39" 'Small Range("F10").Select ActiveCell.Formula = "=[" & wD & ".xls]LeakFreq!$F$39" 'Medium Range("G10").Select ActiveCell.Formula = "=[" & wD & ".xls]LeakFreq!$G$39" 'Large Range("H10").Select ActiveCell.Formula = "=[" & wD & ".xls]LeakFreq!$H$39" 'Failure Cases Range("C11").Select ActiveCell.Formula = "=[" & wE & ".xls]LeakFreq!$B$6" 'Freq Range("D11").Select ActiveCell.Formula = "=[" & wE & ".xls]LeakFreq!$D$39" 'Pin Range("E11").Select ActiveCell.Formula = "=[" & wE & ".xls]LeakFreq!$E$39" 'Small Range("F11").Select ActiveCell.Formula = "=[" & wE & ".xls]LeakFreq!$F$39" 'Medium Range("G11").Select ActiveCell.Formula = "=[" & wE & ".xls]LeakFreq!$G$39" 'Large Range("H11").Select ActiveCell.Formula = "=[" & wE & ".xls]LeakFreq!$H$39" End Sub -- ccl28 ------------------------------------------------------------------------ ccl28's Profile: http://www.excelforum.com/member.php...o&userid=36095 View this thread: http://www.excelforum.com/showthread...hreadid=570248 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simplify the Macro of Looping
Sub Macro1()
Dim wA, wZ As String Dim i As Long Dim aryW(1 To 5) 'Windows("SummaryLeakFreq").Activate Sheet1.Select aryW(1) = Range("C4").Value aryW(2) = Range("C5").Value aryW(3) = Range("C6").Value aryW(4) = Range("C7").Value aryW(5) = Range("C8").Value With Sheets("LeakFrequencySummary") For i = 7 To 11 'Failure Cases .Range("C" & i).Formula = "=[" & aryW(i - 6) & ".xls]LeakFreq!$B$6" 'Freq .Range("D" & i).Formula = "=[" & aryW(i - 6) & ".xls]LeakFreq!$D$39" 'Pin .Range("E" & i).Formula = "=[" & aryW(i - 6) & ".xls]LeakFreq!$E$39" 'Small .Range("F" & i).Formula = "=[" & aryW(i - 6) & ".xls]LeakFreq!$F$39" 'Medium .Range("G" & i).Formula = "=[" & aryW(i - 6) & ".xls]LeakFreq!$G$39" 'Large .Range("H" & i).Formula = "=[" & aryW(i - 6) & ".xls]LeakFreq!$H$39" Next i End With End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "ccl28" wrote in message ... Hi, Anyone can help me to simplify the below macro. I have a lot of repeating until wZ as variable. Thank you. Sub Macro1() Dim wA, wZ As String Windows("SummaryLeakFreq").Activate Sheet1.Select wA = Range("C4").Value wB = Range("C5").Value wC = Range("C6").Value wD = Range("C7").Value wE = Range("C8").Value Sheets("LeakFrequencySummary").Select 'Failure Cases Range("C7").Select ActiveCell.Formula = "=[" & wA & ".xls]LeakFreq!$B$6" 'Freq Range("D7").Select ActiveCell.Formula = "=[" & wA & ".xls]LeakFreq!$D$39" 'Pin Range("E7").Select ActiveCell.Formula = "=[" & wA & ".xls]LeakFreq!$E$39" 'Small Range("F7").Select ActiveCell.Formula = "=[" & wA & ".xls]LeakFreq!$F$39" 'Medium Range("G7").Select ActiveCell.Formula = "=[" & wA & ".xls]LeakFreq!$G$39" 'Large Range("H7").Select ActiveCell.Formula = "=[" & wA & ".xls]LeakFreq!$H$39" 'Failure Cases Range("C8").Select ActiveCell.Formula = "=[" & wB & ".xls]LeakFreq!$B$6" 'Freq Range("D8").Select ActiveCell.Formula = "=[" & wB & ".xls]LeakFreq!$D$39" 'Pin Range("E8").Select ActiveCell.Formula = "=[" & wB & ".xls]LeakFreq!$E$39" 'Small Range("F8").Select ActiveCell.Formula = "=[" & wB & ".xls]LeakFreq!$F$39" 'Medium Range("G8").Select ActiveCell.Formula = "=[" & wB & ".xls]LeakFreq!$G$39" 'Large Range("H8").Select ActiveCell.Formula = "=[" & wB & ".xls]LeakFreq!$H$39" 'Failure Cases Range("C9").Select ActiveCell.Formula = "=[" & wC & ".xls]LeakFreq!$B$6" 'Freq Range("D9").Select ActiveCell.Formula = "=[" & wC & ".xls]LeakFreq!$D$39" 'Pin Range("E9").Select ActiveCell.Formula = "=[" & wC & ".xls]LeakFreq!$E$39" 'Small Range("F9").Select ActiveCell.Formula = "=[" & wC & ".xls]LeakFreq!$F$39" 'Medium Range("G9").Select ActiveCell.Formula = "=[" & wC & ".xls]LeakFreq!$G$39" 'Large Range("H9").Select ActiveCell.Formula = "=[" & wC & ".xls]LeakFreq!$H$39" 'Failure Cases Range("C10").Select ActiveCell.Formula = "=[" & wD & ".xls]LeakFreq!$B$6" 'Freq Range("D10").Select ActiveCell.Formula = "=[" & wD & ".xls]LeakFreq!$D$39" 'Pin Range("E10").Select ActiveCell.Formula = "=[" & wD & ".xls]LeakFreq!$E$39" 'Small Range("F10").Select ActiveCell.Formula = "=[" & wD & ".xls]LeakFreq!$F$39" 'Medium Range("G10").Select ActiveCell.Formula = "=[" & wD & ".xls]LeakFreq!$G$39" 'Large Range("H10").Select ActiveCell.Formula = "=[" & wD & ".xls]LeakFreq!$H$39" 'Failure Cases Range("C11").Select ActiveCell.Formula = "=[" & wE & ".xls]LeakFreq!$B$6" 'Freq Range("D11").Select ActiveCell.Formula = "=[" & wE & ".xls]LeakFreq!$D$39" 'Pin Range("E11").Select ActiveCell.Formula = "=[" & wE & ".xls]LeakFreq!$E$39" 'Small Range("F11").Select ActiveCell.Formula = "=[" & wE & ".xls]LeakFreq!$F$39" 'Medium Range("G11").Select ActiveCell.Formula = "=[" & wE & ".xls]LeakFreq!$G$39" 'Large Range("H11").Select ActiveCell.Formula = "=[" & wE & ".xls]LeakFreq!$H$39" End Sub -- ccl28 ------------------------------------------------------------------------ ccl28's Profile: http://www.excelforum.com/member.php...o&userid=36095 View this thread: http://www.excelforum.com/showthread...hreadid=570248 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simplify this macro | Excel Discussion (Misc queries) | |||
simplify a macro | Excel Worksheet Functions | |||
How do I simplify a macro in excel? | Excel Programming | |||
Simplify a macro | Excel Programming | |||
looking to simplify a recorded macro with code | Excel Programming |