Solved: Creating Formulas for Dynamic Ranges using VBA

[Slicemahn]

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]

[vba]

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
[/vba]

[Slicemahn]

Xld,

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