francis
09-03-2008, 09:44 AM
If I want to expand the macro provided to include additonal criteria. For example, if I need to sum with an additional criteria and only meeting these 2 criteria, then a "Y" or "N' be input in column C
Let say, Names in Column A such as ABC 01, ABCD 001, XY 0001 and so on....the names can be varies in length, but we can identify by the group
name, like ABC xxx, XY xxx ...etc
In Column D, I have Code like "US", "CA", "SG"...etc for the corresponding rows and in column B, I have amount for related to each names.
If the total sum of the amount in a particular group and code, eg. all the names under ABC and code under "US", is less than 100, put a "N" in Column C of the corresponding rows, otherwise put a "Y" if the amount is greater than 100.
The macro below put a "N" under Name group if the sum is less than
100 and "Y" if greater than 100. How to adjust the macro to include 2 criterias mentioned.
SUb FlagCriteria()
Dim rCells As Range, rCell As Range, vUni As Variant, c, nCell As String
Set rCells = Range("A2", Range("A" & Rows.Count).End(xlUp))
With CreateObject("scripting.dictionary")
For Each rCell In rCells
nCell = Split(rCell, " ")(0)
If nCell <> "" Then
If Not .Exists(nCell) Then
.Add nCell, ""
c = c + 1
End If
End If
Next rCell
vUni = .keys
End With
Dim oDex As Integer, SumCell As Double
Dim p As Integer, oAd(), Ans As Integer, oRes As String
For oDex = 0 To UBound(vUni)
For Each rCell In rCells
nCell = Split(rCell, " ")(0)
If nCell = vUni(oDex) Then
SumCell = SumCell + rCell.Offset(, 1)
p = p + 1
ReDim Preserve oAd(p)
oAd(p) = rCell.Offset(, 2).Address
End If
Next rCell
If SumCell > 100 Then
oRes = "Y"
Else
oRes = "N"
End If
For Ans = 1 To UBound(oAd())
Range(oAd(Ans)) = oRes
Next Ans
SumCell = 0
p = 0
Next
End Sub
thanks
regards, xlsops
Let say, Names in Column A such as ABC 01, ABCD 001, XY 0001 and so on....the names can be varies in length, but we can identify by the group
name, like ABC xxx, XY xxx ...etc
In Column D, I have Code like "US", "CA", "SG"...etc for the corresponding rows and in column B, I have amount for related to each names.
If the total sum of the amount in a particular group and code, eg. all the names under ABC and code under "US", is less than 100, put a "N" in Column C of the corresponding rows, otherwise put a "Y" if the amount is greater than 100.
The macro below put a "N" under Name group if the sum is less than
100 and "Y" if greater than 100. How to adjust the macro to include 2 criterias mentioned.
SUb FlagCriteria()
Dim rCells As Range, rCell As Range, vUni As Variant, c, nCell As String
Set rCells = Range("A2", Range("A" & Rows.Count).End(xlUp))
With CreateObject("scripting.dictionary")
For Each rCell In rCells
nCell = Split(rCell, " ")(0)
If nCell <> "" Then
If Not .Exists(nCell) Then
.Add nCell, ""
c = c + 1
End If
End If
Next rCell
vUni = .keys
End With
Dim oDex As Integer, SumCell As Double
Dim p As Integer, oAd(), Ans As Integer, oRes As String
For oDex = 0 To UBound(vUni)
For Each rCell In rCells
nCell = Split(rCell, " ")(0)
If nCell = vUni(oDex) Then
SumCell = SumCell + rCell.Offset(, 1)
p = p + 1
ReDim Preserve oAd(p)
oAd(p) = rCell.Offset(, 2).Address
End If
Next rCell
If SumCell > 100 Then
oRes = "Y"
Else
oRes = "N"
End If
For Ans = 1 To UBound(oAd())
Range(oAd(Ans)) = oRes
Next Ans
SumCell = 0
p = 0
Next
End Sub
thanks
regards, xlsops