Use Worksheet Formulas in VBA

**craigos** · 03-26-2013, 02:08 PM

Hi All,

I have a Worksheet that I need VBA to generate the formulas for as due to other requirements (not shown on attachment) VBA is the only option.

My Requirement:

In a Range ((D2:D5) the user selects text from a Validated List as "No Break"
On Selecting that text VBA looks at the previous cell ((C2:C5) which is date formatted as dd mmm yy) and adds 182 to it.
VBA then puts the revised date into the correct cell in the Range (E2:E5).
i.e. 27 Sep 12 + 182 = 28 Mar 13
Then:
The cell in Range (E2:E5) has 91 added to it and this date is put in the correct cell in Range(F2:F5) i.e. 27 Jun 13

I have read up on using VBA for formulas but cannot get my head around this.....apologies.

I have attached a Workbook to further illustrate my question to you.

Can anyone help?

In Anticipation

Craig

Work: XP and 2002
Home: W7 and 2010

The more I learn, the more I want to know

**Norie** · 03-26-2013, 02:11 PM

Craig

Perhaps I'm missing something but couldn't all this be done with formulas?

What part of it do you think needs VBA?

**craigos** · 03-26-2013, 02:37 PM

Hi Norrie,

Please excuse my lack of knowledge:

I am unsure as to how write the formulas to get the result I want.

The problem (only as I see it) is if the user selects another option from the list they have to manually input the Date into column C as they need to check another Date Calculator: I can of course leave Column F with formula based on what they input into previous column / cell, so that is my omission.

The manual input is due to complications with having to do additional checks based on what is selected in the dropdown.

Therefore I believed that using VBA for the "No Break" option was the best way as it removes the need to validate the other other options entry.

I trust I have made sense but if sheet formulas can be used that would be the preferred option.

Craig