Using a VBA variable in a cell formula?

[TG58]

Hi Guys,

Not sure if this should be in the VBA or Formulas section.

I have the formula in cell B9 =sheetoffset(-1,B9)-sheetoffset(-1,B10)+B8 which "brings forward" figures from the previous sheet.

Please Login or Register  to view this content.

If I have an alternate figure in a variable, I would like to use that instead of the above, BUT retain the above when going to the next sheet.
So, can I have something like =If(MyVariable<>,MyVariable, sheetoffset(-1,B9)-sheetoffset(-1,B10)+B8)

Thanks for looking.

[MrShorty]

You should be able to call your UDF from within an IF() worksheet function just like you describe https://support.office.com/en-us/art...c-aa8bbff73be2

[TG58]

Thank you for your reply.

The variable that has been generated comes from choices made using option buttons on a userform. Can you suggest a way that I can get from the userform to the cell?

[MrShorty]

I never use userforms, so I cannot suggest a way. My first thought would be to have the code behind the userform write the desired option(s) into a cell or cells in the spreadsheet (using the .value property of the desire range object), which your IF() worksheet function can then refer to, but I will defer to others with more experience working with userforms.

In the meantime, are there other aspects of this project that you need to explain? What calls the userform? What kind of values are in the userform? How do you determine which values in the userform are impacting this decision/calculation? or anything else that might be pertinent. If you can upload a sample workbook, that will likely help whoever comes along to understand exactly what you are trying to do.

[TG58]

Hi MrShorty,

I can't attach the file using the paperclip on this page, so below is a link that should give anyone the sample file.

On sheet2 there is a command button "Move Crewman" this loads the userform.


https://drive.google.com/open?id=0B8...EV2M0dwVjhUbTQ

I said in my initial post that the formula is in cell B9, this was just an example,

I need this to work for 8 cells, B9, F9, B18, F18, B27, F27, B36 & F36.

Thanks

[TG58]

Although not "exactly" what I was hoping for, MrShorty has offered a workable sustitute, so I will mark this post as solved.