|
Macro Help
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 |
Macro Help
Application.WorksheetFunction.
goes in front of VLookup to use it in VBA "Kim" wrote: 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 |
Macro Help
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 |
Macro Help
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 |
Macro Help
That's quite another thing:
Range("AA2:AA" & lngRow).Formula = _ "=VLOOKUP(P2,PriceRanking,2,10)+VLOOKUP(X2,MarketS hare,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,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 |
Macro Help
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 |
| All times are GMT +1. The time now is 05:45 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com