Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMIF in VBA
Found my own answer. Sub SumIfer() Dim EnderA As String Dim EnderC As String Dim EnderD As String Dim EnderE As Variant Dim Acta As String Range("A6").Select Selection.Sort Key1:=Range("A6"), Order1:=xlAscending Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Range("A7").Select EnderA = Range(Selection, Selection.End(xlDown)).Address Debug.Print EnderA EnderC = Range(Selection, Selection.End(xlDown)).Offset(0 2).Address Debug.Print EnderC EnderD = Range(Selection, Selection.End(xlDown)).Offset(0 3).Address Debug.Print EnderD EnderE = Range(Selection, Selection.End(xlDown)).Offset(0 4).Address Debug.Print EnderE Range(EnderE).Formula = _ "=IF(A7=A6,"""",SUMIF((" & EnderA & "),A7,(" & EnderC & "))" "-SUMIF((" & EnderA & "),A7,(" & EnderD & ")))" Range("A7").Select Range(Selection, Selection.End(xlDown)).Offset(0, 4).Copy Range(Selection, Selection.End(xlDown)).Offset(0, 4).PasteSpecia xlPasteValues With Selection .HorizontalAlignment = xlRight .Style = "Comma" .Font.ColorIndex = 5 .NumberFormat = "#,##0.00_);[Red](#,##0.00)" End With Columns("C:D").Select Selection.Style = "Comma" Range("A1").Select Application.CutCopyMode = Fals -- LeeBoWad ----------------------------------------------------------------------- LeeBoWadd's Profile: http://www.excelforum.com/member.php...fo&userid=1655 View this thread: http://www.excelforum.com/showthread.php?threadid=31409 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif for multi conditions. i.e sumif(A1:A10,"Jon" and B1:B10,"A" | Excel Worksheet Functions | |||
SUMIF help! | Excel Worksheet Functions | |||
Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |