• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

help with excell application

A

asafraz81

Member
hi there

i have a little problem with sumif application/

when i run the macro below i get an eror that worksheetfunction does not work with sumif. i checked in vba help called worksheetfunction members and sum if was there. i dont understnd why its not working. please help

Sub sumif1()

Dim sumif1 As Integer

Set privaterange = Worksheets("âéìéåï1").Range("a1:b10")

sumif1 = Application.WorksheetFunction.sumif(privaterange, "x", "b1:b10")

MsgBox (sumif1)

End Sub
 
Asafraz81


You weren't far off:

[pre]
Code: 
Sub sumif1()
Dim sumif1 As Integer
Set PrivateRange = Worksheets("Sheet1").Range("a1:b10")
sumif1 = Application.WorksheetFunction.SumIf(PrivateRange, "x", Range("b1:b10"))
MsgBox (sumif1)
End Sub
[/pre]
 
i need the last help about this code/

i want that the range will be dinamic

instead a1:b10 make the b10=last row

how i define it?

thanks for the help with the code it working very good
 
Try:

[pre]
Code: 
Sub mySumif()
Dim sumif1 As Integer
Dim LookFor as Variant

Lookfor = "x"

Worksheets("Sheet1").Range("A1:B1").Select
Set DataRange = Range(Selection, Selection.End(xlDown))

Worksheets("Sheet1").Range("B1").Select
Set SumRange = Range(Selection, Selection.End(xlDown))

sumif1 = Application.WorksheetFunction.SumIf(DataRange, Lookfor, SumRange)
MsgBox (sumif1)

End Sub
[/pre]
 
Hi asafraz,


Your criteria range A1:Bx is two columns but your sum range B1:Bx is one column. This is going to lead to problems. What will actually happen under the hood is your sum range will be extended to B1:Cx internally within the calculation. (Also, I assume the sum range is on a different worksheet otherwise it overlaps with your criteria range?)


Can you describe in words what you want the conditional sum to be and we might be able to offer something more robust? In the meantime, these two articles explain what I've written above and give some examples of using SUMIF() from VBA:


SUMIF Formulas

SUMIF From VBA
 
Asafraz81


Colin is suggesting the code should be:

[pre]
Code: 
Sub mySumif()
Dim sumif1 As Integer
Dim LookFor as Variant

Lookfor = "x"

Worksheets("Sheet1").Range("A1").Select
Set DataRange = Range(Selection, Selection.End(xlDown))

Worksheets("Sheet1").Range("B1").Select
Set SumRange = Range(Selection, Selection.End(xlDown))

sumif1 = Application.WorksheetFunction.SumIf(DataRange, Lookfor, SumRange)
MsgBox (sumif1)

End Sub
[/pre]

Which is technically correct, although it didn't seem to affect the answers in your case


You will also need to ensure that Column A and B have data in the same range or else you'll probably get errors

That is If Column A has data in Rows 1:20, Column B must also have data in Rows 1:20
 
hui thanks for helping me.

but the code isnt working it giving me eror after set myrange that "expected function or variable".

i have a simple code like:

sub sumrange

dim sum as integer

set myrange=Worksheets("Sheet1").Range("a1:b5")

for each cell in rang(myrange))

sum=appliction.worksheetfunction.sum(myrange)

msgbox myrange

end sub


all i want that my code will be dinamic, how can i replace the"b5" with definition that looking for the last row in column b???

thanks :)
 
Asafraz81


Instead of asking us a different question to the initial question,

How about posting a sample file and telling us what your trying to achieve?


You post a sample file, Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Hi ,


At present , your code is generating a simple spelling error in this statement :


for each cell in rang(myrange))


Since you have mis-spelled Range as rang , Excel is taking rang as a function , and since it does not find a function named rang , you are getting this particular error.


To correct this error , just eliminate the rang , since you have defined myrange as a range ; your statement can be :


for each cell in myrange


Once you correct this error , you can start checking the procedure to see whether it does what you want it to do.


There are other mistakes too , such as appliction instead of application. So also :


Msgbox myrange


You can have :


Msgbox myrange.address


Try this ; at least it will execute :

[pre]
Code: 
Sub sumrange()
Dim sum As Integer
Set myrange = Worksheets("Sheet1").Range("a1:b5")
For Each cell In myrange
sum = Application.WorksheetFunction.sum(myrange)
Next
MsgBox sum
End Sub
[/pre]
Narayan
 
A good habit to start practicing is to put an Option Explicit statement at the top of your code modules. When you try to run (compile) your code it will tell you if there are any typos in your code. It will also tell you if you have any undeclared variables (for example, in this case, "myrange").
 
hui hi,

when i run your code in my data/

i get an eror 400.

i dont understand why its happening.

i cant upload the file because it confidential.

i change the integer into long its teel making me troubles.

please help
 
Hi asafraz81,


More information would help us to help you:


Q1. 400 is the error number, but what is the error message?

Q2. Which line of code does the error occur on?

Q3. Please post the current version of the code you are using, including any changes you have made?
 
thanks for helping me'

the code is:

Sub mySumif()

Dim sumif1 As Long

Dim LookFor As Variant


LookFor = "מ.אירוח"


Worksheets("רשתות").Range("A4").Select

Set datarange = Range(Selection, Selection.End(xlDown))


Worksheets("רשתות").Range("x4").Select

Set sumrange = Range(Selection, Selection.End(xlDown))


sumif1 = Application.WorksheetFunction.SumIf(datarange, LookFor, sumrange)

MsgBox (sumif1)


End Sub
 
Try the below code. If you still get an error then please answer Q1 and Q2 from my last post.

[pre]
Code: 
Sub mySumif()

Const LookFor As String =  "מ.אירוח"

Dim sumIf1 As Double
Dim DataRange As Range
Dim sumRange As Range

With ThisWorkbook.Worksheets("רשתות")
Set DataRange = .Range("A4", .Range("A4").End(xlDown))
Set sumRange = .Range("X4", .Range("X4").End(xlDown))
End With

sumIf1 = Application.WorksheetFunction.SumIf(DataRange, LookFor, sumRange)
MsgBox CStr(sumIf1)

End Sub
[/pre]
 
They look the right way round to me. As a test you could do the SUMIF() formula manually in a cell to see if you get the same result as the VBA code. I assume that the code change fixed your runtime error then?
 
You must log in or register to reply here.
Back
Top