Thoug finish process ,For Loop Not initialised error display on some weird place of code.

[Chirag R Raval]

Dear all respected Sirs & Experts ,

Simple macro I run.

1st loop, open one by one each file of predefined folder,

2nd inner loop for Advance filter on database for multiple criteria rows on that opened file, & copy result to destination file & close that file,

If there are no rows left for filter, naturally , control re-pass to 1st loop, for open next file for same process.

I also put check in inner loop (Adv Filter), that if no match found for criteria row, so filtered row blanks , if after header row, checks count visible cells , if there are no visible cells except first row, its jump on label skip:

I run below code, though all main procedure complete of advance filter on every opened files & copy filtered result to next sheet , when start new process on destination sheet, macro stop on some weird place , its display below error

'ADVANCE FILTER ON MULTIPLE CRITERA ,  multi critera , multi_critera & SEPRATE THAT FILE BASIC CODE
'https://chandoo.org/forum/threads/formula-for-advance-filter-on-multiple-criteria-for-separate-each-filtered-instance-as-new-file.36314/#post-218502
'ASKED FROM CHIRAG
'ANSWERED BY MR NARAYANK991
'Sub STG_MasterOrdStaMultCriSepFile()
Sub advfilterandcopytonxtsht()

    Dim lrow As Long
    Dim i As Integer
    Dim criteriarows As Long
    Dim inputdatarange As Range
    Dim criteria_range As Range
    Dim rw As Variant
    Dim criteria_header As Range
    Dim crange As Range
    Dim Criteria As New Collection


    With swsht
    If .AutoFilterMode Then .Cells.AutoFilter
        lrow = .Cells(Rows.Count, 1).End(xlUp).Row
        Set inputdatarange = .Range("A1:I" & lrow)
        criteriarows = .Range("K" & Rows.Count).End(xlUp).Row
        Set criteria_range = .Range("K2:K" & criteriarows).Resize(, 6)
        Set crange = .Range("R1:R2").Resize(, 6)
    End With

    On Error GoTo SKIP

    For Each rw In criteria_range.Rows

        crange.Cells(2, 1).Resize(, 6) = rw.Value
        inputdatarange.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=crange, Unique:=False

  '++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'most imp
'http://www.vbaexpress.com/forum/archive/index.php/t-18419.html
'herzberg 03-16-2008, 11:28 PM
'I work with Autofilters a lot too. Here's what I use:

Dim counter As Integer
    With inputdatarange
        counter = .Columns(1).SpecialCells(xlCellTypeVisible).Count
            If counter = 1 Then
    
                If ActiveSheet.FilterMode Then ActiveSheet.showAllData


        GoTo SKIP
  
'Occurs when no records are returned. Exit Sub here or something
'Basically, I count the number of visible rows. When it's 1, it means only the header is there and no records are returned.
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
ElseIf destsht.Range("A1") = "" Then

        swsht.Range("A1:I" & lrow).Copy

        Else
                      
            swsht.Range("A2:I" & lrow).Copy
            End If
    
        End With

    destsht.Activate

'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
If destsht.Range("A1") = "" Then
destsht.Range("A1").Activate

Else
'REMEMBER IF NO DATA FOUND THEN ("A").END METHOD FAIL &
'ROWS 1 WILL BE OVER WRITTEN THEN SECURED & MUST BE USED ROWS COUNT METHOD AS BELOW

destsht.Range("A" & Rows.Count).End(xlUp).Offset(1).Activate

End If

ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

      Application.CutCopyMode = False

              mainsht.Activate
'  Next


    '++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SKIP:
Next rw


SWBK.Activate

        If ActiveSheet.FilterMode Then ActiveSheet.showAllData
        ActiveSheet.AutoFilterMode = False

SWBK.Close SaveChanges:=False


DoEvents
destwbk.Sheets(1).Activate


Next r



'FILTER COPY FINISH
'NOW OTHER PROCESS OF FORMATTING AND BUYER NAME ADDED PROCESS START BELOW
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
destwbk.Sheets(2).Activate

ActiveSheet.Range("c:c").NumberFormat = ("dd/mm/yyyy")
      Columns.AutoFit

Set destwbk = Nothing
Set SWBK = Nothing



Application.Run "personal.xlsb!STGCompIndexMatchclosedPhoneOK"

'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'warning from here last row can not access with variable lrow so construct new AS DIRECT TILL COLUMN A'S END NOT END.UP

