PDA

View Full Version : Solved: concatenating & shifting cells



zest1
12-06-2005, 11:22 AM
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
12-06-2005, 11:30 AM
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
12-06-2005, 01:35 PM
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
12-06-2005, 03:49 PM
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
12-06-2005, 04:40 PM
Hey Zest1, Welcome! :hi:

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) :thumb

zest1
12-06-2005, 05:00 PM
Thanks a lot Joseph!

We will try it out tonight.

zest1
12-06-2005, 09:56 PM
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
12-06-2005, 09:59 PM
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 :thumb And if the thread is solved, please mark it so by choosing "Mark Thread Solved" in the thread tools of this thread :)