PDA

View Full Version : Solved: Creating Formulas for Dynamic Ranges using VBA



Slicemahn
12-28-2007, 05:45 AM
Hello Everyone,

I am looking to create some formulas in the columnar format that an Excel pivot table failed to do. My problem is that this data is going to change daily so I can't add formulas and copy down because :

1) the data sets are 32K rows and 5 tabs
2) Time

So I would appreciate any help on this one. I would need to calculate the % of Cable Total and then all sum the total calls. I have attached the spreadsheet with the before and after.

Many thanks for your thoughts and suggestions

Slice

Bob Phillips
12-28-2007, 06:10 AM
Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim cell As Range
Dim FirstAddress As String
With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("D2").Resize(LastRow - 1).Formula = "=IF(ISNUMBER(SEARCH(""Total"",A2)),"""",C2/SUMIF(A:A,A2,C:C))"
Set cell = .Columns(1).Find("Total", LookIn:=xlValues, LookAT:=xlPart)
i = 2
If Not cell Is Nothing Then
FirstAddress = cell.Address
Do
If cell.Row < LastRow Then
cell.Offset(0, 6).Formula = "=SUM(G" & i & ":G" & cell.Row - 1 & ")"
End If
i = cell.Row + 1
Set cell = .Columns(1).FindNext(cell)
Loop While Not cell Is Nothing And cell.Address <> FirstAddress
End If
End With

End Sub

Slicemahn
12-29-2007, 11:37 AM
Xld,

Once again you have saved the day! Many thanks for this.