Dim LastRow As Long
'  Find Last Row
  LastRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

'  Select Range
  ActiveSheet.Range("A1:T" & LastRow).Select

  With Selection.Borders
    .LineStyle = xlContinuous
    .Weight = xlThin

End With

With ActiveSheet.Range("A1:T" & LastRow)
.Font.Size = 9
.Font.name = "Calibri"
End With

With ActiveSheet.Range("A1:T" & LastRow)
.LineStyle = xlContinuous
.Weight = xlThin
    End With

    End Sub

I search for solution for it but I can not figure out what mistake I do in my code.

Microsoft says below.



Though , Microsoft suggest not to jump out from loop, i need to jump on label skip: so I already put iferror goto skip laben in inner loop, but error not raised on inner loop, its raised on main file opening loop, 1st loop, or parent loop of inner loop.

Though all process of file opening & filtration process complete, weirdly its raised on next procedure of simple formatting.

After all process complete, & already start formatting, that not part of that both loop, like its suddenly realise something forgot to do in outer main loop & re-jump in file opening loop's "Next r" line ,stop, & raise that error.

Hope some solution there because I already stuck that error since last 5 days, can not outcome..

Please help , will be appreciated.

Regards,

Chirag Raval

 

[GraH - Guido]

Hi Chirag,

I do spot an IF , that you don't seem to close.... I never have used such a construction where an If is inside a With, so I might be heading wrong here.

With swsht
    If .AutoFilterMode Then .Cells.AutoFilter

Then there is

Next rw

and a bit further

Next r

This last one, also a loop, does not seem to start anywhere ...

If you put the last line

Next r

in comments, what happens then?

 

[Marc L]

Hi !

Goto statement can - must ! - be easily avoided
just with a conditional statement, it's just a - bad - heritage from BASIC
and I don't see a code using it since at least more than 20 years !

On Error can be a mess as it can mask any not expected error !

Using both you lost the "logic" during the code execution.
​

 

[Marc L]

I had not enough time to try to explain the use of On Error Goto so
when an error is trapped by it, you're out of a normal code
and it must be restarted via Resume statement
as it is clearly stated in VBA inner help of On Error Goto.
So the label (as On Error Goto label) can't be inside any loop …
​

 

[Chirag R Raval]

Dear Sirs @GraH - Guido & Sir @Marc L ,

Thanks for your effort..
Means I should re-think /re-learn about logical construct.

& Oh I learn never seen before concepts,

As per Sir @Grah - Guideo-"I never have used such a construction where an If is inside a With"
&
As per Sir @Mrc l "Goto statement can - must ! - be easily avoided
just with a conditional statement, it's just a - bad - heritage from BASIC
and I don't see a code using it since at least more than 20 years !

On Error can be a mess as it can mask any not expected error !

Using both you lost the "logic" during the code execution."

I really confused...I think I should / must Re-learn from start about Goto, On Error, & conditional stamen if should not use in With Construct"

Hope there are more understandable found about that..

Now, so what can I do for my above code?

I think in inner loop, I should roll-up all the things if filtered process not display any data or criteria row not match with data, or data not found as per criteria in filtered row & then should control pass to file open loop.

but how?.

Regards,

Chirag Raval

 

[Chirag R Raval]

Dear Sir @Marc L ,

Super I liner knowledge..."About use of On Error Goto, when an error is trapped by it, you're out of a normal code , and it must be restarted via Resume statement"

Thank you...I should / must need to more deeply understand things..

Regards,

Chirag Raval

 

[Marc L]

As you can see in the example of VBA inner help …​

Sub Demo0()
        On Error GoTo Trap
    For N% = 4 To 0 Step -1
        Debug.Print N & " ÷ " & N - 2;
        Debug.Print " = " & N / (N - 2)
    Next
        Exit Sub
Trap:
        Debug.Print " : error !"
        Resume Next
End Sub

Preventing the error so On Error is useless :​

Sub Demo1()
    For N% = 4 To 0 Step -1
        Debug.Print N & " ÷ " & N - 2;
        If N - 2 = 0 Then Debug.Print " : impossible !" Else Debug.Print " = " & N / (N - 2)
    Next
End Sub

 

[Chirag R Raval]

Dear Sir @Marc L,

Thanks for give strait forward way towards
Error handling.

