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