Help to Call Sub or Function with conditions

[nhnn1986]

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 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./.

 

[D_Mon]

Hello

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]

Thanks for your respond but code error line: Call Filter(ws.Name)

And have notice: Compile error: Argument not optional

 

[FormR]

Hi, you could try like this:

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]

Thanks @FormR but code don't work

Maybe I using basic code like:

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]

 

[FormR]

but code don't work

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

 

[nhnn1986]

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

[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:

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]

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?

 

[D_Mon]

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.

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]

What the ...
The board software seems to add line breaks where i didn't put them.