keeping a column in numerical order on workbook_change

Status
Not open for further replies.
H

hc

New Member
Joined
Dec 22, 2003
Messages
31
I need a simple Worksheet_Change macro that will do the following:

Code: 
A            B
January      1
.
.
.                <-- variable amount of rows
.
January      2
.
.
January      3   <-- variable amount, but always from 1 onwards
.
.
.
February     1
.
. 
February     2
.
.
February     3
.
.
March        1
.
.
.
etc. for the entire year

- If at any time I delete the lines that make up January 2, then January 3 must automatically become January 2, February etc. must remain as is.

- If I delete January 1, then January 2 must become January 1 and January 3 must become January 2.

- If I delete February 3, then do nothing.

Thank you.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
Try this, it will run on range A1:A500 - if you Jan dates go beyond this range the increase it. If your dates run in order (i.e. Jan, Feb Mar etc) then out the part that is notes i.e. If Month(c.Value) > 1 Then


Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Dim c As Range, counter As Long, mydate As Date
counter = 1
For Each c In Range("A1:A500")

'********************************************************
'is your dates run in order add these lines
'If Month(c.Value) > 1 Then
'Application.EnableEvents = True
'Exit Sub
'End If
'********************************************************

If IsDate(c.Value) = True And Month(c.Value) = 1 Then
If Day(c.Value) <> counter Then
mydate = Format(counter & "/01/" & Year(c.Value), "dd/mm/yy")
c.Value = mydate
counter = counter + 1
Else
counter = counter + 1
End If
End If
Next c
Application.EnableEvents = True

End Sub
 
Upvote 0
Thanks Jimboy for the quick response, however it didn't work for me.

Column A is only the month. The month value is filled in manually, so it is a string value. Column B is an integer only. It always runs from 1 onwards.

I might have January 1 until January 35, as the number does not relate to a date. If I then decide to delete January 5 manually, I want all the other numbers to fix themselves so that they remain in numeric order, so the result will be January 1 to January 34 (in this example). I am trying to avoid having to fix the numbers manually.

The counter must reset itself when it hits the next month, running from 1 onwards again.

The months will always be in the correct order.

Thank you.
 
Upvote 0
hc said:
I need a simple Worksheet_Change macro that will do the following:

Code: 
A            B
January      1
.
.
.                <-- variable amount of rows
.
January      2
.
.
January      3   <-- variable amount, but always from 1 onwards
.
.
.
February     1
.
. 
February     2
.
.
February     3
.
.
March        1
.
.
.
etc. for the entire year

- If at any time I delete the lines that make up January 2, then January 3 must automatically become January 2, February etc. must remain as is.

- If I delete January 1, then January 2 must become January 1 and January 3 must become January 2.

- If I delete February 3, then do nothing.

Thank you.
Click to expand...

hi!
does the blanck between the dates jan1 and jan 2 is jan 1 too. ?
 
Upvote 0
If you data starts in row 1 enter 1 in cell B1 and in B2 type:

=IF(COUNTIF(A$1:A2,A2)=1,1,MAX(IF(A$1:A1=A2,B$1:B1,0))+1)

making sure to press Ctrl+Shift+Enter not just Enter and copy down. This is an array formula and Excel will surround it with curly braces {} if correctly entered.
 
Upvote 0
Sorry, I assumed you had your dates in column A.

Try this, you will have to have the month in col. A (i.e. January not 01/01/04 formatted to "mmmm")

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Dim c As Range, counter As Long, mydate As Date
counter = 1
For Each c In Range("A1:A500")

'********************************************************
'if your dates run in order add these lines
If c.Value = "February" Then
Application.EnableEvents = True
Exit Sub
End If
'********************************************************

'If IsDate(c.Value) = True And Month(c.Value) = 1 Then
If c.Value = "January" Then
If c.Offset(0, 1) <> counter And c.Offset(0, 1) <> "" Then

c.Offset(0, 1) = counter
counter = counter + 1
Else
If c.Offset(0, 1) = counter Then counter = counter + 1
End If
End If
Next c
Application.EnableEvents = True

End Sub
 
Upvote 0
Jimboy, your solution is on the right track, but it doesn't take into account the other months of the year. It only takes care of January.

If I have Jan, Feb, Mar etc. with November spanning from 1 to 40, and I delete the lines that make up November 22, November 23 to 40 must fix themselves.

How about creating a Month as Variant array with:
Month(0) = "January"
.
etc.
Month(11) = "December"

Now you can use something like:
If c.Value = Month(Counter) Then ...
so that you can loop through the months of the year.
 
Upvote 0
How if you data entered into column A, is it text i.e. "January" or date i.e. 01/01/04 and then formatted "mmmm"?
 
Upvote 0
Try this;

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Dim c As Range, counter As Long, mydate As Date, mymonth As String, mynextmonth As String

mymonth = "January"
mynextmonth = "February"
counter = 1

myloop:
For Each c In Range("A1:A500")
If c.Value = mynextmonth Then Exit For
If c.Value = mymonth Then
If c.Offset(0, 1) <> counter And c.Offset(0, 1) <> "" Then
c.Offset(0, 1) = counter
counter = counter + 1
Else
If c.Offset(0, 1) = counter Then counter = counter + 1
End If
End If
Next c
If mymonth = "December" Then
Application.EnableEvents = True
Exit Sub
End If
If mymonth = "November" Then mymonth = "December": mynextmonth = "skip"
If mymonth = "October" Then mymonth = "November": mynextmonth = "December"
If mymonth = "September" Then mymonth = "October": mynextmonth = "November"
If mymonth = "August" Then mymonth = "September": mynextmonth = "October"
If mymonth = "July" Then mymonth = "August": mynextmonth = "September"
If mymonth = "June" Then mymonth = "July": mynextmonth = "August"
If mymonth = "May" Then mymonth = "June": mynextmonth = "July"
If mymonth = "April" Then mymonth = "May": mynextmonth = "June"
If mymonth = "March" Then mymonth = "April": mynextmonth = "May"
If mymonth = "February" Then mymonth = "March": mynextmonth = "April"
If mymonth = "January" Then mymonth = "February": mynextmonth = "March"
counter = 1
GoTo myloop

End Sub
 
Upvote 0
Status
Not open for further replies.

Similar threads

R
  • Solved
VBA Help to Automate Year
Replies
2
Views
125
RattlingCarp3048
R
M
  • Question
how to make highlights dependent on distance to today's date?
Replies
5
Views
665
etaf
etaf
O
  • Question
Trying to create a standby list
Replies
8
Views
344
Fluff
Fluff
J
  • Question
Formula Needed - Create a Multiplier w/o helper columns
Replies
2
Views
385
Fluff
Fluff
JohnZ1156
  • Solved
SUMIFS Column by Date Range
Replies
3
Views
414
Fluff
Fluff

Forum statistics

Threads
1,214,790
Messages
6,121,607
Members
449,037
Latest member
Arbind kumar

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top