Formulas that can find headers and be updated

[justvba]

Hi,

I need some help!! I am trying to put formulas in a few columns. Here's where the problem comes in, I am using variables to look for the column with the headers names.

Cells(5,dis).select
        Price= Application.Match("List Price", .Rows(1), 0)
        Cost= Application.Match("Cost Price", .Rows(1), 0)
        sale= Application.Match("SalePrice", .Rows(1), 0)
        dis= Application.Match("Discount", .Rows(1), 0)
        Item= Application.Match("Item", .Rows(1), 0)

 With ActiveSheet
        LastRow = .Cells(.Rows.Count, "Item").End(xlUp).Row
    End With


Activecell.formulaR1C1= "=(RC[-4]-RC[-3])/rc[-4]"
SELECTION.COPY

    Do until Activecell.row > lastrow
        activecell.offset(1,0).select
        activecell.paste
    loop
application.cutcopymode.false
       
    End Sub

So when the user adds columns everything gets messed up with the formula so then I tried to change this line

Activecell.formulaR1C1= "=(RC[-4]-RC[-3])/rc[-4]"
changed to
Activecell.formulaR1C1= "=((" & Price & " )-(" & Sale& " ))/(" & Price & " )"
but only gives me the number that the column is not the value in the cell

Can anyone help!!!

 

[steve the fish]

Try this:

With ActiveSheet
    Price = Application.Match("List Price", .Rows(1), 0)
    Cost = Application.Match("Cost Price", .Rows(1), 0)
    Sale = Application.Match("Sale Price", .Rows(1), 0)
    Dis = Application.Match("Discount", .Rows(1), 0)
    Item = Application.Match("Item", .Rows(1), 0)
    lr = .Cells(.Rows.Count, Price).End(xlUp).Row
    If IsNumeric(Dis) And IsNumeric(Price) And IsNumeric(Sale) and lr > 4 Then
        With Range(.Cells(5, Dis), .Cells(lr, Dis))
            .FormulaR1C1 = "=IF(RC" & Price & "<>"""",(RC" & Price & "-RC" & Sale & ")/RC" & Price & ","""")"
        End With
    End If
End With

 

[justvba]

I am getting an error on the formula Application defined or object defined error Found it sorry how do remove the $ on the column?