Valci Filho
Valci Filho

Reputation: 1

Using SUMIF within VBA

enter image description hereI need a little help with VBA to perform the Sumif function.

I have a file and need to extract from sheet2: Using the normal formulas, the function is thus:enter image description here SUMIF (Sheet2! B: B; Sheet1! A: A; Sheet2! C: C) However, I want them in VBA, can give this small force :)

I've tried a few things, but it always returns zero. Then follow the criteria to get help with new ideas

Upvotes: 0

Views: 138

Answers (1)

user4039065
user4039065

Reputation:

Your worksheet formula is using 'implicit reference' for the Sheet1!A:A criteria. Normally, you would use,

=SUMIF(Sheet2!B:B; Sheet1!A2; Sheet2!C:C)

... in Sheet1!C2 (or any other cell) and fill down for Sheet1!A3, Sheet1!A4, etc. as the criteria. There is no 'implicit reference' because you are explicitly referencing one specific cell in Sheet1's column A. By using,

=SUMIF(Sheet2!B:B; Sheet1!A:A; Sheet2!C:C)

... as a standard formula you are implicitly referencing the cell in column A which is in the same worksheet row as the formula. When the formula is in row 5, Sheet1!A5 is referenced as the criteria; when it is in row 99, Sheet1!A99 becomes the criteria.

The concept of 'implicit reference' (aka 'inferred reference') can be easily demonstrated with this simple exercise.

  1. Put 5, 10, 15, ... 40, 45, 50 into cells A1:A10.
  2. In any unused column to the right, pick a row between 1 and 10 and put in the formula,

    =a:a
    
  3. The result will be the value from column A in whatever row you put the formula into.

Without a cell (in VBA known as Application.Caller) to provide the implicit or inferred row, the formula becomes confused as to what cell in column A to implicitly reference. Using the simplified example provided above, VBA has no row reference to determine how to resolve A:A to a single cell reference. My best guess in that it is trying to reference either Sheet1!A1 or Sheet1!A1048576 as the criteria.

Hope I've explained that concept sufficiently. Comments welcomed and I'll try to respond.

Upvotes: 1

Related Questions