Macro Help

  • Thread starter Thread starter Kim
  • Start date Start date
K

Kim

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,MarketShare,2,10))+(COUNTIF(Q2:T2,"Y")*5)

Thanks.
Kim
 
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:
 
My original formulas is something like:

strFormula5 =
"=(VLOOKUP(P2,PriceRanking,2,10))+(VLOOKUP(X2,MarketShare,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 ?
 
That's quite another thing:
Range("AA2:AA" & lngRow).Formula = _
"=VLOOKUP(P2,PriceRanking,2,10)+VLOOKUP(X2,MarketShare,2,10)+COUNTIF(Q2:T2,""Y"")*5"

Clear in your example usage of strFormula5 and strFormula52!

Regards,
Stefi

„Kim†ezt írta:
My original formulas is something like:

strFormula5 =
"=(VLOOKUP(P2,PriceRanking,2,10))+(VLOOKUP(X2,MarketShare,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 said:
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:
Click to expand...
Click to expand...
 
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 said:
My original formulas is something like:

strFormula5 =
"=(VLOOKUP(P2,PriceRanking,2,10))+(VLOOKUP(X2,MarketShare,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 said:
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:
Click to expand...
Click to expand...
 
Back
Top