VBA Sort Syntax Correct?

[AlexB123]

Hi all,

My macro runs and saves a created workbook from the macro workbook. But the file that is saved corrupt and excel has to ask if the user wants to recover data whenever these files are produced.

I have gone over all my code, and I suspect that the way I am sorting the data is buggy.

Is my syntax correct? Could this be a cause of this issue?

Set sort1 = Range("A1:AJ1")
        Set sort2 = Range("A1")
        Set sort3 = Range("G1")
        Set sort4 = Range("H1")
        
    mainSh3.Range("A1").Value = "Match?"
    mainSh3.Range("B1").Value = "PlnTaxSrc"
    mainSh3.Range("C1").Value = "Current"
    mainSh3.Range("D1").Value = "Compare"
            
            With mainSh3.Sort
                .SortFields.Add Key:=sort2, Order:=xlDescending
                .SortFields.Add Key:=sort3, Order:=xlDescending
                .SortFields.Add Key:=sort4, Order:=xlDescending
                .SetRange sort1
                .Header = xlYes
                .Apply
            End With

I set the references to mainSh3 with:

Set mainWB = Workbooks.Add 
    mainWB.Sheets.Add After:=Sheet1, Count:=2 
        Set mainSh1 = mainWB.Worksheets(1)
        Set mainSh2 = mainWB.Worksheets(2)
        Set mainSh3 = mainWB.Worksheets(3)

 

[shg]

Using the Range.Sort method:

Sub AlexB()
  With mainSh3
    .Range("A1:D1").Value2 = Array("Match?", "PlnTaxSrc", "Current", "Compare")
    .Range("A:AJ").Sort Key1:=.Range("A1"), Order1:=xlDescending, _
                        Key2:=.Range("G1"), Order2:=xlDescending, _
                        Key3:=.Range("H1"), Order3:=xlDescending, _
                        Header:=xlYes
  End With
End Sub

 

[AlexB123]

Wonderful! My sort works and my file is no longer corrupt!

On the downside ... I have a loop that uses .Replace to remove spaces, commas and dashes, but it seems to have quit working. I know I should post another thread, but any ideas why fixing the bug in the sort would break the replace?

Many, many thanks!

Dim j As Long
Dim Ary2 As Variant
           
 Ary2 = Array(" ", "-", ",")
           
     For j = 1 To UBound(Ary2)
                    mainSh3.Range("C2:C" & lastrowSh3).Replace Ary2(j), "", xlPart, xlByColumns, , , False, False
                Next j

 

[AlexB123]

Nevermind, shg!!!

This was a completely unrelated bug ... I forgot to change the counter variable back to 0 for my array (I grabbed the code from a module in the same workbook that uses Option Base 1 for arrays).

I appreciate your help, getting the file to save uncorrupted was an all day process that only ended with your post!

 

[shg]

You're welcome.