how to add formula to this macro???
This macro was given to me by TOM and it works great,
however, i will like to have another total on the bottom to add SICK, VACATION AND SUBTRACT LUNCH..I am able to add a cell for total for each totals. however, i am unable to edit the formula section for it to add sick and vacation and subtract lunch. I will appreciate any help. Sub ProcessData() Dim cnt As Long, cnt1 As Long Dim c As Range Dim firstAddress As String Dim rngStart As Range With Worksheets(1).Columns(1) Set rngStart = .Cells(1, 1) Set c = .Find("Total", _ After:=Worksheets(1).Cells(Rows.Count, 1), _ Lookat:=xlPart, LookIn:=xlValues, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False) If Not c Is Nothing Then cnt = 1 firstAddress = c.Address Do If cnt Mod 5 = 0 Then cnt1 = Application.Round(cnt / 5, 0) c.Offset(1, 0).Resize(5).EntireRow.Insert c.Value = "Total" & cnt1 c.Offset(1, 0).Value = "Vacation" & cnt1 c.Offset(2, 0).Value = "Sick" & cnt1 Set rng1 = Worksheets(1).Range(rngStart, c.Offset(-1, 0)) c.Offset(1, 1).Formula = "=Sumif(" & rng1.Offset(0, 2).Address & _ ",""Vacation""," & rng1.Offset(0, 4).Address & ")" c.Offset(1, 1).BorderAround Weight:=xlMedium c.Offset(2, 1).Formula = "=Sumif(" & rng1.Offset(0, 2).Address & _ ",""Sick""," & rng1.Offset(0, 4).Address & ")" c.Offset(2, 1).BorderAround Weight:=xlMedium Set rngStart = c.Offset(1, 0) End If Set c = .FindNext(c) cnt = cnt + 1 Loop While Not c Is Nothing And c.Address < firstAddress End If End With End Sub |
All times are GMT +1. The time now is 01:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com