Need a macro that allows me to place text across multiple columns? (Excel example included

Looks like a sheet_change event should work. Can you:

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Thanks for the reply! I have uploaded it via your method, but I had to delete a lot of rows of sample data (nothing important) you should still be able to see what I'm talking about. I'm not sure if a before/after version would help, to see what I want look at rows 12,15,18,21,24,27,30,33,36 of the 'Main Sheet'. There is one cell with a comment that kind of shows what it would look like if text could overwrite adjacent cells.

One thing that got lost since I had to delete info to upload this way was that I had Bay 9 and 10 having some work that lasted only a week in a few places. I mention that in case there is a way to do what I said above where the macro would only print the formula in the cell if the cells below repeat the same result for X number of days.

I'm not feeling very bright, that is, I'm not sure exactly what it is that you want

What you want the macro to do and what is the data.

ok

Unless you can think of an easier way this is what I want the macro to do:

1.) Run the macro on specific rows (12,20,28,36,44,52,60,68,76,94 is what I currently need but I know that doesn't match what I have attached. It's just every row one row above where each block of schedule/bay is) beginning at column E. An ending column isn't necessary but if it must be specified, column NG is the last column.

2.) For every cell on these rows within that range:

* If the cell one row down on the same column is blank (FYI it is not technically blank it is made blank by a formula) OR if the cell one row down on the same column equals the cell one row down and one column to the LEFT, then clear that cell.
*If not, then:

Make the cell have this formula:

=IFERROR(INDEX('By Line'!$C$4:$C$236,MATCH($B12&TRUE,'By Line'!$B$4:$B$236&'By Line'!Q$4:Q$236,0)),0)

Where $B$12 must change so that the '12' will always equal whatever row number the macro is looking at at the time. So if its run on row 20, the formula will change to $B$20.

I think my logic there is ok, essentially I want it only print the formula when the cell below changes to a non zero new value. And again, all of this is so that I can have text display over multiple columns automatically.

One final note: if possible, I would like it to do a check before it prints out the formula. The check would be: only print out formula if the cell one row down on the same column has the same value for the next 20 columns. If not, do not print out the formula AKA make it blank. (Sometimes I have some blocks of time that only last a week or so, and I don't want to have a description above those)


Does that make sense? I'm not sure what other data to give you other than what I already attached.

Thank you for reading!! Please let me know if you have further questions or if I can help in any way!

Hi Katie,

It looked like a scary chore with those array formulas - so, this guru is trying a "better way"

Let me know a few things that are wrong for this first attempt:

Please Login or Register  to view this content.

Katie, what is the main sheet supposed to look like? The macro seems to be duplicating what's there

Hi xladept,

Maybe I should just make a new thread because I wasn't exactly sure how to explain what I wanted when I made this one. Here is a (hopefully) concise recap of what I'm trying to accomplish:

I'm trying to write a macro that uses IF statements to solve the problem where text can't overflow into adjacent columns if there is a formula in the next columns. So I'm just trying to have a description above each block of time, I can't use formulas for this because of the reason I just mentioned.

I'm trying to make this car repair schedule:

First image below

Look like this:

Second image below

The example excel file is attached.

The yellow cells in column B are the rows where I need a macro. But note that I have made everything static, there are supposed to be formulas in the other two lines (rows 13 and 15, rows 23 and 25, etc).

I'm trying to have above each colored block of time, a description of the work below, as long as the block is 30 or more days long. The description (where the macro will pull the data from) is simply in the cell above, and the block of time is in the cell below, with the macro line in between both.

I think the logic I need is:

Run the macro on specific rows (14,24,34,44,54,64,74,84,94,114) beginning at column F. An ending column isn't necessary but if it must be specified, column NG is the last column.

For every cell on these rows within that range:

If the cell one row down on the same column is blank (FYI it is not technically blank but is made blank by a formula) OR if the cell one row down on the same column equals the cell one row down and one column to the LEFT, then clear that cell.

If not, then:


Make the cell equal the cell directly above (which will have the description).

One last condition: The cell should only equal the cell directly above if the block of time below repeats for 30 or more days (which are the same as columns in my spreadsheet). If you look at my images this just makes it so that a description doesn't appear above the block of time if the block of time is too short to really show anything.



I think the above will give me what I have in my second image. Does that make sense? I've tried writing this using some if statements I've found on the internet and using the macro recorder for the other parts but I can't get close to anything workable.

The before sheet has no information on it?

I'm not sure what you mean exactly? The before sheet is the same as the after sheet except that the after sheet has the descriptions above the colored sections. The before sheet is what should be used to write the macro, the after sheet is what the before sheet should look like after the macro has been run on it.

If you mean in comparison to the files I previously uploaded in this thread, what I did was just strip out all of the formulas and made everything static. I did it to make it more clear, it shouldn't affect the macro.

Hi Katie,

I saw all that - but, my first attempt was in the interest of a "better way" - but you just want to use formulas; correct?

Are they actually formulas or just text?

Ahh man, I'm confused as well now I don't completely understand the code you posted or what you mean by better way, which is why I don't understand what you're getting at.


I am still using the long index match formulas, but I have made those lines static in my new example. But I'm not sure it matters whether they are static or not? If you have a cell equal another cell using a macro, does it matter if that cell is a formula or static? I mean the macro will look at those two lines above and below the lines the macro is run on for two reasons:

1.) The macro will make a cell to be equal to the cell above it follows certain conditions.

