Kim, few points

--Last argument within VLOOKUP() should be either 0 or 1...You have
mentioned that as 10.
--You are assigning formula to a variable strFormula5 but to the cells you
are assigning the variable strFormula2
--Double quotes wihtin the formula should be double-double quotes as VBA
considers double-double quote as a single quote.
--Make sure lngRow is always greater than 2

Try the below

strFormula5 = "=VLOOKUP(P2,PriceRanking,2,0)+" & _
"VLOOKUP(X2,MarketShare,2,0)+(COUNTIF(Q2:T2,""Y"") *5)"
Range("AA2:AA" & lngRow).Formula = strFormula5

Range("AA2:AA" & lngRow).NumberFormat = "#,##0_ ;[Red]-#,##0 "

If this post helps click Yes
---------------
Jacob Skaria


"Kim" wrote:

My original formulas is something like:

strFormula5 =
"=(VLOOKUP(P2,PriceRanking,2,10))+(VLOOKUP(X2,Mark etShare,2,10))+(COUNTIF(Q2:T2,"Y")*5)"
Range("AA2:AA" & lngRow).Formula = strFormula2
Range("AA2:AA" & lngRow).NumberFormat = "#,##0_ ;[Red]-#,##0 "

So how should I change them ?

"Stefi" wrote:

Maybe you mean this:

x = WorksheetFunction.VLookup(Range("P2"), Range("PriceRanking"), 2, 10) + _
WorksheetFunction.VLookup(Range("X2"), Range("MarketShare"), 2, 10) + _
WorksheetFunction.CountIf(Range("Q2:T2"), "Y") * 5
Regards,
Stefi

€žKim€� ezt Ã*rta:

I have this formulas in Excel but I guess it doesn't work the same in macro.
Can someone pls help

=(VLOOKUP(P2,PriceRanking,2,10))+(VLOOKUP(X2,Marke tShare,2,10))+(COUNTIF(Q2:T2,"Y")*5)

Thanks.
Kim