Data being lost after or during a Sort implemented in a Macro

[DonEB]

Ladies and Gentlemen:

To say the least, I am completely baffled by what I am observing when a section of my Macro is implemented within my worksheet. To add to the confusion, the macro seems to work 50% of the time. If you will allow me, I'll share that portion of the code which is causing the problem and if someone would be so kind as to find the error or suggest a protentional solution or test... I would be so grateful.

First, you can see that I have made a couple attempts at debugging this myself and this is how I've been able to focus in on this particular piece of code.

• The first "MsgBox" comes right after a subroutine called PlayerCombo completes. I wanted to see if this subroutine writes the computed values to the spreadsheet at the desired locations
  • Answer: YES... every time the subroutine is run, the computed values are written to the desired locations
• The second "MsgBox" comes right after the Sort code completes. Here, I simply wanted to verify that after the conclusion of the Sort, the desired location still has values.
  • Answer: This is where I am baffled. The results are inconsistent. Sometimes values will be displayed after the SORT and other times NO values will be displayed.

One last piece of information... just in case this matters. This code is being created to handle assignment of players to 2, 3, 4 or 5 courts. But in this test, we are only working with 8 players or two courts. So, when computing Court 1 players, there are many combinations which this sort uses. But, once we get to Court 2, there is only one combination that the SORT code is asked to sort. In short and in this test, nothing really to sort for Court 2. However, I would still expect and do see on many occasions the correct values being written to the spreadsheet. But, once again, I don't understand why on other occasions the values seem to disappear.

Something seems to be causing the SORT to randomly lose all the values and I don't understand how or why. If anyone has any thoughts or suggestions, I would greatly appreciate it. Thank you for any assistance or guidance you can provide.

MsgBox "After Subroutine PlayerCombo - in Court2: " & Sheets("Courts2").Range("C7").Value & Sheets("Courts2").Range("D7").Value & Sheets("Courts2").Range("E7").Value & Sheets("Courts2").Range("F7").Value

'##################################################################################################################################
'# Once all player combos are written to Courts2, an additional column (Q) filled with calculations must be sorted decending order
'# with column P being the secondary sort
'##################################################################################################################################
ActiveWorkbook.Worksheets(wsName).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(wsName).Sort.SortFields.Add Key:=Range( _
"Q7:Q1826"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets(wsName).Sort.SortFields.Add Key:=Range( _
"P7:P1826"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets(wsName).Sort
.SetRange Range("C7:Q1826")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

MsgBox "After Worksheet Sort - in Court2: " & Sheets("Courts2").Range("C7").Value & Sheets("Courts2").Range("D7").Value & Sheets("Courts2").Range("E7").Value & Sheets("Courts2").Range("F7").Value

 

[DonEB]

Sorry to bother everyone... if I could delete the original inquiry I would.

While I have not identified the exact cause of the problem of the problem I am having above, I have determined it is NOT caused by the Sort function but by an added value (Column Q) that contains a calculated value and is the column that the worksheet is sorted on.

My apologies for not catching this sooner.

 

[Fluff]

Do any of the cells contain a formula, if so what?

 

[DonEB]

Do any of the cells contain a formula, if so what?

The column that the range of cells was to be sorted on contained a formula the caused the row to be placed at the bottom of the range and out of sight.

I have since adjusted this formula and now it is working very well.

Thank you for your interest and, once again, I apologize for not fully thinking out the problem before presenting it. I simply woke up the next morning after thinking about it overnight and realized the sort was not losing the values it was simply pushing the expected range of cells down a 1000+ rows and out of sight.

Feel free to delete this inquiry and thank you for your time.

 

[Fluff]

Glad you sorted it & thanks for letting us know.