2.) The macro will compare the cell below to the previous cell on the same row to see if they are equal to each other.

Does it matter if those cells are static or formulas?

I might be as confused as everyone else, but see if my effort below contributes (positively) to progress. I used your original sample and created a worksheet activate event. Every time you switch to the Master Sheet, it will look in column B for entries that match column B on your Feeder sheet, thereby identifying the Bays. In the row above each Bay, it should insert the description in the cell that aligns with the start date, but only if the date duration is longer than 20 days. That was my interpretation of the request; hopefully I'm close? Tying the changes to the dates on the feeder sheet made more sense to me than designing them around the formulas used for the bars on the Master sheet. I toyed with some of your sample data and it appeared to hold up. Take a look at the attachment to see if it works as desired:

Please Login or Register  to view this content.

EDIT: It'll need some modifying if it's going to apply to multiple blocks on the same bay. How will Bays with multiple start/end dates appear on your Feeder sheet?

Here's an alternative that I think will work with multiple entries in the same bay. This one is based off of the data on the main sheet. Wherever it sees the start of a stretch of 20 or more days with values greater than 1 on a bay row of the main sheet, it implants the description from the feeder sheet above the first entry. It will be triggered whenever the main sheet is activated. It's not tremendously efficient, so if you have tons of bays, this version may take a few seconds to update, but with only 9 bays it's nearly instantaneous. It seems to be testing cleanly for me, but take a look at the attachment to see if it'll do.

N.B.: In the attachment, I changed some of your formulas on the bay rows of the main sheet into values so I could more easily test the functionality of the procedure. The procedure works fine on both values and formula-generated results, but I didn't want to guess as to how you applied multiple blocks to your chart.

Please Login or Register  to view this content.

My second effort should adapt pretty cleanly to the new sample. I changed the name of the "After Macro" sheet to "Master Sheet" and went with the following:

Please Login or Register  to view this content.

It still triggers each time you activate the sheet. Try the attachment to see if it works.

EDIT: It should be noted that changing the name was meaningless. Just habit. As long as the event is applied to the correct sheet, the sheet name is meaningless.

It's not easy to understand what you mean but I needed some Excel time so I just made something.

I like arrayformulas but here they were just slowing things down while doing mostly nothing so I went for VBA.

Forgot to mention, it ads the text you want but when there is not enough room it ads it in a comment instead.