excel macro help... please

[cheekyblue]

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

 

[Kassie]

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

 

[Mike H]

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]

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

 

[cheekyblue]

Hi mike,

it was almost right but I managed to figure out the amendment myself. thanks
a million! x

 

[Mike H]

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]

Glad I could help

Hi mike,

it was almost right but I managed to figure out the amendment myself. thanks
a million! x

 

[cheekyblue]

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 :-/