Range changes values unexpectedly as code executes

[Kerry H]

Hi all, Hope you can help with the following. It is a section of a long Sub. At the beginning of the Sub, a range "RangeR" is read in. It is an entire worksheet which will later be sorted in the code below. There are 5 sorts being carried out following some copy/paste operations. The copy/paste is from WS 1 to WSs 2 to 5. In sort Case 3 (below), WS 1 is sorted and printed and then meant to be returned to its original state. RangeR represents the original state of WS 1. By trial and error, I have determined that RangeR retains its original values throughout all the previous operations of the Sub. However, at the end of the sort below, which works just fine, RangeR has taken on the values of the sorted Range1. (Range1 is also the entire original matrix of WS 1 and is changed by the sort. During the Sub, Range1 takes on various values, depending on the WS being pasted or sorted.) I have left out the print statements to test the Sub. Thank for your insights.


Case 3 ' Sort by Retail type (alpha), Name, Value in RETAILERS onl y

' RangeR ok at this point

ActiveWorkbook.Worksheets(Sh).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(Sh).Sort.SortFields.Add2 Key:=Range5, SortOn:=xlSortOnValues, order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets(Sh).Sort.SortFields.Add2 Key:=Range3, SortOn:=xlSortOnValues, order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets(Sh).Sort.SortFields.Add2 Key:=Range4, SortOn:=xlSortOnValues, order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets(Sh).Sort
.SetRange Range1
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

' At this point, RangeR has taken on the values of the sorted "Range1".
With ActiveWorkbook.Worksheets(Sh)
RangeR.Copy
Worksheets(Sh).Cells(r1, c1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With

[p45cal]

At the beginning of the Sub, a range "RangeR" is read in.
I'd like to see the line that does that.
Have you Dimmed the variable RangeR? If so how?

RangeR represents the original state of WS 1. By trial and error, I have determined that RangeR retains its original values throughout all the previous operations of the Sub.
With your line:
RangeR.Copy
is the strong implication that RangeR is a range, if so it does not retain original values.

You might get away with the likes of holding the original values of that range at the start of your macro with a line such as:
RangeRValues=RangeR.value
and later, return those values to the sheet with the likes of:
RangeR.value = RangeRValues

[Kerry H]

RangeR is a range and defined as such.

I have now added a sequential index number to WS 1 and simply re-sorted on the index number after printing out the WS.
It may have been possible, also, to copy the original WS 1 matrix to an unused area of WS 1; then copy it back again after the sort.
I must admit, though, that I don't understand why the original coding didn't work. i.e. why RangeR doesn't hold its values if it's never used in the SUB until the end.

Thanks very much for your help.
(I've asked before: is there a thank you button on this forum?)
(I've also asked how VBA Express is related to MrExcel?)