Data Validation Calling Macros Failed

Question

I have asked several questions in regards to data validation menu calling macros. I have come very close to solve my problems. However, when I apply my code on the worksheet, excel always print out the content of my last macro. My Code for the worksheet is as below:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$3" Then
    On Error GoTo bm_Safe_Exit
    Application.EnableEvents = False
    Select Case Target.Value2
         Case "ABCP"
            Call Macro1
         Case "Accounting Policy"
            Call Macro2
         Case "Audit Committee"
            Call Macro3
         Case "Auto"
            Call Macro4
         Case "Auto Issuer Floorplan"
            Call Macro5
         Case "Auto Issuers"
            Call Macro6
         Case "Board of Director"
            Call Macro7
         Case "Bondholder Communication WG"
            Call Macro8
         Case "Canada"
            Call Macro9
         Case "Canadian Market"
            Call Macro10
          Case Else
              'Do Nothing
    End Select
End If

bm_Safe_Exit: Application.EnableEvents = True End Sub

I have ten Macros, and the content is pretty much the same, only cell changes. And here is the code:

Sub Macro1()
Range("F2").Select
Selection.FormulaArray = _
    "=IF(COUNTIF(Database!R2C35:R10000C35,Committees!R2C1)>=ROW(Committees!R2C:RC),INDEX(Database!R2C[-5]:R10000C[-5],SMALL(IF(Database!R2C35:R10000C35=Committees!R2C1,ROW(Database!R2C35:R10000C35)-ROW(Database!R2C35)+1),ROWS(Committees!R2C:RC))),"""")"
Selection.AutoFill Destination:=Range("F2:T2"), Type:=xlFillDefault
Range("F2:T2").Select
Selection.AutoFill Destination:=Range("F2:T5000")
Range("F2:T5000").Select
Sheets("Reports").Select
Range("F2").Select
Selection.FormulaArray = "=IF(ISERROR(Committees!RC),"""",Committees!RC)"
Selection.AutoFill Destination:=Range("F2:T2"), Type:=xlFillDefault
Range("F2:T2").Select
Selection.AutoFill Destination:=Range("F2:T5000")
Range("F2:T5000").Select
Range("E2").Select
End Sub

When I run macro in VBA, it also notifies me that the AutoFill Range failed.

Please help correct the code, thanks!

First, I highly recommend studying How to avoid using .Activate \ .Select, as it can cause headaches for sure, especially when using multiple worksheets. Also, which macro is throwing the error, the Worksheet_Change event, or Macro 1? What line and what error does it occur on? — BruceWayne
Hi Bruce, thank you for the recommendation. It's the macro that is throwing error, the forth line with the selection.Autofill. — Joseph Lin
Hm, I tried that and it filled for me. I have a feeling it's because you're using .Select. I'll add an answer which will show you your Macro 1 without .Select and see if that helps any. — BruceWayne

BruceWayne BruceWayne · Accepted Answer · 2016-05-10T19:42:09

I think the issue is probably the use of .Select. Try replacing your Macro 1 with this:

Sub Macro_1()

Range("F2").FormulaArray = _
    "=IF(COUNTIF(Database!R2C35:R10000C35,Committees!R2C1)>=ROW(Committees!R2C:RC),INDEX(Database!R2C[-5]:R10000C[-5],SMALL(IF(Database!R2C35:R10000C35=Committees!R2C1,ROW(Database!R2C35:R10000C35)-ROW(Database!R2C35)+1),ROWS(Committees!R2C:RC))),"""")"
Range("F2").AutoFill Destination:=Range("F2:T2"), Type:=xlFillDefault
Range("F2:T2").AutoFill Destination:=Range("F2:T5000")

Sheets("Reports").Activate ' not needed, but there just in case you want to step through with F8
Sheets("Reports").Range("F2").FormulaArray = "=IF(ISERROR(Committees!RC),"""",Committees!RC)"
Sheets("Reports").Range("F2").AutoFill Destination:=Sheets("Reports").Range("F2:T2"), Type:=xlFillDefault
Sheets("Reports").Range("F2:T2").AutoFill Destination:=Sheets("Reports").Range("F2:T5000")

End Sub

Note all the Sheets("Reports") in the second part. You should also do that for the first part, I just didn't know what sheet that info was on.

Data Validation Calling Macros Failed

1 Answers