Code executing inconsistent

**Rasm** · 02-07-2012, 03:05 AM

The code below has two (3) steps

1) Finds & replaces values on the sheet

2) Refresh the sheet (I added this later after I identified problem)

3) Sorts the sheet

Originally I did not have step 2 - so on some PCs my code works - others it does not - If I insert a code break then it works. So I think the problem is that the replacement routine has not completed b4 I start sorting - So by inserting 'Application.Calculate' I make sure the sheet is refreshed (so I think) - Not sure if this makes sense. If it does --- what is the proper code line to make sure the replacement is completed b4 the sorting starts

Code:

        Selection.Replace WHAT:=MyString(iii), _
                Replacement:=ProductNameSlt(ii), _
                LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
                
        Application.Calculate '   Using This to refresh my sheet _
                                   WHAT IS THE RIGHT WAY
       
        
        Astr = "A1:" & Split(Cells(1, ColLast).Address, "$")(1) & LastRow
        .Range(Astr).Sort Key1:=.Columns(ColProductID), Order1:=xlAscending, Header:=xlYes

I added this later in the post - would this be better

Application.Wait Time + TimeSerial(0, 0, 1) 'Waits 1 second before code continues

Now - this is the third Edit - It turns out that I had my entire code side the same

WITH worksheet("sheet1)

routine - I have now added END WITH and then immediately followed by a WITH worksheet("sheet1) in the next line - that has fixed the problem. I am puzzled by this - anybody ever had a similar experience.

**Rasm** · 02-07-2012, 05:01 PM

OK - I think this is the memory ADO leak problem

see

Windows 8 Developer Preview build contains the complete fix of the ADO typelib issue

Does anybody have method of connection Excel to Access using something different than an ADO libery