Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
update formula in column when another cell formula is changed | Excel Worksheet Functions | |||
For To / Next loop doesn't update... | Excel Programming | |||
Loop Formula | Excel Programming | |||
update range in For loop | Excel Programming | |||
extract a foldername with a formula-update formula does not work | Excel Programming |