VBA Macro Misread

J

JosephALin

New Member
Joined
May 13, 2016
Messages
13
Hi, everybody! I am very new to excel vba and macro. Here is the situation I have now:

I created a data validation drop down menu that contains a list of names, which I expected to summon an unique macro that is linked to it.
The data validation code is as below:

HTML: 
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 Ifbm_Safe_Exit:    Application.EnableEvents = TrueEnd Sub
And the macro code format is like this, the only difference among all of them is the cell number in the first formula array,

HTML: 
Sub Macro1()'' Macro1 Macro'
'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:=xlFillDefaultRange("F2:T2").AutoFill Destination:=Range("F2:T6000")
Sheets("Reports").Range("F2").FormulaR1C1 = "=IF(ISERROR(Committees!RC),"""",Committees!RC)"Sheets("Reports").Range("F2").AutoFill Destination:=Sheets("Reports").Range("F2:T2"), Type:=xlFillDefaultSheets("Reports").Range("F2:T2").AutoFill Destination:=Sheets("Reports").Range("F2:T6000")
End Sub

And now the situation that I encounter is that, when I click one of the item in the dropdown menu, Excel prints out the right content, however, after that, no matter which item I choose from the list, it prints out the exact same content as the first item I chose, which is really weird. I can tell teh excel is running the correct macro code in behind, since the screen change showcases every procedure in it, only at the last step, the right content will be covered. I couldn't figure out why this is happening, is there anything wrong with my code or macro? </div></div>
Please help me solve this problem, thanks! :)
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
JosephALin said:
Hi, everybody! I am very new to excel vba and macro. Here is the situation I have now:

I created a data validation drop down menu that contains a list of names, which I expected to summon an unique macro that is linked to it.
The data validation code is as below:

Code: 
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 Ifbm_Safe_Exit:    Application.EnableEvents = TrueEnd Sub
And the macro code format is like this, the only difference among all of them is the cell number in the first formula array,

Code: 
Sub Macro1()'' Macro1 Macro'
'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:=xlFillDefaultRange("F2:T2").AutoFill Destination:=Range("F2:T6000")
Sheets("Reports").Range("F2").FormulaR1C1 = "=IF(ISERROR(Committees!RC),"""",Committees!RC)"Sheets("Reports").Range("F2").AutoFill Destination:=Sheets("Reports").Range("F2:T2"), Type:=xlFillDefaultSheets("Reports").Range("F2:T2").AutoFill Destination:=Sheets("Reports").Range("F2:T6000")
End Sub

And now the situation that I encounter is that, when I click one of the item in the dropdown menu, Excel prints out the right content, however, after that, no matter which item I choose from the list, it prints out the exact same content as the first item I chose, which is really weird. I can tell teh excel is running the correct macro code in behind, since the screen change showcases every procedure in it, only at the last step, the right content will be covered. I couldn't figure out why this is happening, is there anything wrong with my code or macro?
Please help me solve this problem, thanks! :)
Click to expand...

...update: if you use "code" instead of HTML then it will format correctly. Answer shortly...
 
Upvote 0
Code: 
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$3" Then ' This is where you need to look for your fault, maybe
    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
        End Select
        End
Ifbm_Safe_Exit:
        Application.EnableEvents = True
End Sub

... well what if your target isn't
Code: 
If Target.Address = "$B$3"
and then you want it to points somewhere else?
 
Last edited:
Upvote 0
Sorry, I was looking for a code option, but I didn't find it, I am new here. And now I don't see a edit option on the webpage, I will paste the code here.

The Data Validation dropdown list:
Code: 
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
          End Select
    End If
bm_Safe_Exit:
    Application.EnableEvents = True
End Sub

And the macro code:
Code: 
Sub Macro1()
'
' Macro1 Macro
'


'
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:T6000")


Sheets("Reports").Range("F2").FormulaR1C1 = "=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:T6000")


End Sub

Sorry again.
 
Upvote 0
I have made sure that is my correct target, and even when I run other code manually, selecte them from the script and then run, it will have the same problem.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Help with VBA Code in Excel
Replies
2
Views
306
tixarah
T
willow1985
  • Solved
VBA Compile error - Trying to send an email using VBA/Excel
Replies
6
Views
1K
gorgkhan
G
Guna13
  • Question Question
Help to Optimize Macro Code for Faster Performance
Replies
0
Views
1K
Guna13
Guna13
M
  • Question Question
Create new workbook for every sheet in active workbook, starting from Sheet(3) and saving them to a user specified folder location.
Replies
0
Views
995
Mars84
M
willow1985
  • Solved
How to end macro if no filtered results
Replies
3
Views
1K
Akuini
Akuini

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

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