Just Great, So I should construct inner loop that way that, if something not happen or to be,
Maximum try to should not need to Jump out from the inner or any loop.

Your above simple but great example is say many things about how to try
& Use 'IF" statement / coàndition in loop & try to cover all possible matters in loop , instead of wait of error raise.

I will surely revert , after try to solve my matter.

Till then please mention about any other aspect
Of error handling that make sound learning about matter.

Regards,

Chirag Raval

 

[Marc L]

As I just re-use what is yet in VBA inner help samples …

Other way when trapping an error is really needed :​

Sub Demo00()
        On Error Resume Next
    For N% = 4 To 0 Step -1
        Debug.Print N & " ÷ " & N - 2;
        Debug.Print " = " & N / (N - 2)
        If Err.Number Then Debug.Print " : error " & Err.Number: Err.Clear
    Next
End Sub

 

[Chirag R Raval]

Dear Sir,

I Found the problem & resolve it as per below

On Error GoTo SKIP
  
    For Each rw In criteria_range.Rows

        crange.Cells(2, 1).Resize(, 6) = rw.Value
        inputdatarange.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=crange, Unique:=False

'most imp  -'http://www.vbaexpress.com/forum/archive/index.php/t-18419.html
'herzberg 03-16-2008, 11:28 PM  'I work with Autofilters a lot too. Here's what I use:
'Occurs when no records are returned. Exit Sub here or something
'Basically, I count the number of visible rows. When it's 1, it means only the header is there and no records are returned.

Dim counter As Integer
    With inputdatarange
        counter = .Columns(1).SpecialCells(xlCellTypeVisible).Count
                    If counter = 1 Then
        
            GoTo SKIP
        
                  End If
              
                  End With
              
'++++++++++++++++++++++++++++++++++++++++++++++++
        
            If destsht.Range("A1") = "" Then
                swsht.Range("A1:I" & lrow).Copy
    
                Else
                          
                    swsht.Range("A2:I" & lrow).Copy
                          End If
                       
                        destsht.Activate
                    

                If destsht.Range("A1") = "" Then
                        destsht.Range("A1").Activate

                    Else

'REMEMBER IF NO DATA FOUND THEN ("A").END METHOD FAIL &
'ROWS 1 WILL BE OVER WRITTEN THEN SECURED & MUST BE USED ROWS COUNT METHOD AS BELOW

                        destsht.Range("A" & Rows.Count).End(xlUp).Offset(1).Activate

                            End If



ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

      Application.CutCopyMode = False
  
      
              mainsht.Activate
          
SKIP:

If ActiveSheet.FilterMode Then
        ActiveSheet.showAllData
    
        End If

Resume Next
            
Next

I cover skip label under in inner loop , do something necessary & use resume next , & re-pass control to inner loop's next procedure .

Then after (below skip trap) I naturally end or close main parent loop
just write next.

so I learned that if you want to jump out for some reason from inner loop
don't set trap or handler in out side of inner loop, but must cover that trap in inner loop..and must use resume next to carry on other procedure of that inner loop.


sorry sir, to use go to statement against your most important advise on post no 3 of this thread. but I am not very practice of error handling.
so pardon me as I am novice about that & use go to.

But I heartily want to learn & want to know more about how to efficiently use of "Go To"

I really feel that errors are our best friend to help us to understand things & force us to construct logic more precisely & more better & main benefit to learn error handling is we can learn code optimisation.

I really recognise, feel & found that Error handling need more precise & accurate understanding & its parallel need to precisely "if" conditional construct as possible..& every VBA users should repeatedly should in touch with it.

Please provide more understandable & more link about error handling & use of conditional statements instead of error handling , if you know, that not easily searchable on web.

Again thanks for your kind help..

Regards,

Chirag Raval

 

[Marc L]

As an efficient code does not need any Goto statement …
Try to find any in my codes within this forum …

I don't know any link as it's just about pure Logic !
First start your code without any Goto statement neither an error trapping.
When an error occurs, just analyse why and think about a workaround …
If you can't see any, just consider the error number and apply a strategy.
​

 

[Chirag R Raval]

Dear Sir Mark L,

Just great guidelines , never seen before.
How you learn that aspect?

I will try this strategy on my same this thread's code.
& Revert .

Thanks again sir.

Regards,

Chirag Raval

 

[Marc L]

I learned with errors I met.

Tell me your error (aka error # + message + codeline),
maybe I could share some workaround …
​