Simplify the Macro of Looping

C

ccl28

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
 
B

Bob Phillips

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 said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

AutoDetect the path of the file? 4
slow macro 3
Who can tell me where I go wrong? 5
Macro to generate FileName of the source file 1
Looping(Rookies) 4
workbook open in looping 2
error correction on vba macro 1
Finding last constant in range 1

Top