Can someone help me make this code work? Moving cell contents if cell has date format...

[Chad Bateman]

Hello,
I've tried to assemble some of this code based on other VBA code I have that works, but as you can see, there are some gaps (the date format section, the AND part).

I basically just want the Macro to cut and copy the value of certain cells into other cells based if any cell in column A has a date. I've attached a spreadsheet with a before and after, and here is the code I TRIED to assemble.

Thanks in advance for your help,

Chad

Help1.xlsm

Please Login or Register  to view this content.

[Norie]

Do you have blocks of data that are delimited by dates and you want to move those blocks of data over to the next column?

[Chad Bateman]

I have to be honest- I don't think I'm excel savvy enough to answer that question (I don't understand it).

Maybe this description will be better:
I have tons of data that is organized very poorly, all in column A. The only thing seperating each section/packet of data is a date. So I want the code to find any time there's a date in row (any date at all as long as it's mm/dd/yyyy format), and move other data elements relative to the cell that has the date.

So...something like "if any cell in column A has a date, move the contents of the cell below the cell with the date to the cell to the right of the cell with the date."

Does that help? Sorry, if it didn't can you clarify (aka dumb down) your question so I can try again?

Thanks for your help.

[Norie]

Sounds like you do have what I was suggesting.

I came up with this which works with sample data similar to that in the workbook you uploaded.

Please Login or Register  to view this content.

[Chad Bateman]

That's awesome! Thanks! Can you help me figure out how to add more conditions (cells that need to be moved based on the places of the cell with a date format) that may not always be consistent?

For example (not asking anyone to build this, just pasted for the sake of illustration),

MOVE.Offset(3, 0).Cut MOVE.Offset(1, 1)
MOVE.Offset(4, 0).Cut MOVE.Offset(3, 2)
MOVE.Offset(6, 0).Cut MOVE.Offset(4, 1)
MOVE.Offset(7, 0).Cut MOVE.Offset(5, 1)
MOVE.Offset(8, 0).Cut MOVE.Offset(6, 1)
MOVE.Offset(9, 0).Cut MOVE.Offset(7, 1)
MOVE.Offset(13, 0).Cut MOVE.Offset(0, 2)
MOVE.Offset(14, 0).Cut MOVE.Offset(0, 3)

Thanks again,

Chad

[Chad Bateman]

I hope I didn't confuse things by my last post (I re-read it and thought it was unclear).

I'm just looking for some tips on how the above code can be modified in a way that I can reference different cells to be moved to different places (in relation to the cell that has the date). For example, one cell might need to be moved 3 cells to the right, and the next cell might need to be moved 6 cells to the right.

I hope that's more clear.

[Chad Bateman]

Here is a spreadsheet that I think will help explain better:

Help3.xlsm

[Norie]

Is the data always structured in the same way?

[Chad Bateman]

Yup, every time. Thanks again Norie.

[Chad Bateman]

Slight clarification:
The cell with the date will be in a different row (but always column A)- for example cell A2, cell A20 in the spreadsheet I attached. The data below the date cell (everything in yellow) will always be structured the same way.

[Norie]

This will move the data.

Please Login or Register  to view this content.

[Chad Bateman]

Thanks for putting this together.

