Help to Call Sub or Function with conditions

nhnn1986

Board Regular
Joined
Oct 12, 2017
Messages
78
Hi all:

I want to have macro that Call al ot of Sub/Functions with conditions: Left(ws.name,4) = "AAA0" and ws.range("A2").value <> 0
When conditions match will Call Sub Filter_ & ws.name

For example: if ws.name = "AAA012345" and sheets("AAA012345").range("A2") <>0 then Call Filter_AAA012345
I try with code belove but it don't work

Code:
<code style="box-sizing: border-box; font-family: inherit; font-size: 1em;">Sub CallSub()
Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If Left(ws.Name, 4) = "AAA0" And ws.Range("A2").Value <> "" Then
            Call Filter_ & ws.Name
        End If
    Next c
End Sub</code>

Please help me./.
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

D_Mon

New Member
Joined
Feb 9, 2019
Messages
6
Hello

Code:
Sub CallSub()Dim ws As Worksheet
Dim c As Integer


    For Each ws In ThisWorkbook.Worksheets
        If Left(ws.Name, 4) = "AAA0" And ws.Range("A2").Value <> "" Then
            'Call Filter_ & ws.Name
            'I don't think you can cal a sub this way, but you could do
             Call Filter(ws.Name)
            Debug.Print ws.Name
        End If
    Next ws
End Sub

Sub Filter(thisWSName as string)
           if thisWSName = "AAA0_01" then
              ' do something
           end if
End Sub

Of curse you could also use "select ... case".

HTH
Regards
D.Mon
 

nhnn1986

Board Regular
Joined
Oct 12, 2017
Messages
78
Thanks for your respond but code error line: Call Filter(ws.Name)

And have notice: Compile error: Argument not optional
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,464
Office Version
  1. 365
Platform
  1. Windows
Hi, you could try like this:

Rich (BB code):
Sub CallSub()
Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If Left(ws.Name, 4) = "AAA0" And ws.Range("A2").Value <> "" Then
            Application.Run "Filter_" & ws.Name
        End If
    Next ws
End Sub

Although, depending on what your "filter_" macros do - you may want to consider passing the worksheet as an argument to a generic filter sub
 

nhnn1986

Board Regular
Joined
Oct 12, 2017
Messages
78

ADVERTISEMENT

Thanks @FormR but code don't work

Maybe I using basic code like:
Code:
Sub CallSub()
Call Filter_AAA001
Call Filter_AAA002
Call Filter_AAA003
Call Filter_AAA004
.........................
Call Filter_AAA099
[COLOR=#ff0000][/COLOR] [COLOR=#333333]End Sub

And have 99 code check value and to do something like:

Sub Filter_AAA001()
   If sheets("AAA001").range("A2") = 0 then exit sub
         code to do something
   End if
End sub
[/COLOR]
 

nhnn1986

Board Regular
Joined
Oct 12, 2017
Messages
78

ADVERTISEMENT

Hi, that's not nearly enough information for us to help you debug. Please try to elaborate.

OKey @FormR, my file have 99 sheets with name like: AAA001, AAA001 ,.... AAA099
And I have 99 Macro to get data from that 99 sheets. Because each sheet have a diffrience structure with other so must have macro for each sheet

I want macro Call all sub to get data with sheet have data
Code:
[COLOR=#333333]Sub CallSub()
[/COLOR]Call Filter_AAA001
Call Filter_AAA002
Call Filter_AAA003
Call Filter_AAA004
.........................
Call Filter_AAA099 
[COLOR=#333333][COLOR=#333333]End Sub[/COLOR][/COLOR]

Each sub of 99 subs like this:
Code:
Sub Filter_AAA001
If sheets("AAA001").range("A2") = 0 then Exit sub
Else if 
Macro to getdata form sheets("AAA001")

End sub

So it verry long code so I want a macro shorter.
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,464
Office Version
  1. 365
Platform
  1. Windows
Hi, you have misunderstood me, you have been given some code to try in post # 4 and you have told us that it doesn't work.

We need to know in what way does it not work.

Do you get a compile error - if so, what is the error description?
Do you get a run-time error - if so, what line causes the error? what is the error description?
Or something else - if so, you need to give us the details?
 
Last edited:

D_Mon

New Member
Joined
Feb 9, 2019
Messages
6
Thanks for your respond but code error line: Call Filter(ws.Name)

And have notice: Compile error: Argument not optional

Sorry. I posted right before leaving to work this morning.
There is a line break missing after the braces in the first line.
This works for me.
Code:
Sub CallSub()Dim ws As Worksheet
Dim c As Integer
    For Each ws In ThisWorkbook.Worksheets
        If Left(ws.Name, 4) = "AAA0" And ws.Range("A2").Value <> "" Then
             Debug.Print ws.Name
             Call Filter(ws.Name)
        End If
    Next ws
End Sub


Sub Filter(myWSName As String)
  Debug.Print "In Filter with " & myWSName
End Sub

I hope this helps.
Greetings
D.Mon
 

D_Mon

New Member
Joined
Feb 9, 2019
Messages
6
What the ...
The board software seems to add line breaks where i didn't put them.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,546
Messages
5,596,776
Members
414,100
Latest member
Eartheart

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
Top