SUMIF in VBA

bhanu2003

Board Regular
Joined
Dec 9, 2003
Messages
76
Hi,

tried searching to see if i could avoid the post but, sorry could not find, so posting.

i want a variable to have the value of a sumif condition.

That is, say

SUMACT = SUMIF(E28:I28,"",E27:I27)

Thanks,
Bhanu :rolleyes:
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Hi Juan,

Thanks,

the same formula works in the sheet but not in VBA and always returns a '0'.

Any help.

Thanks,
Bhanu
 
Upvote 0
Juan’s macro snippet works for me:
Code:
Sub Macro1()

Dim Sumact As Range
Set Sumact = Sheets(1).Range("A1")

Sumact = Application.SumIf(Range("E28:I28"), "", Range("E27:I27"))

End Sub
Alternative:

Code:
Sub Macro2()
Dim Sumact As Range
Set Sumact = Sheets(1).Range("A1")

With Sumact
  .Formula = "=SUMIF(E28:I28,"""",E27:I27)"
  .Value = .Value
End With

End Sub
Note:
See the second macro. When you enter a worksheet function directly into VBA, you need a double "" to denote a blank.

Regards,

Mike
 
Upvote 0
Hi,

Can any one please let me know if i am doing something wrong here.

i donot want a cell to have the value as i would be closing without changes rather i want a code variable to directly "get" the value afte doing a SUMIF.

or is your way to do it, the only possible one.

Thanks,
Bhanu :rolleyes:
 
Upvote 0
Not sure what you mean when you say that you want a code variable to directly "get" the value after doing a SUMIF.

A message box perhaps?
Code:
Sub Macro3()
Dim Sumact As Long

Sumact = Application.SumIf(Range("E28:I28"), "", Range("E27:I27"))

MsgBox Sumact
' or do something else with the Sumact value?

End Sub

HTH

Mike
 
Upvote 0
Sorry for the confusion, i could not get my HTML maker to ork earlier, now here is what i want to do, i know it is simple but i am in a tangle some where, Thank you for your patience.
Forecast Sheet V1.9 - 2-23-04.xls
EFGHIJ
250
260
27002019367280
28657680221
290
30
31
Forecast - Hours


i need,

Sumact = 260 because it is the sum of cells E27:I27 where E28:I28 has no value.

Thanks,
Bhanu
 
Upvote 0
The below does not work?

Dim Sumact as Double
' do stuff
Sumact = Application.SumIf(Range("E28:I28"), "", Range("E27:I27"))
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top