writing macro that lives the formula inside the cell

[asafraz81]

hi there,
i wrote a realy simple macro that multiply cells and lives a formula inside the cell.
same row , different column.
the life was too easy.
but the real problem is that i need the macro to be dinamic.
my meaning is:
the macro needs to identify the number of the column by the column name that inside the first row.
this is my macro:

Sub lives_formula()
Dim lastrow As Integer
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For a = 2 To lastrow
first_co = ("a" & a)
second_co = ("b" & a)
Cells(a, "c").Formula = "=(" & first_co & "*" & second_co & ")"
Next a
End Sub

the first column is sales and the second is price.
i need a dinamic macro that can find the column number/character.
to find the number its easy using match formula.
but, how can i express this to a character.
or that can be another way.

hope this is easy problem for you guys,
thanks,
asaf.

 

[Luke M]

This should do what you need.

Sub lives_formula()
Dim lastRow As Long
Dim firstCol As Long
Dim lastCol As Long

lastRow = Cells(Rows.Count, 1).End(xlUp).Row
'Assumes that values are in some cell. No error check
With Range("1:1")
    firstCol = .Find("sales", , , xlWhole, , xlNext, False).Column
    lastCol = .Find("price", , , xlWhole, , xlNext, False).Column
End With

'Write all the formulas in on shot
Range("C2:C" & lastRow).FormulaR1C1 = _
    "=RC" & firstCol & "*RC" & lastCol

End Sub

 

[asafraz81]

thanks.
i wasnt so far.
i just dont know to write the last formula with the "rc" operator.
and the truth that i am still dont get the idea with this.

its work perfect as i wish.

thank you very much.