Is this still making the change based on whether that cell is a date? The process will need to be repeated hundreds of times, but the date in the A Column is always the constant that everything else is structured around. When I run this Macro on my actual data it makes the changes for the first two "packets" of data, but any packets below that (I included 2 in the sample spreadsheet, but there will be hundreds). The cells with dates may not always be the same distance from each other either, one might be in cell A2, the next in A20, and the next in A50- but the data below (deposit, deposit reference #, Source, Amount, etc.) will always be the same distance relative to the date cell.

Also-
which parts will I change if I need to add/change which cells get moved where? (I will need to modify it heavily).

Thanks for the code you posted- I've just got figure out how to modify it to work as needed.

[Norie]

It's not relying on the dates, I've assumed that the rest of the data would be structured as the sample and the layout of the packets is consistent, ie the same no of rows between.

If those 2 assumptions are wrong then the code will need to be changed.

The date thing is easy enough to handle, but I'm not sure about the rest, especially since you mention 'modify...heavily.

What will need be modified?

What the code I posted does is put each single column packet of data into an array and then transfers the values from that array into the relevant rows/columns in a 3 column array.

Once that's done we write the 3 column array back to the sheet, overwriting the original data.

[Chad Bateman]

I see.

The date part is important- the number of rows between will definitely vary, so the cell with the date has to be used to initiate everything else.

As for the "modify heavily" part, below is what I started trying to map out. I guess "MOVE" would represent the cell with the date- I can change the name to whatever.

Below are the cells that need to be moved, and where they need to be moved to, in relation to the date cell. I didn't post all of this initially because I didn't want someone to have to go through and build this whole thing. I was looking for some code that I could modify pick apart and modify to accomplish this (it looks like in trying to make this easy for you I made it much harder- sorry).

If "Move" = Date, then
MOVE.Offset(1, 0).Cut MOVE.Offset(0, 1)
and
MOVE.Offset(3, 0).Cut MOVE.Offset(1, 1)
and
MOVE.Offset(5, 0).Cut MOVE.Offset(3, 1)
and
MOVE.Offset(6, 0).Cut MOVE.Offset(4, 1)
and
MOVE.Offset(7, 0).Cut MOVE.Offset(5, 1)
and
MOVE.Offset(8, 0).Cut MOVE.Offset(6, 1)
and
MOVE.Offset(9, 0).Cut MOVE.Offset(7, 1)
and
MOVE.Offset(13, 0).Cut MOVE.Offset(0, 2)
and
MOVE.Offset(14, 0).Cut MOVE.Offset(0, 3)
and
MOVE.Offset(24, 0).Cut MOVE.Offset(0, 11)
and
MOVE.Offset(26, 0).Cut MOVE.Offset(0, 13)

continue until no more dates are found

[Norie]

Like I said, the date part shouldn't be a problem.

All we would need to do for that would be to loop down the column and then when we find a date we move the relevant data for that date.

Once that's done we move on to the next date, move the data for that, and so on.

Could you mock up some data with a little less uniformity?

By the way, is there really only one column of data?

[Chad Bateman]

Sure, I'll put together a much better example and post it shortly.

Thanks again.

[Chad Bateman]

Ok, here's a spreadsheet with a before and after. I put 3 data "packets" in, but there will be hundreds of packets, and the # of rows between packets will not be consistent.

Yes, the data really is in 1 row- crazy, right?

Hopefully this spreadsheet is more clear (this one is an exact version what I'd need).

Thanks!

Help4.xlsm

[Norie]

Chad

That looks like something I can adapt the code I've posted so far for.

Where do you want the results?

Would you want them on a new sheet?

[Chad Bateman]

Yay! Awesome! Yes, on the same sheet please.

You've made my day!

[Norie]

I've set this up so the result goes to a new sheet, mainly for debugging purposes, but that can be changed.

It probably needs a few tweaks too, eg headers added, column A filled in etc., but that can be taken care of.

Give it a shot though and see how it works.

Please Login or Register  to view this content.

[Chad Bateman]

Norie,
I've been working with this, and so far everything it's working perfectly. 2 questions-
-How can I change it so that it will make the changes to the same tab (instead of moving the contents onto a new tab)?
-How can I change it to cut (or delete contents) to the cell directly above the date cell? There will always be a number above the date cell, and I didn't mention it before because I thought it wouldn't impact the final result, but it looks like it's stopping the code from running through all the data packets (but the code works great after I manually delete the numbers above the date cell).

Other than those two things, I think it's perfect. Seriously, awesome. Thanks again.

[Norie]

Can you upload a workbook that includes these numbers in the data?

The problem with those numbers is that even though they aren't actually dates VBA could be regarding them as dates, and that's going to throw things off.

I have a couple of ideas about how to handle them but I'd need to see them in place.

As for having the results on the original sheet, that should be straightforward.

I was only using the new sheet for debugging really, if I'd used the original sheet I would have needed to recreate it multiple times as I tested the code.

PS These numbers, do you want to keep them?

[Chad Bateman]

Here's how it would look. I don't see the numbers above the dates EVER being over 4 digits, if that helps. I'd like to remove the numbers, I don't need them.

Thanks,

Chad
Help5.xlsm

[Norie]

How's this?

Please Login or Register  to view this content.

[Chad Bateman]

I just tested it for a few minutes and it was perfect. I'll test it a little more tomorrow (once I've had some sleep), but it looks like we're set!

You are my VBA Hero!

[Chad Bateman]

Norie- the last code you posted is perfect, and I've already implemented it into daily use. Thanks again for helping me solve this!