• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Loop is breaking in mid.

sumit Madaan

New Member
When i am running this code, same is going break in the mid and then again i have change the range of loop and loop runs.
but i am not able to understand why it is breaking in the Mid.

Loop is 2 to 39, sometimes it breaks in 14, sometimes 16, sometimes 19 etc.. every time loop break once when i run this code.

Please help.

Code:
sub FILE_RH()
'
' copy Macro
'
' Keyboard Shortcut: Ctrl+Shift+Q
'




For i = 2 To 39
Dim r As Range
Set r = Selection


Workbooks("MasterBBG - Copy").Sheets("Data").Activate
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.AutoFilter
    Range("A1").Select
    Sheets("Data").Range("$A$1:$AI$200000").AutoFilter Field:=31, Criteria1:=Sheet3.Range("a" & i).Value
    Range("$A$1:$AI$200000").Select
    Range("A1").Activate
    Selection.SpecialCells(xlCellTypeVisible).Select
    Range("$A$1:$AD$200000").Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\ibl63122\Desktop\New folder\" & Sheet3.Range("d" & i).Value, FileFormat:= _
        xlExcel12, CreateBackup:=False
        ActiveWorkbook.Close
    
Workbooks("MasterBBG - Copy").Sheets("sheet1").Activate
ActiveWorkbook.EnvelopeVisible = True
With r.Parent.MailEnvelope.Item
.To = Sheet3.Range("b" & i).Value
.cc = Sheet3.Range("C" & i).Value
.bcc = ""
.Subject = "Portfolio Position of " & Sheet3.Range("a" & i).Value
.attachments.Add "C:\Users\ibl63122\Desktop\New folder\" & Sheet3.Range("d" & i).Value
.send

Kill "C:\Users\ibl63122\Desktop\New folder\" & Sheet3.Range("d" & i).Value
End With
Next i
              
End Sub
MOD EDIT: CODE Tags were added.
 
Last edited by a moderator:

AlanSidman

Active Member
This appears to be a recorded Macro and is a bit convoluted and tough to follow. In simple terms, please explain what you are trying to do. It would be helpful if you attached a file showing a before and after scenario (mocked up as necessary). Using the example, explain what you would do if you had to do it manually. Once we have this information, I am sure we can give you some clean code.
 

sumit Madaan

New Member
Hi Alan,

this code is running perfectly. the only problem i am facing in this code is loop is getting break in the mid.
in attached code i am running loop from row 2 to 39.

so when i run this code, it will break in mid like sometimes in 17 number, 19 number etc.

and it breaks only once when i run this code.
 

vletm

Excel Ninja
sumit Madaan
Have You checked that Sheet3's 17..19 rows used values are valid?
You don't have any error handling procedures there.
Do You really need always to use 200000 rows data?
 

vletm

Excel Ninja
sumit Madaan
I wrote three lines and You wrote back ... one line.
It could only make more guesses...
1) You haven't checked Sheet3's those rows which could make challenges with You ... please, check, if You would like to solve Your challenge.
2) If no error handling procedures ... then You or anyone could only guess.
3) Complete Excel data ... yes ... how about if 'complete Excel data has over 200000 rows? Why not use number of rows, which has data?
 

AlanSidman

Active Member
Sumit
Still waiting for an explanation to my request. Do you want help or do you want to argue with us. We only ask for information to help us to help you. Without clear answers to our questions, you are wasting our time and delaying getting a solution.

And if it breaks, then it cannot be running perfectly.
 
Top