Operator is in excel cells, how to reference it
You can access worksheet functions from VBA as follows
MyTotal = worksheetfunctions.sum("A1:B10")
what is also nice is that any range in VBA can also be passed to the
worksheet functions
set MyRange = sheets("Sheet5").Range("B11:C12")
MyTotal = worksheetfunctions.sum(MyRange)
The range can also be a name from the worksheets
MyTotal = worksheetfunctions.sum(activesheet.MyRange)
You probably want to use the worksheet function SUMIF.
set MyRange = sheets("Sheet5").Range("A11:A100")
lesstotal = worksheetfunctions. _
sumif(MyRange,"'< 4000",MyRange.offset(rowoffset:=0,columnoffset:=1)
sheets("Summary").Range("B1") = lesstotal
Another method is for VBA to actuall put the formula for sumif into tyour
summary worksheet
lesstotalformula = "=sumif(Sheet5!A11:A100,""<4000"",Sheet5!B11:B100) "
sheets("Summary").Range("B1").formula = lesstotalformula
the above can be made dynamic as follows
LastRow = 100
lesstotalformula = "=sumif(Sheet5!A11:A" & Lastrow & _
",""<4000"",Sheet5!B11:B" & LastRow & ")"
sheets("Summary").Range("B1").formula = lesstotalformula
" wrote:
Hi, All:
What I want to accomplish is that I want to summarize the data in the
sheets "data" under some conditions?
For example I want to know how many customers have balance below 4000
and how many of them have balance above 5000, etc
So I have a set of conditions in column B in "summary" sheet, < 4000,
5000
in column C, another variable =0, =1 etc
...........
In column O, is the sum of customers satisfy my conditions in column B
to P.
So I have B2="'< 4000," B3="5000".......
How can I reference them in VBA, for example, I want to check if
Sheets("data").cells(1,1)0
or not. I want to use B2 directly. How can I do that in VBA
I tried
if Sheets("data").cells(1,1) & Sheets("summary").(2, 2) then
..........
end if
It doesn't work. Any one knows how to handle this? Thanks.
|