Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel macro help... please
Hi there,
I have some data that I need quickly sorting so i am hoping there is a macro out there for me. I'll try to use something simple as an example Column A contains all the dates in April 1-30 Column B contains various random dates in April Column C contains number of sales for the dates in Column B I want a macro that checks if the date in A1 matches B1 If it does then move onto A2 and B2 if it does not match then is inserts cells into B1 and C1 until a match is found I need it to loop until the end of the dates in Column A Can anyone help? Thanks Charlotte |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel macro help... please
What do you mean by "insert cells into B1 and C1"?
Adding empty cells will never result in a match? -- HTH Kassie Replace xxx with hotmail "cheekyblue" wrote: Hi there, I have some data that I need quickly sorting so i am hoping there is a macro out there for me. I'll try to use something simple as an example Column A contains all the dates in April 1-30 Column B contains various random dates in April Column C contains number of sales for the dates in Column B I want a macro that checks if the date in A1 matches B1 If it does then move onto A2 and B2 if it does not match then is inserts cells into B1 and C1 until a match is found I need it to loop until the end of the dates in Column A Can anyone help? Thanks Charlotte |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel macro help... please
Hi,
I have made some assumptions:- 1. There are no duplicate dates in column B 2. Column B is sorted right click your sheet tab, view code and paste this in and run it. Sub sortem() Dim MyRange As Range lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row Set MyRange = Range("B1:B" & lastrow) For Each c In MyRange If c.Value < "" And c.Value c.Offset(, -1).Value Then c.Insert Shift:=xlDown End If Next End Sub Mike "cheekyblue" wrote: Hi there, I have some data that I need quickly sorting so i am hoping there is a macro out there for me. I'll try to use something simple as an example Column A contains all the dates in April 1-30 Column B contains various random dates in April Column C contains number of sales for the dates in Column B I want a macro that checks if the date in A1 matches B1 If it does then move onto A2 and B2 if it does not match then is inserts cells into B1 and C1 until a match is found I need it to loop until the end of the dates in Column A Can anyone help? Thanks Charlotte |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel macro help... please
Sorry Kassie, hard to explain, i'll try again
This is what i currently have column A Column B Column C Date Date Tot Sales 01/04/09 02/04/09 4 02/04/09 03/04/09 10 03/04/09 05/04/09 6 04/04/09 05/04/09 I'd like the macro to insert cells in b and C until it looks like the below Date Date Tot Sales 01/04/09 02/04/09 02/04/09 4 03/04/09 03/04/09 10 04/04/09 05/04/09 05/04/09 6 does that make more sense? I want gaps where there are no sales but I still want to see the date, after the macro I will be deleting column B so that i have all the dates and all the sales Thanks Char "Kassie" wrote: What do you mean by "insert cells into B1 and C1"? Adding empty cells will never result in a match? -- HTH Kassie Replace xxx with hotmail "cheekyblue" wrote: Hi there, I have some data that I need quickly sorting so i am hoping there is a macro out there for me. I'll try to use something simple as an example Column A contains all the dates in April 1-30 Column B contains various random dates in April Column C contains number of sales for the dates in Column B I want a macro that checks if the date in A1 matches B1 If it does then move onto A2 and B2 if it does not match then is inserts cells into B1 and C1 until a match is found I need it to loop until the end of the dates in Column A Can anyone help? Thanks Charlotte |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel macro help... please
Hi mike,
it was almost right but I managed to figure out the amendment myself. thanks a million! x "Mike H" wrote: Hi, I have made some assumptions:- 1. There are no duplicate dates in column B 2. Column B is sorted right click your sheet tab, view code and paste this in and run it. Sub sortem() Dim MyRange As Range lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row Set MyRange = Range("B1:B" & lastrow) For Each c In MyRange If c.Value < "" And c.Value c.Offset(, -1).Value Then c.Insert Shift:=xlDown End If Next End Sub Mike "cheekyblue" wrote: Hi there, I have some data that I need quickly sorting so i am hoping there is a macro out there for me. I'll try to use something simple as an example Column A contains all the dates in April 1-30 Column B contains various random dates in April Column C contains number of sales for the dates in Column B I want a macro that checks if the date in A1 matches B1 If it does then move onto A2 and B2 if it does not match then is inserts cells into B1 and C1 until a match is found I need it to loop until the end of the dates in Column A Can anyone help? Thanks Charlotte |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel macro help... please
OOPs,
I forgot column C had to move doen also, try this Sub sortem() Dim MyRange As Range lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row Set MyRange = Range("B1:B" & lastrow) For Each c In MyRange If c.Value < "" And c.Value c.Offset(, -1).Value Then c.Resize(, 2).Insert Shift:=xlDown End If Next End Sub Mike "Mike H" wrote: Hi, I have made some assumptions:- 1. There are no duplicate dates in column B 2. Column B is sorted right click your sheet tab, view code and paste this in and run it. Sub sortem() Dim MyRange As Range lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row Set MyRange = Range("B1:B" & lastrow) For Each c In MyRange If c.Value < "" And c.Value c.Offset(, -1).Value Then c.Insert Shift:=xlDown End If Next End Sub Mike "cheekyblue" wrote: Hi there, I have some data that I need quickly sorting so i am hoping there is a macro out there for me. I'll try to use something simple as an example Column A contains all the dates in April 1-30 Column B contains various random dates in April Column C contains number of sales for the dates in Column B I want a macro that checks if the date in A1 matches B1 If it does then move onto A2 and B2 if it does not match then is inserts cells into B1 and C1 until a match is found I need it to loop until the end of the dates in Column A Can anyone help? Thanks Charlotte |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel macro help... please
Glad I could help
"cheekyblue" wrote: Hi mike, it was almost right but I managed to figure out the amendment myself. thanks a million! x "Mike H" wrote: Hi, I have made some assumptions:- 1. There are no duplicate dates in column B 2. Column B is sorted right click your sheet tab, view code and paste this in and run it. Sub sortem() Dim MyRange As Range lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row Set MyRange = Range("B1:B" & lastrow) For Each c In MyRange If c.Value < "" And c.Value c.Offset(, -1).Value Then c.Insert Shift:=xlDown End If Next End Sub Mike "cheekyblue" wrote: Hi there, I have some data that I need quickly sorting so i am hoping there is a macro out there for me. I'll try to use something simple as an example Column A contains all the dates in April 1-30 Column B contains various random dates in April Column C contains number of sales for the dates in Column B I want a macro that checks if the date in A1 matches B1 If it does then move onto A2 and B2 if it does not match then is inserts cells into B1 and C1 until a match is found I need it to loop until the end of the dates in Column A Can anyone help? Thanks Charlotte |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel macro help... please
thats ok mike, I managed to figure that out by myself. i understand macros
when they are in front of me but i just dont know how to write them :-/ "Mike H" wrote: OOPs, I forgot column C had to move doen also, try this Sub sortem() Dim MyRange As Range lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row Set MyRange = Range("B1:B" & lastrow) For Each c In MyRange If c.Value < "" And c.Value c.Offset(, -1).Value Then c.Resize(, 2).Insert Shift:=xlDown End If Next End Sub Mike "Mike H" wrote: Hi, I have made some assumptions:- 1. There are no duplicate dates in column B 2. Column B is sorted right click your sheet tab, view code and paste this in and run it. Sub sortem() Dim MyRange As Range lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row Set MyRange = Range("B1:B" & lastrow) For Each c In MyRange If c.Value < "" And c.Value c.Offset(, -1).Value Then c.Insert Shift:=xlDown End If Next End Sub Mike "cheekyblue" wrote: Hi there, I have some data that I need quickly sorting so i am hoping there is a macro out there for me. I'll try to use something simple as an example Column A contains all the dates in April 1-30 Column B contains various random dates in April Column C contains number of sales for the dates in Column B I want a macro that checks if the date in A1 matches B1 If it does then move onto A2 and B2 if it does not match then is inserts cells into B1 and C1 until a match is found I need it to loop until the end of the dates in Column A Can anyone help? Thanks Charlotte |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Avoid Outlook macro security for Excel bades macro | Excel Programming | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Need syntax for RUNning a Word macro with an argument, called from an Excel macro | Excel Programming | |||
translate lotus 1-2-3 macro into excel macro using excel 2000 | Excel Programming |