Answered by:
Check if the column in the workseet is sorter in Descending order, if fails return that row.
Question

Hi All,
I am trying to get the code in VBA to check if the particular column in the sheet (tab) is sorter in descending order? If yes, then insert a row into a SQL table saying it is success, else insert a row in the same table with result as failure with the row number where it got failed on the first occurrence on the sheet.
Table structure:
CheckResults (CheckNo, Result, RowID)
Can someone help me getting the VBA code for this?
Rahul11
 Edited by Rahul 11 Wednesday, August 16, 2017 6:53 PM
Wednesday, August 16, 2017 6:52 PM
Answers

Rahul
re: is dated sorted?
I can't help you with SQL, but if you have data in a single column in an Excel worksheet...
The following VBA code will check the data sequence and return the row number where the sequence check fails.
NOTE: the failure row in the data range and the worksheet row number will only be the same if the data starts in the first row of the worksheet.
'
Sub CheckDescendingSort()
Dim N As Long
Dim rng As Range
Set rng = Range("H4:H20").Cells '<<< you must specify the correct range
For N = 2 To rng.Rows.Count
If rng(N).Value > rng(N  1).Value Then
'insert failure message into SQL table using
'the numberic N value as the row in the data.
'N is the row in the data range, not the row in the worksheet.
MsgBox "Failure at row " & N '<<< used for testing
Exit Sub
End If
Next
'insert success message into the SQL table
MsgBox "Data sorted descending" '<<< used for testing
End Sub
'
Jim Cone
Portland, Oregon USA
https://goo.gl/IUQUN2 (Dropbox)(free & commercial excel addins & workbooks) Marked as answer by Rahul 11 Monday, August 21, 2017 7:27 PM
Wednesday, August 16, 2017 11:08 PM
All replies

Rahul
re: is dated sorted?
I can't help you with SQL, but if you have data in a single column in an Excel worksheet...
The following VBA code will check the data sequence and return the row number where the sequence check fails.
NOTE: the failure row in the data range and the worksheet row number will only be the same if the data starts in the first row of the worksheet.
'
Sub CheckDescendingSort()
Dim N As Long
Dim rng As Range
Set rng = Range("H4:H20").Cells '<<< you must specify the correct range
For N = 2 To rng.Rows.Count
If rng(N).Value > rng(N  1).Value Then
'insert failure message into SQL table using
'the numberic N value as the row in the data.
'N is the row in the data range, not the row in the worksheet.
MsgBox "Failure at row " & N '<<< used for testing
Exit Sub
End If
Next
'insert success message into the SQL table
MsgBox "Data sorted descending" '<<< used for testing
End Sub
'
Jim Cone
Portland, Oregon USA
https://goo.gl/IUQUN2 (Dropbox)(free & commercial excel addins & workbooks) Marked as answer by Rahul 11 Monday, August 21, 2017 7:27 PM
Wednesday, August 16, 2017 11:08 PM 
This does works for me Jim, but there is actually more to it.
So, I need to check if the column is sorted in descending order based on the ID column for that particular column.
For example, I need to check if the Column [Total] is sorted in descending order based on the ID column of it. Here is the sample data look like,
ID Total
1 120
1 23
1 12
2 200
2 121
2 26
So, as shown above the [Total] column should be checked for the sorting and it is tied on the ID column. We need to check if the ID is same then do the SortScheck else go to next ID and do the check. And finally, if we find any failure then pull back that [ID] value.
Sub CheckDescendingSort() Dim N As Long Dim rng As Range Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets("1asheet") Set rng = ws.Range("S6:S2000").Cells For N = 2 To rng.Rows.Count If rng(N).value > rng(N  1).value Then 'insert failure message into SQL table using 'the numberic N value as the row in the data. 'N is the row in the data range, not the row in the worksheet. MsgBox "Failure at row " & N Exit Sub End If Next 'insert success message into the SQL table MsgBox "Data sorted descending" '<<< used for testing End Sub
Can you help me in this?
Rahul11
 Edited by Rahul 11 Thursday, August 17, 2017 2:14 PM
Thursday, August 17, 2017 2:14 PM 
Rahul,
re: sort problems
1. What/where/who is doing the existing sort?
2. Is the ID data strictly numeric and do the numbers align to the right in each cell?
3. Same question for the "total" data.
3. Has the data (all columns) been cleaned? ...
remove extra spaces, move trailing minus signs to the front, unmerge cells,
remove nonprinting characters and word wrap,
replace carriage returns and nonbreaking spaces with a spaceA standard Excel sort will do what you want (ID column sorted ascending, Total column sorted descending within each ID group); but it depends on what you present to Excel.
'
Jim ConeThursday, August 17, 2017 3:14 PM 
re: sort problems  more info
The point is one cannot determine if a sort is correct if the data is messy.
Excel sorts text separately from numbers. So...
1234 with a leading space sort sorts separately from 1234 without a leading space.
The first one is text and second is a number.Thursday, August 17, 2017 4:10 PM