Excel sum formula with "*", written in VBA

[Max Peacock]

I know this is really an excel function. But is there a way to get around the wildcards to write the following as vba code?

Please Login or Register  to view this content.

Any help much appreciated.

[arthurbr]

Hi and welcome to the board

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

I will do it for you this time

[TMS]

Well, that looks as though that would be a SUMPRODUCT in Excel 2003, but you couldn't use whole column ranges.

What are you trying to do? Not in a formula, in real life.

Regards

[Max Peacock]

I'm trying to recreate the following excel formula as vba code:

Please Login or Register  to view this content.

I know this can be done with an if formula in vba, but I was really looking for a way round the * wildcard.

The formula looks for all entries in column B with value1, all entries in column C with value2 and inputs the corresponding value (for that row) into the cell with this formula.

I have also seen the above as an array formula, and rather than try to convert the above into an if formula, I wondered if it could still be done with the * (or some vba replacement) satisfying as a "and" statement.

[TMS]

Still not sure what you want to do. And what wild cards do you mean? The asterisks (*) are multiplying together the conditions.

Anyway, see if this helps:

Please Login or Register  to view this content.

Assuming that cell F1 is selected, they're all ways of putting the same formula in it (F1).

The last version could actually be placed in any cell.

Regards

[Max Peacock]

All of these codes put the formula into the cell. I only wanted the resultant value. So how could I do indentical to your answers, but without the apostrophes.

I need to do this for a column over 15000 cells long and was looking for a way to cut down on memory while maintaining the original formula.

[snb]

The asterisk is an operator, not a wildcard in this case.

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[Max Peacock]

That's doing the job alright. Thanks snb.

I know vba recognises applications like SUMIF, why not SUM with operator * ?

i.e. what is wrong with my original code?

[snb]

'Only' this....

Please Login or Register  to view this content.

[Max Peacock]

Ah, but you see I don't want the formulas in the cells just the values.

Am I being to fussy?

[snb]

No, in that case:

Please Login or Register  to view this content.

[Max Peacock]

Perfect.

Thanks snb.

[Max Peacock]

... It didn't like me evaluating ranges did it? (where excel doesn't mind).

[Max Peacock]

1234567890