Trouble with a Index Match formula in VBA

[Stephenosn]

Hello all,

I'm Trying to insert a line of code into a sheet's cell when a command button is pressed. I have three text boxes;tbMaterial, tbQty and tbCost. There a some materials that we use regularly so I already have their price listed (Ref_Cost). Other materials prices need to be entered manually. My intentions with the following line of code is to send the data from all three textboxes to my sheet if they are all used. If a price is not entered into tbCost I want to pass the following formula into the cell

=IFERROR(INDEX(Ref_UnitCost,MATCH(J2,Ref_Appliance,0))*AJ2,"")

Bellow is my attempt at the code.

If c.Offset(i - 1, 10) <> "" Then     c.Offset(i - 1, 36) = CCur(CDbl(c.Offset(i - 1, 10)) * c.Offset(i - 1, 35))
     If c.Offset(i - 1, 10) = "" Then
          c.Offset(-1, 36).FormulaR1C1 = "=INDEX(Ref_UnitCost,MATCH(1,(RC[-27]=Ref_Materials,Ref_Appliance,0)"
     End If 

End If

When the command button is pressed nothing is passed to the cell when the nested if statement is true.

Any idea what I've done wrong in my code?

Thank you for having a look,

John

 

[Stephenosn]

Hello all,

The following code solved my issue.

If c.Offset(i - 1, 10) <> "" Then                c.Offset(i - 1, 36) = CCur(CDbl(c.Offset(i - 1, 10)) * c.Offset(i - 1, 35))
            Else
                c.Offset(i - 1, 36).FormulaR1C1 = "=IFERROR(INDEX(Ref_UnitCost,MATCH(RC[-27],Ref_Appliance,0))*RC[-1],"""")"
            End If

Thanks for looking,

John