I would like to know if it is possible to apply the WorksheetFunction on a range instantly.
For example, say I want to Vlookup with the Index 1 & 3 to get their ID Num
|Index|Name|Sales|ID Num|
|-|-|-|-|-|
|1| A| 2| 123|
|2| B| 3| 321|
|3| A| 5| 456|
|4| A| 1| 654|
I managed to achive it on the vlookup function:
Range("J2:J3")=WorksheetFunction.VLookup(Range("I2:I3"), Range("A:D"), 4, 0)
Where :
- "I2:I3" are the cells storing the criteria 1 & 3
- "A:D" is the column Index, Name, Sales & ID Num
The excel formula version is "=VLOOKUP(I2,A:D,4,0)"
It returned me :
- cell "J2" = 123
- cell "J3" = 456
,which is good but when I use the same methodology on the SUMIF function, it gives me a run-time error Type mismatch
Here is the code : Range("G2:G3") = Application.WorksheetFunction.SumIf(Range("B:B"), Range("F2:F3"), Range("C:C"))
Where :
- "B:B" is the column Name
- "F2:F3" are the cells storing A & B
- "C:C" is the column Sales
The excel formula version is "=SUMIF(B:B;F2;C:C)"
In the formula above, I want to get the total sales for A & B
Have I done something wrong?Or is there an alternative?
Please let me know
Thanks in advance