Solved: Replace Cell with Formula - automatically

[parttime_guy]

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.........

Best regards

[Bob Phillips]

[vba]

Sub Replacecellwithformula()

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

End Sub
[/vba]

[parttime_guy]

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]

Can you go with Case Select?

[Bob Phillips]

[vba]

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
[/vba]

[parttime_guy]

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

[Bob Phillips]

LOL! What a prat.

[vba]

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
[/vba]

[parttime_guy]

Hey XLD - as always - UR the star! thanks for your help