SumIf with column header

[rana8689]

Could some one help me. Below is the table . I need calculate the Margin for the total SO for one Sales rep and for others the margin is calculated for each row.The sumif does not work. Kindly help.
A B C D E
Rep Name Sales Price Margin %MARGIN Order No
Al 750.00 665.47 89% 33102
Al 562.50 487.51 87% 33102
Al 5,100.00 5,025.01 99% 33102
Al 1,375.00 1,237.26 90% 33102
Brett 820.00 714.11 87% 33102
Brett 3,000.00 2,769.70 92% 35219
Brett 500.00 394.70 79% 35221
Brett 250.00 237.50 95% 35285
Brett 234.50 222.78 95% 35285
Brett 937.50 890.63 95% 35285
Brett 275.00 261.25 95% 35285
Brett 945.00 368.24 39% 35285
Brett - - 0% 35285
Brett 210.00 199.50 95% 35285
Keith 4,800.00 4,479.70 93% 34939
Keith 2,000.00 1,831.01 92% 34939


Sub MARGINPER()
Dim SRep As Long, SNo As Long, SMar As Long, MarPer As Long, SPrice As Long
Dim i, LastRow
LastRow = Range("A" & Rows.Count).End(xlUp).Row
With ThisWorkbook
With .ActiveSheet
SRep = .Cells.Find(What:="Rep Name", After:=.Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Column
SNo = .Cells.Find(What:="Order No", After:=.Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Column
SMar = .Cells.Find(What:="Margin", After:=.Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Column

MarPer = .Cells.Find(What:="%MARGIN", After:=.Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Column
SPrice = .Cells.Find(What:="Sales Price", After:=.Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Column


For i = 2 To LastRow

If Cells(i, SRep) = "Keith" Then
Cells(i, MarPer).formula = "=SumIf(" & .Range(.Cells(SNo)) & "," & _
Cells(i & SNo) & "," & .Range(.Cells(SPrice)) & ")/" & _
"SumIf(" & .Range(.Cells(SNo)) & "," & Cells(i, SNo) & "," & _
.Range(.Cells(SMar)) & "))"
ElseIf Cells(i, SPrice) = "0" Then
Cells(i, MarPer) = "0"
Else: Cells(i, MarPer).formula = "=" & Cells(i, SMar) & "/" & Cells(i, SPrice)
End If
Next
End With
End With

End Sub

 

[Andrew Fergus]

Hi

I'm curious why you are using VBA for this. Will a SUMIF formula not do the job?

Andrew