I need help converting formula array to VBA

[zeerho]

I have the below formula array that I need to put into VBA so it can be ran with a button. I need this to start in cell B2 and go down the column until it reaches blanks and stop. From other help, I have realized that I can get it into a string, but the constant $A2 will not change per cell. I need it to change from A2, B2, C2 etc... until the next cell is blank.

Please Login or Register  to view this content.

Any and all help is greatly appreciated!

[Richard Buttrey]

Hello and welcome to the forum.

Would you clarify -" I need this to start in cell B2 and go down the column until it reaches blanks and stop."
Are there already values in column B and you want to change them to the formula you mention, or is column B blank at the moment and you want to use another column to find the last row.

And did you really mean you want the reference to $A2 to change to B2, C2, D2..etc as you copy the formula down to B3, B4, B5... or should it change to $A3, $A4, $A5

[zeerho]

Hello and welcome to the forum.

Would you clarify -" I need this to start in cell B2 and go down the column until it reaches blanks and stop."
Are there already values in column B and you want to change them to the formula you mention, or is column B blank at the moment and you want to use another column to find the last row.

And did you really mean you want the reference to $A2 to change to B2, C2, D2..etc as you copy the formula down to B3, B4, B5... or should it change to $A3, $A4, $A5

So I want the output of the formula to start in cell B2. It should correspond with cell A2. So where we are matching A2 with the formula, we output the results to B2. Then move on to match A3, and ouput results to B3. so on so forth. Does that make more sense?

Cells in column B are blank, you are correct.

[Richard Buttrey]

Hi,

No, still confused. Would you upload an example that shows the data you start with, then manually add the results you expect from the formula and clearly identify which cells are original data and which are results.

[zeerho]

Ok. I uploaded to the below link. Hopefully the instructions there make more sense.

The original data would be in A1:B4 and A8:A11. The formula needs to populate and run starting in cell B9 and go down column B.

https://app.box.com/s/6iymrzy2evkj9dxqg1uqio8uvrbnfopo

[Richard Buttrey]

Hi,

Would you mind uploading the file to this forum please. Many of us prefer not to visit file hosting sites of unknown provenance.

[zeerho]

I cannot upload to this forum. When I click the attachment button I just get a blank white line. I've tried Chrome and Edge. Any ideas or thoughts on where I can put it so you can view?

[Richard Buttrey]

Hi,

Not quite sure which attachment button you are referring to. However if you choose the 'Go Advanced' button at the bottom of your post then look underneath the post for the 'Manage Attachments' option you should be able to upload from there.

[zeerho]

That is where I was going, but it is not working. Below is the code that I have which is working now. However, I have 1 issue. My VBA code below is searching for any value from A2:A within column B on Sheet 2. Right now, it will post the first result. I need to find all instances the value is found and display them. Would prefer to show them from newest to oldest on new lines such as :

New1
Old2

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

You should not be using a looping macro for queries like this. Loops are inherently slow, particularly where each iteration has to interact with the Excel App.
The fastest way I know of achieving what you want is with an Advanced Data Filter.

[sanram]

Try this :

Please Login or Register  to view this content.