Using Excel SumIf Command in VB6

[joel009]

Hi,
anyone got any tips on using the sumif command? Anywhere I can get help. I keep getting a "type mismatch" error. I created and object for the ranges and set the ranges equal to them and have tried to reference the cells explicitly. Nothing seems to work.

 

[MadForIt]

Hi,

You have to access this via the WorksheetFunction command

Eg.

Result = Application.WorksheetFunction.SumIf(Range("a1:a100&quot, "ABC", Range("b1:b100&quot)

There are many worksheet functions that can be accessed this way.
Rgds.

 

[joel009]

Hey Madforit,
here is a sample of the coed I have been trying.

Dim Range1 As Range
Dim Range2 As Range
Dim Range3 As Range
Dim dblNew As Double


Set Range1 = xlApp.Worksheets("TEAM Summary Report&quot.Range("C2:C6&quot
Set Range2 = xlApp.Worksheets("TEAM Summary Report&quot.Range("E2:E6&quot
Set Range3 = xlApp.Worksheets("TEAM Summary Report&quot.Range("C2&quot

dblNew = xlApp.WorksheetFunction.SumIf(Range1, "Tschirky, Theresa M", Range2)
which give me a type mismatch

I tried this from your note and get the error:
"error 1004"
dblNew = xlApp.WorksheetFunction.SumIf(Range("C2", "C6&quot, "Tschirky, Theresa M", Range("E2", "E6&quot)

 

[MadForIt]

Joel009,

Not sure how you are creating your xlApp object. Assuming Excel is already running this code works on my machine.

Rgds.

Dim xlApp As Object
Set xlApp = GetObject(, "Excel.Application&quot

Dim Range1 As Range
Dim Range2 As Range
Dim Range3 As Range
Dim dblNew As Double

Set Range1 = xlApp.Worksheets("TEAM Summary Report&quot.Range("C2:C6&quot
Set Range2 = xlApp.Worksheets("TEAM Summary Report&quot.Range("E2:E6&quot
Set Range3 = xlApp.Worksheets("TEAM Summary Report&quot.Range("C2&quot

dblNew = xlApp.WorksheetFunction.SumIf(Range1, "Tschirky, Theresa M", Range2)

 

[xlbo]

Bizarre - maybe something to do with it being outside excel - coupla things to try:
Try using Range1.address (and Range2.address) instead
OR
Try using
With xlApp
Set Range1 = .Worksheets("TEAM Summary Report&quot.Range("C2:C6&quot
Set Range2 = .Worksheets("TEAM Summary Report&quot.Range("E2:E6&quot
Set Range3 = .Worksheets("TEAM Summary Report&quot.Range("C2&quot

dblNew = .WorksheetFunction.SumIf(Range1, "Tschirky, Theresa M", Range2)

OR
Try dimming dblNew as LONG

Also, the range defining convention is range("C1:C6&quot not
Range("C2", "C6&quot,

HTH Rgds
~Geoff~

 

[xlbo]

Another thing - place a watch on Range1 and Range2 - let us know what they evaluate to Rgds
~Geoff~

 

[joel009]

Thanks, finally figured it out. The range thing was a problem. Is there any way to refer to a range without using the letter? I've had to convert the column from numeric to lteer reference and set the whole thing to a variable and it would have been easier to just use the numeric value but couldn't make it work.

Joel009

 

[xlbo]

To refer to a range without using a letter, use the Cells syntax

For 1 cell, use

Cells(r,c)

For multiple cells, use

Range(cells(r,c)cells(r,c))

r = row ref
c = column ref(number a=1, B=2 etc etc)
HTH Rgds
~Geoff~

 

[joel009]

Thanks, that works fine and would've save me writing a function to convert the whole thing to a string.

Can you recommend a good book that you find usefull for this type of thing. I got the "MicroSoft Excel 2000 Power Programing with VBA" and it is not very useful for a VBA beginner, refrences are too vague and not cross referenced/indexed well at all. I like the SAMS or QUE books but haven't been able to locate any.

Joel

 

[xlbo]

Sorry - the only one I use is Power Programming for VBA (John Walkenbach) which I personally like. However, that's probably 'cos I didn't get hold of it till I'd been writing code for a coupla years and kinda knew my way around the object model. Basically, I jus' taught myself using the macro recorder, practice, excel discussion lists and a lot of swearing. Personally, I reckon the most important part of programming in excel is learning the object model. Once you know what can do what, life is much easier. Rgds
~Geoff~