karthirm90
New Member
- Joined
- Aug 8, 2022
- Messages
- 8
- Office Version
- 365
- Platform
- Windows
I am trying to do macro automation for the following 3 tasks and I'm stuck in all 3 tasks, kindly request to help.
Fill the blank cells with a default value of 15 days as all these tickets are older than 2 weeks and ageing calculation couldn't be done due to incorrect date format issue.
Refresh pivot tables
- Calculate ageing of tickets using assigned date of ticket
- Fill blank cells for which ageing calculation wasn't done due to an incorrect date format issue
- Refresh pivot tables
- I have implemented the below macro code but I don't know how to dynamically code on finding total no. of rows present, here i'm hardcoding it to 872.
- Also, this one doesn't fill with numbers ( for ageing) for all cells, it leaves few rows in between and i don't know what is wrong. Is there a better way to implement ?
- For example - it fills rows from 1 to 20, then leaves 21 to 60 for some strange reason which I'm unable to find out and then fills again from 61 to 90. Not sure if i'm missing something. Please help
VBA Code:
Sub AgeingCalculation()
Dim xWb As Workbook
Dim xWs As Worksheet
Dim xRow As Integer
Dim K As Long
On Error Resume Next
Set xWs = Worksheets("Raw Data")
xWs.Range("I1").Value = "Current Date"
xWs.Range("I2:I872").Value = Now
Rows(1).Font.Bold = True
xWs.Range("J1").Value = "Ageing"
For K = 2 To 872
xWs.Cells(K, 10).Value = DateDiff("d", Cells(K, 3), Cells(K, 9))
Next K
End SubFill the blank cells with a default value of 15 days as all these tickets are older than 2 weeks and ageing calculation couldn't be done due to incorrect date format issue.
- Problem here is if rows 1 to 5 have values, 6th and 7th rows are blank then the below logic doesn't work. It works only if all the rows are blank but in real time i will never get the use case
- For example - row 1 to 20 may have values, 21 to 25 might be blank. Again 26 to 75 may have values, 76 to 90 might be blank.
- I want to fill the rows which are blank with default value of >15 days but its not coming correctly
VBA Code:
Sub Replace_Blank_With_Text()
Dim Range1, Range2 As Range, cell As Range
Dim Value_1 As String
Dim xWb As Workbook
Dim xWs As Worksheet
On Error Resume Next
Set xWs = Worksheets("Sheet5")
Value_1 = ">15 days"
Set Range1 = xWs.Range("J2:J837")
Set Range2 = xWs.Range("K2:K837")
For Each cell In Range1
If Range1.Text = "" Then
Range2.Value = Value_1
End If
Next cell
End SubRefresh pivot tables
- I have a sheet called as raw data, after doing task 1 (ageing calculation) and task 2 (fill the blank cells with default value) this can be accomplished.
- Tried the below logic but it doesn't work at all - not refreshing pivot tables - PivotTable2 and PivotTable3 are names of pivot tables whch are present in sheet called as PIVOT
VBA Code:
Sub PivotTableUpdate()
Dim PT As PivotTable
For Each ws In ActiveWorkbook.Worksheets
For Each PT In ActiveWorkbook.PivotTables
PT.RefreshTable
Next PT
Next ws
End Sub- Tried to hardcode logic like below also but doesn't make a difference, please help
VBA Code:
Sub PivotTableUpdateCalc()
Worksheets("PIVOT").PivotTables("PivotTable2").PivotCache.Refresh
Worksheets("PIVOT").PivotTables("PivotTable3").PivotCache.Refresh
End Sub
