Solved: Inrease a number in a column basd on condition

[kisinana]

Having a problem. I thought it was just auto numbering but it is more complicated it seems. I am working on a macro to sort information as it arrives in groups
I want to click a check box and change a condition to true - solved
Then run a macro which
Then have information sorted by true/false - solved
Then insert numbers beginning at 1 and increasing 1 at a time in a column only for each true statement. - Cannot get it to put in numbers
Then if more check boxes are clicked and the macro run again the new true statements will be sorted after those which are already numbered and the numbers carry on sequentially from the first numbers.

Any ideas much appreciated

[JONvdHeyden]

Hello Kisinana

Assuming you solved TRUE / FALSE is in column A, and indexing should take place in column B, then perhaps this will work for you:


Sub test()
Dim lID As Long, rCell As Range, rRng1 As Range, rRng2 As Range, lRow As Long
lRow = Range("A" & Rows.Count).End(xlUp).Row
Set rRng1 = Range("A1:B" & lRow)
Set rRng2 = Range("B2:B" & lRow)
lID = 0
On Error Resume Next
lID = Application.Max(rRng2.SpecialCells(xlCellTypeConstants, 1))
On Error GoTo 0
With rRng1
.AutoFilter Field:=1, Criteria1:=True
.AutoFilter Field:=2, Criteria1:="="
For Each rCell In rRng2.SpecialCells(xlCellTypeVisible)
rCell = lID + 1
lID = lID + 1
Next rCell
.AutoFilter
.Sort key1:=Range("B1"), header:=xlYes
End With
End Sub


You will need to change the ranges so that it works for your construct.

[kisinana]

If my True/False is in column D from D2 to D73 and I want the numbers in E, does rRng1 become "D1: D73" and rRng2 becomes "E1:E73" because this is what I tried and it does not enter any value nor do I get any errors showing?

[JONvdHeyden]

Hi

I'vve adjusted the ranges, I hope it fits your requirements. Please see my comments in the code, I hope it can help you understand what it does:

Sub test()
Dim lID As Long, rCell As Range, rRng1 As Range, rRng2 As Range, lRow As Long

'//assumes last row in your workbook can be determined by the last occupied row in column D //
lRow = Range("D" & Rows.Count).End(xlUp).Row

'// assumes that you have column labels in row 1, TRUE / FALSE in column D and results to be returned to column E //
Set rRng1 = Range("D1:E" & lRow) '// Rng1 needs to include column labels and must include bothe column D and E //
Set rRng2 = Range("E2:E" & lRow) '// Rng2 should EXCLUDE column labels and refers to the results column only (E) //

'// this bit works out what the last index number was, or, if none have been indexed yet then it should be zero //
lID = 0
On Error Resume Next
lID = Application.Max(rRng2.SpecialCells(xlCellTypeConstants, 1))
On Error GoTo 0

With rRng1
.AutoFilter Field:=1, Criteria1:=True
.AutoFilter Field:=2, Criteria1:="="
For Each rCell In rRng2.SpecialCells(xlCellTypeVisible)
rCell = lID + 1
lID = lID + 1
Next rCell
.AutoFilter
.Sort key1:=Range("E1"), header:=xlYes '//this needs to point at the column label for the results column (Index #) //
End With
End Sub

[kisinana]

Thanks Jon
This fixed my problem. I especially want to thank you for the inset explanations, they helped me understand the sections of the code that I was not familar with and to understand how they work.

Kisinana