Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.3k views
in Technique[技术] by (71.8m points)

excel - VBA code for SUMIFS?

I'm trying to write a custom function that will let me retrieve a cell from the first row in a range that meets x number of criteria. I imagine this would be very similar to the way SUMIFS works, just simpler in that it doesn't continue processing after the first match.

Does anyone know code to reproduce the SUMIFS (excel 07) function in VBA?

So, for example, if I have a table in excel like:

W X Y Z
a b 6 1
a b 7 2
b b 7 3

I want to be able to write a function that will give me the value in column Z where columns W=a, X=b, Y>=7 (in other words the value 2).

SUMIFS can approximately do this, assuming the record I want is unique and I'm looking to return a number. For my purposes though, those assumptions won't work.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

An example using ADO.

strFile = Workbooks(1).FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

'I want to be able to write a function that will give me the value '
'in column Z where columns W=a, X=b, Y>=7 '
'(in other words the value 2).' 

strSQL = "SELECT Top 1 Z " _
         & "FROM [Sheet1$] " _
         & "WHERE W='a' And X='b' And Y>=7"

rs.Open strSQL, cn

Result = rs.Fields("Z")

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...