Hướng dẫn excel vba sort text as numbers - excel vba sắp xếp văn bản dưới dạng số

Tôi có một vấn đề. Tôi đang lọc từ một bảng sản phẩm lớn (với khoảng 32'000 hàng) các sản phẩm mà tôi cần và sắp xếp nó trước theo tên sản phẩm và sau đó theo số phân phối.

Vấn đề của tôi là, nếu tôi muốn lọc và sắp xếp các số dưới dạng tên sản phẩm (cũng có thể), nó không sắp xếp chính xác (xem hình).

Hình ảnh của bảng được lọc và sắp xếp

Mã của tôi là cái này:

'*************************************************************
'Filter Product Code
Set myrange = Range("A1:A" & lastrow)

For Each c In myrange
    If Len(c.Value) <> 0 Then
                ThisWorkbook.Worksheets(Worksheets.Count).Columns("A:D").AdvancedFilter xlFilterCopy, _
            Sheet1.Range("A1:A" & lastrow), Sheet1.Range("G1"), False
    End If
Next

'Sheet1.Range("51:100").RemoveDuplicates Columns:=3, Header:=xlYes

'*******************************************************************
'Sort the filtered list first by Product Code then by the Delivery number
Dim lngRow As Long
Dim lngRowMax As Long
Dim wsf As WorksheetFunction

With Sheet1

lastrow = Cells(Rows.Count, 8).End(xlUp).Row

Range("G2:J" & lastrow).Sort Key1:=Range("G2:G" & lastrow), _
 Order1:=xlAscending, Key2:=Range("I2:I" & lastrow), _
 Order1:=xlAscending, Header:=xlNo

Set wsf = Application.WorksheetFunction
lngRowMax = .UsedRange.Rows.Count

End With

Làm thế nào tôi có thể tiến hành để nó sắp xếp chính xác. Đầu tiên cùng một mã sản phẩm, sau đó theo số giao hàng (tăng dần) vì tôi muốn bán các sản phẩm lâu đời nhất trước.

Cảm ơn bạn đã trả lời.

Trong bài viết này

• Sắp xếp dữ liệu với Excel VBA
  • Sử dụng thực tế phương pháp sắp xếp trong VBA
  • Dữ liệu mẫu để chứng minh sự sắp xếp Excel với VBA
  • Ghi một macro cho một loại VBA
  • Mã VBA để thực hiện một loại cấp độ duy nhất
  • Mã VBA để thực hiện một loại đa cấp
  • Sắp xếp theo màu di động
  • Sắp xếp theo màu phông chữ
  • Sử dụng các tham số khác trong phân loại VBA
    • Sắp xếp
    • Gọi món
    • DataOption
    • Tiêu đề
    • Matchcase
    • Định hướng
    • Sắp xếp
    • Sử dụng một sự kiện nhấp đúp để sắp xếp dữ liệu bảng
  • Mở rộng chức năng sắp xếp bằng VBA

Sắp xếp dữ liệu với Excel VBA

Sử dụng thực tế phương pháp sắp xếp trong VBA

Dữ liệu mẫu để chứng minh sự sắp xếp Excel với VBA

Ghi một macro cho một loại VBA

Mã VBA để thực hiện một loại cấp độ duy nhất

Mã VBA để thực hiện một loại đa cấp

Sắp xếp theo màu di động

Sắp xếp theo màu phông chữ

Sử dụng các tham số khác trong phân loại VBA

Sắp xếp

Worksheets("Sheet1").Sort.SortFields.Clear

Gọi món

Sử dụng thực tế phương pháp sắp xếp trong VBA

Dữ liệu mẫu để chứng minh sự sắp xếp Excel với VBA

Ghi một macro cho một loại VBA

Mã VBA để thực hiện một loại cấp độ duy nhất

Mã VBA để thực hiện một loại đa cấp

Dữ liệu mẫu để chứng minh sự sắp xếp Excel với VBA

Ghi một macro cho một loại VBA

Mã VBA để thực hiện một loại cấp độ duy nhất

Mã VBA để thực hiện một loại đa cấp

Sắp xếp theo màu di động

Ghi một macro cho một loại VBA

Mã VBA để thực hiện một loại cấp độ duy nhất

Mã VBA để thực hiện một loại đa cấp

Sắp xếp theo màu di động

Hãy nhớ rằng đối với một hoạt động được thực hiện trong VBA, không có tính năng hoàn tác, vì vậy, bạn nên tạo một bản sao của dữ liệu bảng lên một bảng tính khác trước khi bạn bắt đầu viết mã sắp xếp của mình.

Ví dụ, nếu bạn thực hiện một sắp xếp đơn giản trên dữ liệu mẫu ở trên, việc sắp xếp theo nhân viên, bản ghi sẽ tạo mã sau:

Sub Macro1()

Range("A1:E6").Select

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("A2:A6"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Range("A1:E6")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

Đây là một đoạn mã khá lớn, và rất nhiều trong số đó là không cần thiết vì các tham số mặc định đang được sử dụng. Tuy nhiên, nếu bạn phải chịu áp lực thời gian để hoàn thành một dự án và bạn cần một số mã nhanh chóng hoạt động, bạn có thể dễ dàng dán nó vào mã VBA của riêng bạn.

Tuy nhiên, nếu bạn muốn làm cho mã của mình dễ hiểu và thanh lịch hơn, thì có những tùy chọn khác có sẵn. & NBSP; 

Mã VBA để thực hiện một loại cấp độ duy nhất

Nếu bạn chỉ muốn sắp xếp mã mẫu chỉ dựa trên nhân viên như trước khi ghi macro, mã rất đơn giản:

Sub SingleLevelSort()

Worksheets("Sheet1").Sort.SortFields.Clear

Range("A1:E6").Sort Key1:=Range("A1"), Header:=xlYes

End Sub

Điều này dễ hiểu hơn nhiều so với mã được ghi lại vì nó chấp nhận các mặc định, ví dụ: sắp xếp tăng dần, do đó không cần phải đặt các tham số thành các giá trị mặc định. Điều này giả định rằng bạn đã sử dụng một câu lệnh ’rõ ràng trước đó.

Phương thức ’rõ ràng được sử dụng ban đầu để đảm bảo rằng mọi tham số sắp xếp cho bảng tính đó được đặt trở lại các giá trị mặc định. Người dùng có thể đã đặt các tham số trước đây thành các giá trị khác nhau hoặc một loại sớm hơn trong VBA có thể đã thay đổi chúng. Điều quan trọng là bắt đầu từ một vị trí mặc định khi sắp xếp, nếu không bạn có thể dễ dàng kết thúc với kết quả không chính xác.

Phương thức rõ ràng không đặt lại tham số tiêu đề và nên đưa vào mã này vào mã của bạn, nếu không, Excel có thể thử và đoán xem hàng tiêu đề có xuất hiện hay không.

Chạy mã này so với dữ liệu mẫu và bảng tính của bạn sẽ trông như thế này:

Mã VBA để thực hiện một loại đa cấp

Bạn có thể thêm nhiều cấp độ sắp xếp theo yêu cầu trong mã của bạn. Giả sử rằng bạn muốn sắp xếp đầu tiên theo bộ phận và sau đó vào ngày bắt đầu nhưng theo lệnh tăng dần cho bộ phận và đơn đặt hàng giảm dần cho ngày bắt đầu:

Sub MultiLevelSort()

Worksheets("Sheet1").Sort.SortFields.Clear

Range("A1:E6").Sort Key1:=Range("E1"), Key2:=Range("C1"), Header:=xlYes, _
    Order1:=xlAscending, Order2:=xlDescending

End Sub

Lưu ý rằng hiện có hai khóa trong câu lệnh SORT (KEY1 và KEY2). KEY1 (Cột bộ E) Trước hết và sau đó là KEY2 (Cột ngày bắt đầu C) được sắp xếp dựa trên sắp xếp đầu tiên.

Ngoài ra còn có hai tham số thứ tự. Order1 Associates với KEY1 (SỞ) và ASPRED2 Associates với KEY2 (ngày bắt đầu). Điều quan trọng là đảm bảo rằng các khóa và đơn đặt hàng được giữ theo từng bước.

Chạy mã này so với dữ liệu mẫu và bảng tính của bạn sẽ trông như thế này:

Mã VBA để thực hiện một loại đa cấp

Bạn có thể thêm nhiều cấp độ sắp xếp theo yêu cầu trong mã của bạn. Giả sử rằng bạn muốn sắp xếp đầu tiên theo bộ phận và sau đó vào ngày bắt đầu nhưng theo lệnh tăng dần cho bộ phận và đơn đặt hàng giảm dần cho ngày bắt đầu:

Lưu ý rằng hiện có hai khóa trong câu lệnh SORT (KEY1 và KEY2). KEY1 (Cột bộ E) Trước hết và sau đó là KEY2 (Cột ngày bắt đầu C) được sắp xếp dựa trên sắp xếp đầu tiên.

Sub MultiLevelSort()

Worksheets("Sheet1").Sort.SortFields.Clear
Worksheets("Sheet1").UsedRange.Sort Key1:=Range("E1"), Key2:=Range("C1"), Header:=xlYes, _
    Order1:=xlAscending, Order2:=xlDescending

End Sub

Ngoài ra còn có hai tham số thứ tự. Order1 Associates với KEY1 (SỞ) và ASPRED2 Associates với KEY2 (ngày bắt đầu). Điều quan trọng là đảm bảo rằng các khóa và đơn đặt hàng được giữ theo từng bước.

Cột bộ phận (E) theo thứ tự tăng dần và cột Ngày bắt đầu (C) theo thứ tự giảm dần.

Hiệu ứng của loại này là đáng chú ý nhất khi nhìn vào Jane Halfacre (hàng 3) và John Sutherland (hàng 4). Cả hai đều ở trong tài chính nhưng Jane Halfacre bắt đầu trước khi John Sutherland và các ngày được thể hiện theo thứ tự giảm dần.

Nếu phạm vi dữ liệu bảng có thể có bất kỳ độ dài nào, bạn có thể sử dụng đối tượng sử dụng để xác định phạm vi sắp xếp. Điều này sẽ chỉ hoạt động nếu chỉ có dữ liệu bảng trên bảng tính vì bất kỳ giá trị nào bên ngoài dữ liệu sẽ cho kết quả không chính xác cho số lượng hàng và cột.

Điều này ngăn chặn vấn đề nếu bạn sử dụng phương thức ‘end (xldown), để xác định phạm vi sắp xếp. Nếu có một ô trống ở giữa dữ liệu, thì bất cứ điều gì sau khi di động trống sẽ không được bao gồm, trong khi sử dụng sẽ đi xuống ô hoạt động cuối cùng trong bảng tính.

Mã hóa VBA được thực hiện dễ dàng

Sub SingleLevelSortByCellColor()

Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("A2:A6"), _
    SortOn:=xlSortOnCellColor, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Range("A2:E6")
    .Apply
End With

End Sub

Ngừng tìm kiếm mã VBA trực tuyến. Tìm hiểu thêm về Automacro - Trình tạo mã VBA cho phép người mới bắt đầu mã hóa các quy trình từ đầu với kiến ​​thức mã hóa tối thiểu và với nhiều tính năng tiết kiệm thời gian cho tất cả người dùng!

Tìm hiểu thêm !!

Sắp xếp theo màu di động

Kể từ Excel 2007, việc sắp xếp theo màu nền của một ô là có thể, điều này mang lại sự linh hoạt rất lớn khi thiết kế mã sắp xếp của bạn trong VBA.

Mã này sẽ sắp xếp phạm vi dữ liệu mẫu (A2: A6) dựa trên màu nền ô. Lưu ý rằng hiện tại có một tham số bổ sung có tên là ‘syton, có giá trị của‘ xlsorcellcolor.

Lưu ý rằng tham số ‘syton, chỉ có thể được sử dụng bởi một đối tượng bảng tính chứ không phải bởi một đối tượng phạm vi.

Do đó, mã phức tạp hơn so với một loại sử dụng các giá trị ô.

Sub SingleLevelSortByFontColor()

Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add(Range("A2:A6"), _
    xlSortOnFontColor, xlAscending, xlSortNormal).SortOnValue.Color = RGB(0, 0, 0)

With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Range("A1:E6")
    .Header = xlYes
    .Orientation = xlTopToBottom
    .Apply
End With

End Sub

Mã để sắp xếp theo màu phông chữ phức tạp hơn nhiều so với màu nền ô. Tham số ‘syton, hiện giữ giá trị của‘ xlsortonfontcolor.

Lưu ý rằng bạn phải chỉ định định hướng là ‘xltoptobottom, và bạn phải chỉ định một màu để sắp xếp. Điều này được chỉ định theo thuật ngữ RGB (đỏ, xanh lá cây, đen) với các giá trị đi từ 0 đến 255.

Sau khi chạy mã này so với dữ liệu mẫu, bảng tính của bạn sẽ trông như thế này:

Sắp xếp bằng màu sắc trong VBA phức tạp hơn nhiều so với loại đa cấp, nhưng nếu mã sắp xếp của bạn không hoạt động (điều này có thể xảy ra nếu thiếu tham số hoặc bạn chưa nhập chính xác mã) thì bạn luôn có thể quay lại khi ghi lại khi ghi Một macro và tích hợp mã được ghi vào VBA của bạn.

Sử dụng các tham số khác trong phân loại VBA

Có một số tham số tùy chọn mà bạn có thể sử dụng trong mã VBA của mình để tùy chỉnh loại của bạn.

Sắp xếp

SHOton chọn liệu loại sẽ sử dụng các giá trị ô, màu nền ô hoặc màu phông chữ ô. Cài đặt mặc định là giá trị ô.

SortOn = xlSortOnValues

Gọi món

Đặt hàng chọn liệu loại sẽ được thực hiện theo thứ tự tăng dần hoặc giảm dần. Mặc định là tăng dần.

Order = xlAscending

DataOption

DataOption chọn cách sắp xếp văn bản và số. Tham số xlsortnormal sắp xếp dữ liệu số và văn bản riêng biệt. Tham số xlsorttextasnumbers coi văn bản là dữ liệu số cho loại. Mặc định là xlsortnormal.

Worksheets("Sheet1").Sort.SortFields.Clear

Tiêu đề chọn liệu phạm vi dữ liệu bảng có hàng tiêu đề hay không. Nếu có một hàng tiêu đề, bạn không muốn cái này bao gồm trong loại.

Các giá trị tham số là Xlys, XLNO và XlySnoguess. Xlyesnoguess rời khỏi nó để xác định xem có hàng tiêu đề hay không, có thể dễ dàng dẫn đến kết quả không nhất quán. Việc sử dụng giá trị này không được khuyến khích.

Giá trị mặc định là XNO (không có hàng tiêu đề trong dữ liệu). Với dữ liệu đã nhập, thường có một hàng tiêu đề, vì vậy hãy đảm bảo rằng bạn đặt tham số này thành Xlys.

Worksheets("Sheet1").Sort.SortFields.Clear

Matchcase

Tham số này xác định xem loại có nhạy cảm trường hợp hay không. Giá trị tùy chọn là đúng hoặc sai. Nếu giá trị là sai, thì các giá trị trường hợp thường được coi là giống như các giá trị trường hợp trên. Nếu giá trị là đúng, thì loại sẽ hiển thị sự khác biệt giữa các giá trị trường hợp trên và thường trong loại. Giá trị mặc định là sai.

Worksheets("Sheet1").Sort.SortFields.Clear

Định hướng

Tham số này xác định liệu loại sẽ diễn ra xuống qua các hàng hay trên tất cả các cột. Giá trị mặc định là xltoptobottom (sắp xếp qua các hàng). Bạn có thể sử dụng XLLEFTTORIGHT nếu bạn muốn sắp xếp thời gian. Các giá trị như XLROWS và XLColumns không hoạt động cho tham số này.

Worksheets("Sheet1").Sort.SortFields.Clear

Sắp xếp

Tham số này chỉ được sử dụng để sắp xếp các ngôn ngữ Trung Quốc. Nó có hai giá trị, xlpinyin và xlstroke. xlpinyin là giá trị mặc định.

Xlpinyin sắp xếp bằng cách sử dụng thứ tự sắp xếp tiếng Trung cho các ký tự. Xlstroke sắp xếp theo số lượng đột quỵ trong mỗi ký tự.

Nếu bạn ghi lại một macro sắp xếp, tham số này sẽ luôn được đưa vào mã và bạn có thể tự hỏi nó có nghĩa là gì. Tuy nhiên, trừ khi bạn đang xử lý dữ liệu bằng tiếng Trung, nó ít được sử dụng.

Worksheets("Sheet1").Sort.SortFields.Clear

Sử dụng một sự kiện nhấp đúp để sắp xếp dữ liệu bảng

Trong tất cả các chức năng mà Microsoft bao gồm trong các phương thức sắp xếp cho VBA, nó không bao gồm một phương tiện đơn giản để nhấp đúp vào tiêu đề cột và sắp xếp toàn bộ dữ liệu bảng dựa trên cột cụ thể đó.

Đây là một tính năng thực sự hữu ích để có, và thật dễ dàng để viết mã để thực hiện nó.

Worksheets("Sheet1").Sort.SortFields.Clear

Mã này cần được đặt trên sự kiện nhấp đúp trên trang tính chứa dữ liệu bảng. Bạn làm điều này bằng cách nhấp vào tên bảng tính trong cửa sổ Project Explorer (góc bàn tay trên bên trái của màn hình VBE), sau đó chọn ‘bảng tính trong lần thả xuống đầu tiên trên cửa sổ mã. Chọn ‘BeforedoubleClick, trong lần thả xuống thứ hai và sau đó bạn có thể nhập mã của mình.

Lưu ý rằng không có tên, phạm vi hoặc tham chiếu ô được mã hóa cứng vào mã này ngoại trừ việc di chuyển con trỏ sang ô A1 ở cuối mã. Mã này được thiết kế để có được tất cả thông tin cần thiết từ tọa độ ô mà người dùng đã nhấp đúp vào và kích thước của phạm vi dữ liệu bảng.

Không quan trọng phạm vi dữ liệu dạng bảng lớn như thế nào. Mã vẫn sẽ nhận tất cả các thông tin cần thiết và nó có thể được sử dụng trên dữ liệu được lưu giữ ở bất cứ đâu trong sổ làm việc của bạn mà không cần phải mã hóa trong các giá trị.

Giả định duy nhất được thực hiện là có một hàng tiêu đề trong dữ liệu bảng và phạm vi dữ liệu bắt đầu tại ô A1, nhưng vị trí bắt đầu cho phạm vi dữ liệu có thể dễ dàng thay đổi trong mã.

Bất kỳ người dùng sẽ được ấn tượng phù hợp với chức năng phân loại mới này!

Lập trình VBA | Bộ tạo mã không hoạt động cho bạn!

Mở rộng chức năng sắp xếp bằng VBA

Microsoft đã cho phép linh hoạt rất lớn trong việc phân loại bằng một loạt các tham số. Tuy nhiên, trong VBA, bạn có thể đưa điều này đi xa hơn.

Giả sử rằng bạn muốn sắp xếp bất kỳ giá trị nào với một phông chữ đậm lên đầu dữ liệu của bạn. Không có cách nào để làm điều này trong Excel, nhưng bạn có thể viết mã VBA để làm điều đó:

Worksheets("Sheet1").Sort.SortFields.Clear

Mã này hoạt động kích thước của phạm vi dữ liệu bảng bằng cách sử dụng đối tượng ’sử dụng và sau đó lặp qua tất cả các hàng trong đó. Khi một phông chữ đậm được tìm thấy, số 0 hàng đầu được đặt trước giá trị ô.

Một loại sau đó diễn ra. Vì loại theo thứ tự tăng dần, bất cứ thứ gì có số 0 ở phía trước sẽ lên đầu danh sách.

Mã sau đó lặp qua tất cả các hàng và xóa các số 0 hàng đầu, khôi phục dữ liệu về các giá trị ban đầu của nó.

Mã này sắp xếp bằng cách sử dụng các phông chữ đậm làm tiêu chí, nhưng bạn có thể dễ dàng sử dụng các đặc điểm tế bào khác theo cùng một cách, ví dụ, phông chữ chữ in nghiêng, kích thước điểm của văn bản, chữ dưới, tên phông chữ, v.v.