Add formula next to every adjacent cell (except blanks)

[2vbr]

Doing this manually, I paste a Table-referencing formula into an empty cell next to a cell of data, then I double-click that little square box in the bottom corner to auto-fill all the way down.

Problem is, there is the occasional blank, so I just skip down and paste the formula again, auto-fill, and repeat until the formula is next to all cells in the A1 column.

I've tried using Record Macro to automate this, unfortunately it can't record "autofill next to ALL cells in the column", only to end at a specific point. ie. B1:B300

I've actually found several google results asking a VBA question similar to this, but they're all how to autofill only down to the last row (with no skips), while I need the formula pasted into ALL cells in column B (but only if there's data in column A)

I hope I'm explaining right. The result should be like this:

Data	=IFERROR(INDEX(Table1...
Data	=IFERROR(INDEX(Table1...
Data	=IFERROR(INDEX(Table1...
‎	‎
Data	=IFERROR(INDEX(Table1...
Data	=IFERROR(INDEX(Table1...
‎	‎
Data	=IFERROR(INDEX(Table1...

The VBA macro is basically:

1. Select cell B1
2. Paste [Formula]
3. Paste same [formula] next to any cell in column A that has data.

[TMS]

Why do you have occasional blanks? Sounds like mixing data with presentation.

[2vbr]

I don't make the workplace rules.

I just have to deal with what I'm given, and leave the blanks untouched.

[TMS]

Post a sample workbook.

[2vbr]

Here we go.

https://filebin.net/iyxa8gfcoiw2h2re

(Spreadsheets are usually muuuuch bigger, with more data... but you get the idea. Again, I'm not allowed to shuffle data around for better presentation. I only have to add the formula to the right of the data.

I actually have to do the same with other columns, but if I can get one simple VBA macro to do it once, I can just change column "A" and "B" to whatever I need.


...


Um... I can't seem to get the Attachment option working.


JBClR7g.png

Is something supposed to happen when I click the "Attachments" button? Tried in both Firefox and Chrome.

Anyway, I uploaded the sample elsewhere (link at top).



EDIT: Okay, got the site-hosted links working via Manage Attachments. Took a few edits.

[Rick Rothstein]

Give this macro a try...

Please Login or Register  to view this content.

EDIT NOTE: This question has been multi-posted over in the MrExcel forum.

[2vbr]

It works! Much appreciated!

This question has been multi-posted

Is that a bad thing?

I haven't been here that long, and I'm afraid I haven't yet picked up on any rivalries, or unspoken community faux pas.

[Rick Rothstein]

It is not a "rivalry" thing... it is a fairness thing. Think about it from the perspective of the people you asked to help you. You post on two (or more) different forums but do not tell either that you did that. On one site, you get a solution. On the other site, someone later comes along and works on the problem eventually coming up with a solution and posts it. That person doesn't know he/she just wasted valuable time they could have spent answering someone else's question because they spent it answering your question which you no longer care about because you got a solution (maybe days ago) on the other site. What you are supposed to do is mention on each forum you post at that you posted elsewhere and provide a link to that (those) other site(s). That way, people can check the other forum(s) first to see if you already got an answer or not before they attempt to answer your question. By the way, there is also an advantage to you to do this as well. You may get an answer at one of the sites, but it may be highly inefficient or have an undisclosed flaw in it... someone from another forum checking to see if you got an answer would see you did and could provide a better solution or point out potential flaws. If you don't give them links to each site you post at, they won't know to do that.

[TMS]

I haven't been here that long, and I'm afraid I haven't yet picked up on any rivalries, or unspoken community faux pas.

Three years is a while, albeit you haven't made many posts. All forums have the same rules about cross posting and wasting people's time, no matter what the topic ... and it WILL be noticed.

The Attachment icon hasn’t worked for as long as I can remember. But see the yellow banner at the top of the page.