• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

writing macro that lives the formula inside the cell

asafraz81

Member
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:
Code:
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.
 

Attachments

  • fix_macro.xlsm
    25.1 KB · Views: 4
Last edited by a moderator:
This should do what you need.
Code:
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
 
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.
 
Back
Top