AutoFill Method of Range Class Failed.......for a Filldown of Array Formula

herman925

New Member
Joined
Apr 9, 2017
Messages
24
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:
  • 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

  1. An input box allowing colleagues to type in their name
  2. The system returns a total number of Client numbers from C2 after counting matches in the database corresponding to the colleague's name
  3. Clear the Table, except the first row
  4. Write the array formula on AV5
  5. 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!
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top