LinkND
07-08-2008, 07:43 AM
I don't know if it's possible, but maybe someone knows how to do it.
I made a program to filter unique companies (column A) from another sheet and count the total ammount (column B) of each of them. In column C I am calculating the % of the total.
The code below is only a part of the code I already have. What I want to try is finding all the companies with a total of 4% or below and put them together into a new cell called 'leftovers'. Merging the small ones will be more efficient if I want to create a chart later on for my report.
If I am just calculating them with a formula, it's easy to find the total ammount of all the companies with a smaller value than my constant of 4%: =SUMIF(C3:C11;"<0,04"), with C3:C11 as my range. I made a dummy file with test data to give you an impression of my own file/sheet.
Sub ProcessCompanies()
Dim LastRow As Long
Dim MyRng As Range
Dim yCell As Range
Application.ScreenUpdating = False
With Sheets("CompanyTotals")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set MyRng = Range(Cells(1, "B"), Cells(LastRow, "B"))
For Each yCell In MyRng
yCell.Select
Selection.FormulaArray = "=COUNTIF(CompanyNames!C[33],RC[-1])"
Next yCell
Set MyRng = Range(Cells(1, "C"), Cells(LastRow, "C"))
For Each yCell In MyRng
yCell.Select
Selection.FormulaArray = "=RC[-1]/(SUM(C[-1]))"
Next yCell
End With
Application.ScreenUpdating = True
End Sub
I made a program to filter unique companies (column A) from another sheet and count the total ammount (column B) of each of them. In column C I am calculating the % of the total.
The code below is only a part of the code I already have. What I want to try is finding all the companies with a total of 4% or below and put them together into a new cell called 'leftovers'. Merging the small ones will be more efficient if I want to create a chart later on for my report.
If I am just calculating them with a formula, it's easy to find the total ammount of all the companies with a smaller value than my constant of 4%: =SUMIF(C3:C11;"<0,04"), with C3:C11 as my range. I made a dummy file with test data to give you an impression of my own file/sheet.
Sub ProcessCompanies()
Dim LastRow As Long
Dim MyRng As Range
Dim yCell As Range
Application.ScreenUpdating = False
With Sheets("CompanyTotals")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set MyRng = Range(Cells(1, "B"), Cells(LastRow, "B"))
For Each yCell In MyRng
yCell.Select
Selection.FormulaArray = "=COUNTIF(CompanyNames!C[33],RC[-1])"
Next yCell
Set MyRng = Range(Cells(1, "C"), Cells(LastRow, "C"))
For Each yCell In MyRng
yCell.Select
Selection.FormulaArray = "=RC[-1]/(SUM(C[-1]))"
Next yCell
End With
Application.ScreenUpdating = True
End Sub