I have a table that goes from Column A to Column AW, starting at row 5 (with Row 4 being headers, and Row 1-3 for placing buttons, and a Summary Table
Each row of the table depends on detecting a 5 digit 'Client Number' from Column AV
Therefore, the starting cell will be AV5 for the first row of data
What I want to do this:
Basic Behaviour of the Macros I want to achieve
Need Help
As much as I tried, the error AutoFill Method of Range Class still fails. I don't really know what is happening so I need some help, please!
Each row of the table depends on detecting a 5 digit 'Client Number' from Column AV
Therefore, the starting cell will be AV5 for the first row of data
What I want to do this:
- Based on the 'Summary Table', it counts for a number of service users assigned to a colleague
Code:
=COUNTIF(Table_MIS[CASEWORKER],A2)
- A2 = Name of the Colleague
- Then, I use a button to initiate the Macro as below:
Code:
Private Sub Check_Cases_Click()
Dim NoOfClients As Long
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = False
CO_Select = Application.InputBox("Please input the name of caseworker you would like to check on.", "Caseworker Name")
Range("A2").value = CO_Select
Application.ScreenUpdating = False
NoOfClients = Range("C2").value
CO_Name = Range("A2").value
CheckCaseMsg = MsgBox(CO_Name & ", there are " & NoOfClients & " clients under your name." & vbNewLine & vbNewLine & _
"System will now calculate all your active cases and display " & vbNewLine & _
"all the clients for your information." & vbNewLine & vbNewLine & _
"Confirm?", vbYesNo, "Case Checking")
If CheckCaseMsg = vbNo Then
Exit Sub
End If
If CheckCaseMsg = vbYes Then
'Remove the filters if one exists
'=========================================
If ActiveSheet.FilterMode Then
Selection.AutoFilter
End If
Clear
Startup_Formula
'Fill down the formula for n times where n= No of Clients of the Caseworker'
'=============================================================================
Dim Sht As Worksheet
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set Sht = Worksheets("Grand Info Sheet")
NoOfClients = Range("C2").value
NoOfClientsAdjusted = NoOfClients + 4
Sht.Range("AV5").AutoFill Destination:=Sht.Range("AV5:AV" & NoOfClientsAdjusted)
Application.Calculation = xlCalculationAutomatic
Range("GI_Table[[#All],[Client number]]").Copy
Range("GI_Table[[#All],[Client number]]").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.ScreenUpdating = True
GI_CustomSort
MsgBox "Case Checking Ready", vbInformation, "Ready"
Range("A1").Select
End If
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
- The Startup_Formula is an array formula that I want to use to be filled down
Code:
Sub Startup_Formula()
Sheets("Grand Info Sheet").Range("AV5").FormulaArray = "=IFERROR(INDEX(Table_MIS[CLIENTNUM],SMALL(IF($A$2=Table_MIS[CASEWORKER]," & _
"ROW(Table_MIS[CASEWORKER])-MIN(ROW(Table_MIS[CASEWORKER]))+1,""""),ROW(AV5))),"""")"
End Sub
Basic Behaviour of the Macros I want to achieve
- An input box allowing colleagues to type in their name
- The system returns a total number of Client numbers from C2 after counting matches in the database corresponding to the colleague's name
- Clear the Table, except the first row
- Write the array formula on AV5
- Filldown the formula for a number of times, where it depends on how many clients are detected. The number of fill down time is added by 4 (thus 'Adjusted') because of the location of the table
Need Help
As much as I tried, the error AutoFill Method of Range Class still fails. I don't really know what is happening so I need some help, please!