VBA Formula w/ Relative Reference

Asked Dec 21 '16 at 19:28

Active Dec 21 '16 at 19:52

Viewed 159 times

I'm running into an issue using the following VBA formula. The code was working until I changed

IFERROR(IF(VLOOKUP(RC1,ListPrice,11,FALSE)=0,VLOOKUP(RC1,ListPrice,12,FALSE),VLOOKUP(RC1,ListPrice,11,FALSE)),0)*RC6

iferror(IF(RC11="",RC12,RC11),0)*(RC6/IF(RC8="",1,RC8)

Thanks for your help!

Complete VBA:

Sub STDCOST()
Dim LastRow As Long
Sheets("EXPIRE").Select
Range("n1").Value = "Ext_Cost"
Range("n2").FormulaR1C1 = "=IFERROR(IF(RC11="",RC12,RC11),0)*(RC6/IF(RC8="",1,RC8)"


Range("I1").Select

With ActiveSheet
    LastRow = .Cells(.Rows.Count, "I").End(xlUp).Row
End With
Range("n2").AutoFill Destination:=Range("n2:n" & LastRow)
Columns("n:n").Select
Range("n:n").Style = "Currency"
End Sub

edited Jul 09 '18 at 19:34

Community

asked Dec 21 '16 at 19:28

ERKSMTY

3

brackets mismatch? You have 4 left brackets and 3 right brackets so expression is not valid. – S Meaden Dec 21 '16 at 19:31
You are missing a `)` at the end. – Scott Craner Dec 21 '16 at 19:31
3

`Range("n2").FormulaR1C1 = "=IFERROR(IF(RC11="",RC12,RC11),0)*(RC6/IF(RC8="",1,RC8)"` needs to be `Range("n2").FormulaR1C1 = "=IFERROR(IF(RC11="""",RC12,RC11),0)*(RC6/IF(RC8="""",1,RC8))"` (i.e. missing bracket and double double-quotation marks) – YowE3K Dec 21 '16 at 19:35
Thanks everyone! @YowE3K, how come double-double quotations are necessary here? – ERKSMTY Dec 21 '16 at 19:57
2

@andrew green: on double quotes http://stackoverflow.com/questions/15770599/about-using-double-quotes-in-vbscript – S Meaden Dec 21 '16 at 20:05
@SMeaden Thank you!! – ERKSMTY Dec 21 '16 at 20:06

VBA Formula w/ Relative Reference

0 Answers0