Two Macros Please :)

sheiono · Apr 30, 2011

ray:

ray:
First...thanks alot for you people out there willing to help rookies like me...
I need help with a couple of macros...

 [FONT=&quot]Here's the first one...
When the date is entered in column L, I would like the value in the same row of column O to change to zero.

The second macro:
I would like to re-sort the remaining rows that have values in column O greater than zero lowest number to highest, renumbering the remaining lowest number left unchanged to the number 1 sequentially adding the next number (2,3,4,etc.). does this make sense?[/FONT]

ray:

sulakvea · Apr 30, 2011

hi, sorry didnt understand the second piece

this is the code for the first Q. this doesnt check if a date was entered - only checks that it was Col L that the entry was made into. goes into corresponding sheet module

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 12 Then Target.Offset(0, 3).Value = 0
End Sub

sheiono · May 1, 2011

Thank you SO much...it worked perfectly

Sorry I was not clear on the 2nd macro: I need to sort rows by column O, lowest to highest (greater than 0), then by column A (date). Let's say the the lowest number after sorting is 5. I would like the 5 to change to 1, then take the next lowest number..hypothetically 13, and change it to 2, the next lowest..24 and change it to 3, and so on until the sort is complete. Hope that makes more sense. I will also re-post this because it is WAY above my comprehension...

Again, thank you!

vmser · May 2, 2011

sheiono said:
ray:ray:
First...thanks alot for you people out there willing to help rookies like me...
I need help with a couple of macros...

 [FONT=&quot]Here's the first one...
When the date is entered in column L, I would like the value in the same row of column O to change to zero.

The second macro:
I would like to re-sort the remaining rows that have values in column O greater than zero lowest number to highest, renumbering the remaining lowest number left unchanged to the number 1 sequentially adding the next number (2,3,4,etc.). does this make sense?[/FONT]
ray:

first bit doesn't require a macro if you add a row.
Use row P to display the resulting value you wish, and hide column O.

In P1, enter the following formula:
=if(I1="";O1;"0")
and drag down.

While there's no date or so set in column I, it'll display the value for column O. When you set a value for the date, it'll show "0".
When removing the date your original value in column O will reappear in P, as you did not delete it. You will not have to run a macro to use this.

As for the second: use the built-in sort function (which you could use on column P now, if you followed the first step, column O other wise).
Just sort values on this column, in ascending order.
Find the first non-zero value, and enter = ROW()-20 (or whatever number you have to substract to make the value equal 1).
Drag down till then end. Values will be renumbered as you request.

Two Macros Please :)

sheiono

New Member

Excel Facts

sulakvea

Well-known Member

sheiono

New Member

vmser

Board Regular

Similar threads

Forum statistics

Share this page

Two Macros Please :)

sheiono

New Member

Excel Facts

sulakvea

Well-known Member

sheiono

New Member

vmser

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock