PDA

View Full Version : Solved: Replace Cell with Formula - automatically



parttime_guy
12-16-2009, 02:44 AM
When I run this sub it uses the formula in last line and paste's it throughout the sheet.


Sub Replacecellwithformula()

Range("G2:G25" & Columns("F").Find("A").Row).Formula = "=A2+D2"
Range("G2:G25" & Columns("F").Find("B").Row).Formula = "=A2-B2"

End Sub


I am trying to get the results in the sheet attached.

Kindly help.........:think:

Best regards

xld
12-16-2009, 03:40 AM
Sub Replacecellwithformula()

Range("G2:G25").Formula = "=IF(F2=""A"",A2+D2,IF(F2=""B"",A2-B2,""""))"

End Sub

parttime_guy
12-16-2009, 04:23 AM
Dear XLD,

Thx for the reply, but I have about 50+ combination formulas which I want replace (automatically) based on the abrevation in Column F ("Formula Rng").

Is there a way to go around this.

Kindly guide....

Thx-n-BR

Aussiebear
12-16-2009, 05:07 AM
Can you go with Case Select?

xld
12-16-2009, 05:36 AM
Sub Replacecellwithformula()
Dim LastRow As Long
Dim i As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("G2").Resize(LastRow - 1).Value = ""

.Columns("A:G").AutoFilter field:=6, Criteria1:="A"
.Range("G2").Resize(LastRow - 1).SpecialCells(xlCellTypeVisible).Formula = "=A2+D2"

.Columns("A:G").AutoFilter field:=6, Criteria1:="B"
.Range("G2").Resize(LastRow - 1).SpecialCells(xlCellTypeVisible).Formula = "=A2-B2"

'etc

.Columns("A:G").AutoFilter field:=6
End With
End Sub

parttime_guy
12-16-2009, 06:00 AM
Hi XLD,

I tried ur sub - small problem

When the sub run for the second criteria (eg, B) - it start again from cell A2.

Eg, the B criteria starts from row 4 - but when the formula runs against the first row.

Attached is the file for your review

Kindly guide ...........

Thanks

xld
12-16-2009, 06:34 AM
LOL! What a prat.



Sub Replacecellwithformula()
Dim LastRow As Long
Dim i As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("G2").Resize(LastRow - 1).Value = ""

.Columns("A:G").AutoFilter field:=6, Criteria1:="A"
.Range("G2").Resize(LastRow - 1).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=RC[-6]+RC[-3]"

.Columns("A:G").AutoFilter field:=6, Criteria1:="B"
.Range("G2").Resize(LastRow - 1).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=RC[-6]-RC[-5]"

'etc

.Columns("A:G").AutoFilter field:=6
End With
End Sub

parttime_guy
12-16-2009, 06:41 AM
Hey XLD - as always - UR the star! thanks for your help :bow: