ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Update Formula Loop (https://www.excelbanter.com/excel-programming/419504-update-formula-loop.html)

RedFive
Update Formula Loop
 
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
--
RedFive

Bob Phillips[_3_]
Update Formula Loop
 
Show us your code so far.

--
__________________________________
HTH

Bob

"RedFive" wrote in message
...
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
--
RedFive



The Code Cage Team[_88_]
Update Formula Loop
 

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/sh...ad.php?t=24914

RedFive
Update Formula Loop
 
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" wrote:


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/sh...ad.php?t=24914

Don Guillett
Update Formula Loop
 
Have you tried this idea
Sub changesourse()
Columns(2).Replace "sheet2", "sheet3"
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"RedFive" wrote in message
...
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
--
RedFive


Bob Phillips[_3_]
Update Formula Loop
 
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" wrote in message
...
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" wrote:


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/sh...ad.php?t=24914


All times are GMT +1. The time now is 07:37 PM.

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