Solved: concatenating & shifting cells

[zest1]

Hi,

can someone please help me with some VBA code that will search col "A" for any cells containing the word "Cost $" and concatenate it with the cell directly above it, which contains the word "Item". Those cells both also contain other words and figures, as do the other cells in col "A".

Secondly, in col "B", all the cells containing the word "Time: " needs to be shifted 1 cell/collumn over to the right.

I appreciate any help with this

[lucas]

Hi Zest1,
Would it be possible for you to upload an example without any personal info so we can get a better understanding of what your trying to accomplish. Just go to manage attatchments at the bottom of the post reply screen. It must be zipped to upload it.

[zest1]

Hi Steve,

it?s a friend?s personal projects ToDo file, she asked me to help but its beyond me, but here?s the basic structure. There is other date in those cells, but they each begin with the heading words as follows:

A | B
Date: Time: |

Task: | ToDo:

Item: | Source

Cost:

Notes:


A | B
Date: | Time:

Task: | ToDo:

Item: , Cost: | Source

Notes:

I need to correct one thing from my initial post, Date & Time are in the same cell in column ?A?, and Time needs to shift into column ?B?. Also, each listing set does not contain the same number of rows (sometimes there are no Notes rows, and sometimes there is a Reminders row too).

Thanks.

[matthewspatrick]

Silly question: why can't your friend simply make a table, with all the column headings in Row 1, and Rows 2 - 65536 representing her different to-do items?

[malik641]

Hey Zest1, Welcome!

Here's a little something...I think it will work for you. Just look at the example and then run the macro.

I'm sure it's not the best way, but it works (at least in the example I gave you)

[zest1]

Thanks a lot Joseph!

We will try it out tonight.

[zest1]

Joseph, we tried your code and it worked except for one thing which I was able to adjust. Deleting only the concatenated cells was shifting only col A up and leaving the contents of col B back so that whatever was in the rows below in col B was being left back and getting further out of sync with col A upon each deletion.

So I simply changed:
"cell.Offset(1, 0).Delete Shift:=xlUp"
to
"cell.Offset(1, 0).EntireRow.Delete Shift:=xlUp"
and it seems to work fine.

Thanks a lot for your help!

[malik641]

Yeah, I was wondering about that part...but I figured you would change the code to be more specific to your needs.

Glad to see you got it working And if the thread is solved, please mark it so by choosing "Mark Thread Solved" in the thread tools of this thread