PDA

View Full Version : Solved: VBA for sorting column with formulas



rkselby98
06-25-2008, 06:32 PM
In a check book ledger in Excel.

The first column is the check numbers - the column is set up with the formula A1+1 so with each entry it add the new check number for him.

Now he like to do a sort by Description or Who at times but when he does a sort the Check Numbers all change because of the formula and basically that column doesn't sort with the

Is there a way to get that column to "sort" with the rest and override the formula so the check numbers sort with the rest of the fields?

Sample file Attached.

Thank you and all have a great day and God Bless,

lucas
06-25-2008, 08:18 PM
Try this. It is sheet change code that autonumbers column A. Just add another supplier and hit enter. Then try to sort.....let me know if it works. I tried it and it seemed ok.

This is sheet change code. To add it to a workbook you right click on the sheet tab and select "view code" paste this code in that module:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim tRows As Long
Dim i As Long
If Target(1, 1).Row < 1 Or Target(1, 1).Value = vbNullString Then Exit Sub
tRows = Target.Rows.Count
For i = 1 To tRows
Cells(Target(i, 1).Row, 1).Value = Target(i, 1).Row - 1
Next i
End Sub

rkselby98
06-26-2008, 03:47 PM
Steve,

Thanks a bunch. Worked like a charm.

Sorry it took so long to get back to you but I tried it on my test file and it worked but I wanted to see if it was going to work in the actual file so I submitted this to the owner of the file and was waiting to hear from him.

He got back to me happy as a Lark so I can't say thanks enough for you solved a problem I had no idea how too.

Thanks and I know that?s really not much but all I can afford so I hope a great big thank you will take care of the hard work you put into helping us out.

Have a great day and God bless you and yours.
:thumb

lucas
06-26-2008, 04:11 PM
You're very welcome.