In-cell formula in VBA

Hello All

I've been struggling for some time to find a solution to this, I've had to admit defeat and come here:

Is it possible to write a VBA version of the following in-cell formula so that cells in a column, say, column C contains the result of the two cells?

Please Login or Register  to view this content.

I wouldn't necessarily run this code on the manual calling of a macro, but the column would just populate on worksheet open or worksheet change. This is what makes this difficult - column C always needs to show the contents of columns a and b, not just when a macro is run.

Ive been working off using the offset method because each row has an Active X button and the A B and C columns are x columns offset from the button, but i havent got anywhere.

A VBA solution would be I think my only solution because the user has to be able to sort the worksheet columns, remove rows and insert rows and as soon as they do that, the in- cell formulas get messed up.

If this is possible it would fantastic, and if anyone can help, that would be excellent!

cheers

Adam

Hi

Please Login or Register  to view this content.

Steffen Thomsen

Thanks Steffen!

Only problem is that it asked me to define the variable 'cell' on the second line of code, I added 'Dim cell as variant'

Is that what I should be doing?

thx

Adam

Dont give it a type,

add at top

Please Login or Register  to view this content.

Perfect! It works well.

How would I implement a worksheet change event so that the result in column C updates if the data in either A or B changes?

thx

Hi,

Put this in the sheet code

Please Login or Register  to view this content.

Steffen Thomsen

Please Login or Register  to view this content.

Steffen, this is what I've now got in the sheet code, and when a cell in column L changes, nothing is changing in column P?

Hi,

Try this

Please Login or Register  to view this content.

If to account for changes done to column J as well use the following

Please Login or Register  to view this content.

No luck I'm afraid. I'm changing values in either column L or J but column P doesn't update

When you run the code in VBA, it asks you to select and run one of the other macros in the project.

Originally Posted by mikerickson

Formulas in column C would do that.
Why re-invent the wheel?
(Especially when all the neo-wheels posted fall apart if the sheet is protected or the workbook is read-only or...)

I'm not criticizing the posted codes, but pointing out that native Excel is far more robust (and faster and uses fewer resources) than its VBA driven equivalent.

Yep, this is what I had before and worked fine until the user sorts the data table, or removes or inserts rows, then the formulas get messed up....

Hi,

Have you placed the code correctly?

Click on the sheet, where this code needs to take effect, and click "View code", here you insert the code.

@mikerickson

True, this can be done with formulas