Just learning the tricks of the trade!

[JJRudd]

Okay, I've, searched the entire board, read through a ton of questions, but I'm am too new at Excel to completely understand some of the answers and then adapt them to my situation.
This is a sample of what I have.
A B C D E F G ........Q
Name|position|Supv Name|Supv Began| hired |Last report|Proj next|
Bob | desk | Sammy | 2Feb03 |2 Feb 03| 5Feb05 |5Feb06 |

I have a formula that updates G whenever I update a date in F. It automatically bumps out G by a year. I'm wanting the program to automatically sort using column G, having the closest Projected date being on top. How can I make this happen? Each row needs to stay together. ie: A5-Q5 stays together. A6-Q6, etc. Any help would be appreciated.

 

[JJRudd]

That worked like a champ. Thanks a lot.

 

[JJRudd]

Actually, I have on problem. After I make my changes and it sorts itself, when I try to save, it pops up saying I ned to debug the code. It highlights the RNG1.Select in yellow and won't let me save. Now, if I just push the END button on the error screen, all is well, but what do I need to do so it won't pop up at all?


Does any of what i typed make sense???

 

[just_jon]

Can you post the code?

 

[colbymack]

just_Jon,
It was on posting page 1 for this listing. Here it is:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim RNG1 As Range


Set RNG1 = Range("A1:IV65536")
RNG1.Select
Selection.Sort Key1:=Range("H1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal


End Sub

 

[colbymack]

JJRudd,
You may want to go ahead and post your changed code. When I use the code I gave you, it still allows me to save without any problem. Also, it will give Just_Jon a better frame of reference than mine did (hopefully) - He is much more advanced than I and might be able to tell you exactly what is wrong.

Good luck,
Colbymack

 

[JJRudd]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim RNG1 As Range

Set RNG1 = Range("A4:Z100")
RNG1.Select
Selection.Sort Key1:=Range("G4"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub

Here is my code. When I try to save, it throws up the debug screen and the RNG1.Select is highlighted in yellow. Now, if I push END on the debug screen, it does save it right, it's just kind of annoying.

 

[colbymack]

I have Excel 2002 SP3, and it seems to save just fine (granted, I don't have all of the data that you do). I know that doesn't help you any.

What version do you have?

 

[JJRudd]

I have Excel 2003 SP2.

 

[JJRudd]

Anybody have a fix for this?

 

[colbymack]

Does this sound like it? You didn't post an error message - only that the code was highlighted in yellow, so I did a little poking around and found this link:
http://support.microsoft.com/?scid=kb;en-us;292476&spid=2512&sid=global