Use FormulaR1C1 to populate formula if cell contains specified value

[CD2017]

Hello,

I need help with inserting a multiplication formula that is using two cell values in the formula IF another cell contains a specific value.

If cell G1 = "1", then I want to insert a formula in cell H1 as follows G1*D1, and so on throughout the table of data.

The table of data will change so I don't want to hardcode the cell references in the formula.

Below is the code I have so far, but I'm missing a step. The code currently sees the value "1" in cells G8, G9, G10 and is populating the formula into every cell in Column H within the workbook. This is not what I want.
I want the formula to populate in cells H8, G9, H10 since the value is "1" in the corresponding G column.

Any help would be greatly appreciated!

Thanks

Sub InsertFormula()
Dim cell As Range

For Each cell In Sheets("InvestmentRevalPS").Range("G:G")

If cell.Value = "1" Then
Sheets("InvestmentRevalPS").Range("H:H").FormulaR1C1 = "=R[-0]C[-1]*R[-0]C[-4]"

End If

Next

End Sub

 

[RCBricker]

Hello,

I need help with inserting a multiplication formula that is using two cell values in the formula IF another cell contains a specific value.

If cell G1 = "1", then I want to insert a formula in cell H1 as follows G1*D1, and so on throughout the table of data.

The table of data will change so I don't want to hardcode the cell references in the formula.

Below is the code I have so far, but I'm missing a step. The code currently sees the value "1" in cells G8, G9, G10 and is populating the formula into every cell in Column H within the workbook. This is not what I want.
I want the formula to populate in cells H8, G9, H10 since the value is "1" in the corresponding G column.

Any help would be greatly appreciated!

Thanks

Sub InsertFormula()
Dim cell As Range

For Each cell In Sheets("InvestmentRevalPS").Range("G:G")

If cell.Value = "1" Then
Sheets("InvestmentRevalPS").Range("H:H").FormulaR1C1 = "=R[-0]C[-1]*R[-0]C[-4]"

End If

Next

End Sub

this will fix the formula in all cells issue

Sub InsertFormula()
 Dim cell As Range

 For Each cell In Sheets("InvestmentRevalPS").Range("G:G")

 If cell.Value = "1" Then
 Sheets("InvestmentRevalPS").Range("H" & cell.row).FormulaR1C1 = "=R[-0]C[-1]*R[-0]C[-4]"

 End If

 Next

 End Sub

 

[CD2017]

RCBricker thank you, works well!