Update Formula Loop

R

RedFive

I have a spreadsheet that I am using as a template. My problem is that I
want to be able to change the source that the formulas pull the data from. I
used the Macro recorder, but now I have to enter the source path for each
column, about 8 times. There has to be a better way, does anybody know? I
thoght a Do Until loop might work, but not sure how to write it into the
recorded code. Thanks
 
R

RedFive

Here is one of the formulas in the worksheet
=IF('[Legacy]11103'!$S2="Checked","X",IF('[Legacy]11103'!$S2="Indeterminate","I",""))

Below is the recorded macro.
Range("A2").Select
ActiveCell.FormulaR1C1 = "=Legacy!RC2"
Range("B2").Select
ActiveCell.FormulaR1C1 = "=Legacy!RC5"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=Legacy!RC3"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=LEFT(Legacy!RC9,30)"
Range("E2").Select
ActiveCell.FormulaR1C1 = _

"=IF(Legacy!RC19=""Checked"",""X"",IF(Legacy!RC19=""Indeterminate"",""I"",""""))"
Range("F2").Select
ActiveCell.FormulaR1C1 = _

"=IF(Legacy!RC20=""Checked"",""X"",IF(Legacy!RC20=""Indeterminate"",""I"",""""))"
Range("G2").Select
ActiveCell.FormulaR1C1 = "=Legacy!RC22"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=IF(Legacy!RC33=""Checked"",""X"","""")"
Range("I2").Select
ActiveCell.FormulaR1C1 = "=IF(Legacy!RC34=""Checked"",""X"","""")"
Range("A2:I2").Select
Selection.AutoFill Destination:=Range("A2:I1499"), Type:=xlFillDefault
Range("A2:I1499").Select

Range("A1500").Select
ActiveCell.FormulaR1C1 = "=Hierarchy!R[-1498]C2"
Range("B1500").Select
ActiveCell.FormulaR1C1 = "=Hierarchy!R[-1498]C5"
Range("C1500").Select
ActiveCell.FormulaR1C1 = "=Hierarchy!R[-1498]C3"
Range("D1500").Select
ActiveCell.FormulaR1C1 = "=LEFT(Hierarchy!R[-1498]C9,30)"
Range("E1500").Select
ActiveCell.FormulaR1C1 = _

"=IF(Hierarchy!R[-1498]C19=""Checked"",""X"",IF(Hierarchy!R[-1498]C19=""Indeterminate"",""I"",""""))"
Range("F1500").Select
ActiveCell.FormulaR1C1 = _

"=IF(Hierarchy!R[-1498]C20=""Checked"",""X"",IF(Hierarchy!R[-1498]C20=""Indeterminate"",""I"",""""))"
Range("G1500").Select
ActiveCell.FormulaR1C1 = "=Hierarchy!R[-1498]C22*Hierarchy!R[-1498]C25"
Range("H1500").Select
ActiveCell.FormulaR1C1 =
"=IF(Hierarchy!R[-1498]C33=""Checked"",""X"","""")"
Range("I1500").Select
ActiveCell.FormulaR1C1 =
"=IF(Hierarchy!R[-1498]C34=""Checked"",""X"","""")"
Range("A1500:I1500").Select
Selection.AutoFill Destination:=Range("A1500:I3000"), Type:=xlFillDefault
Range("A1500:I3000").Select
 
D

Don Guillett

Have you tried this idea
Sub changesourse()
Columns(2).Replace "sheet2", "sheet3"
End Sub
 
B

Bob Phillips

Something like this?

Set rng1 = Application.InputBox("Select first cell", Type:=8)
With rng1

.FormulaR1C1 = "=Legacy!RC2"
.Offset(0, 1).FormulaR1C1 = "=Legacy!RC5"
.Offset(0, 2).FormulaR1C1 = "=Legacy!RC3"
.Offset(0, 3).FormulaR1C1 = "=LEFT(Legacy!RC9,30)"
.Offset(0, 4).FormulaR1C1 = _
"=IF(Legacy!RC19=""Checked"",""X"",IF(Legacy!RC19=""Indeterminate"",""I"",""""))"
.Offset(0, 5).FormulaR1C1 = _
"=IF(Legacy!RC20=""Checked"",""X"",IF(Legacy!RC20=""Indeterminate"",""I"",""""))"
.Offset(0, 6).FormulaR1C1 = "=Legacy!RC22"
.Offset(0, 7).FormulaR1C1 =
"=IF(Legacy!RC33=""Checked"",""X"","""")"
.Offset(0, 8).FormulaR1C1 =
"=IF(Legacy!RC34=""Checked"",""X"","""")"
.Resize(, 9).AutoFill Destination:=Range("A2:I1499"),
Type:=xlFillDefault
End With

Set rng2 = Application.InputBox("Select second cell", Type:=8)

With rng2

.FormulaR1C1 = "=Hierarchy!R[-1498]C2"
.Offset(0, 1).FormulaR1C1 = "=Hierarchy!R[-1498]C5"
.Offset(0, 2).FormulaR1C1 = "=Hierarchy!R[-1498]C3"
.Offset(0, 3).FormulaR1C1 = "=LEFT(Hierarchy!R[-1498]C9,30)"
.Offset(0, 4).FormulaR1C1 = _
"=IF(Hierarchy!R[-1498]C19=""Checked"",""X"",IF(Hierarchy!R[-1498]C19=""Indeterminate"",""I"",""""))"
.Offset(0, 5).FormulaR1C1 = _
"=IF(Hierarchy!R[-1498]C20=""Checked"",""X"",IF(Hierarchy!R[-1498]C20=""Indeterminate"",""I"",""""))"
.Offset(0, 6).FormulaR1C1 =
"=Hierarchy!R[-1498]C22*Hierarchy!R[-1498]C25"
.Offset(0, 7).FormulaR1C1 = _
"=IF(Hierarchy!R[-1498]C33=""Checked"",""X"","""")"
.Offset(0, 8).FormulaR1C1 = _
"=IF(Hierarchy!R[-1498]C34=""Checked"",""X"","""")"
.Resize(, 8).AutoFill Destination:=Range("A1500:I3000"),
Type:=xlFillDefault
End With

--
__________________________________
HTH

Bob

RedFive said:
Here is one of the formulas in the worksheet:
=IF('[Legacy]11103'!$S2="Checked","X",IF('[Legacy]11103'!$S2="Indeterminate","I",""))

Below is the recorded macro.
Range("A2").Select
ActiveCell.FormulaR1C1 = "=Legacy!RC2"
Range("B2").Select
ActiveCell.FormulaR1C1 = "=Legacy!RC5"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=Legacy!RC3"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=LEFT(Legacy!RC9,30)"
Range("E2").Select
ActiveCell.FormulaR1C1 = _

"=IF(Legacy!RC19=""Checked"",""X"",IF(Legacy!RC19=""Indeterminate"",""I"",""""))"
Range("F2").Select
ActiveCell.FormulaR1C1 = _

"=IF(Legacy!RC20=""Checked"",""X"",IF(Legacy!RC20=""Indeterminate"",""I"",""""))"
Range("G2").Select
ActiveCell.FormulaR1C1 = "=Legacy!RC22"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=IF(Legacy!RC33=""Checked"",""X"","""")"
Range("I2").Select
ActiveCell.FormulaR1C1 = "=IF(Legacy!RC34=""Checked"",""X"","""")"
Range("A2:I2").Select
Selection.AutoFill Destination:=Range("A2:I1499"), Type:=xlFillDefault
Range("A2:I1499").Select

Range("A1500").Select
ActiveCell.FormulaR1C1 = "=Hierarchy!R[-1498]C2"
Range("B1500").Select
ActiveCell.FormulaR1C1 = "=Hierarchy!R[-1498]C5"
Range("C1500").Select
ActiveCell.FormulaR1C1 = "=Hierarchy!R[-1498]C3"
Range("D1500").Select
ActiveCell.FormulaR1C1 = "=LEFT(Hierarchy!R[-1498]C9,30)"
Range("E1500").Select
ActiveCell.FormulaR1C1 = _

"=IF(Hierarchy!R[-1498]C19=""Checked"",""X"",IF(Hierarchy!R[-1498]C19=""Indeterminate"",""I"",""""))"
Range("F1500").Select
ActiveCell.FormulaR1C1 = _

"=IF(Hierarchy!R[-1498]C20=""Checked"",""X"",IF(Hierarchy!R[-1498]C20=""Indeterminate"",""I"",""""))"
Range("G1500").Select
ActiveCell.FormulaR1C1 = "=Hierarchy!R[-1498]C22*Hierarchy!R[-1498]C25"
Range("H1500").Select
ActiveCell.FormulaR1C1 =
"=IF(Hierarchy!R[-1498]C33=""Checked"",""X"","""")"
Range("I1500").Select
ActiveCell.FormulaR1C1 =
"=IF(Hierarchy!R[-1498]C34=""Checked"",""X"","""")"
Range("A1500:I1500").Select
Selection.AutoFill Destination:=Range("A1500:I3000"),
Type:=xlFillDefault
Range("A1500:I3000").Select
--
RedFive


The Code Cage Team said:
Can you give us your formula and what you would like changing over what
range?


--
The Code Cage Team

Regards,
The Code Cage Team
www.thecodecage.com
------------------------------------------------------------------------
The Code Cage Team's Profile:
http://www.thecodecage.com/forumz/member.php?userid=2
View this thread:
http://www.thecodecage.com/forumz/showthread.php?t=24914
Click to expand...
Click to expand...
 
Top