VBA code that will reapply my formula to the specific range of cells

[glenn0004]

Hi All

I have an ongoing requirement that I'm trying to resolve within a sales order workbook. If Sales person has agreed a fixed price for subsequent sales order lines they enter the price in to sheet 1, and the pricing is pulled through to each order line on sheet 2. However if the Sales person has not agreed a fixed price for subsequent sales order line they manually enter the price in to the second sheet and overwrite the formula that was originally pulling through the price.

My problem is now that the formula is overwritten and the workbook is now erroneous if reused to create another order as some order lines will contain the will contain the formula and some will not. Is there a VBA code that will reapply my formula to the specific range of cells that the user can run.

Many thanks in advance.

 

[Fishboy]

Hi All

I have an ongoing requirement that I'm trying to resolve within a sales order workbook. If Sales person has agreed a fixed price for subsequent sales order lines they enter the price in to sheet 1, and the pricing is pulled through to each order line on sheet 2. However if the Sales person has not agreed a fixed price for subsequent sales order line they manually enter the price in to the second sheet and overwrite the formula that was originally pulling through the price.

My problem is now that the formula is overwritten and the workbook is now erroneous if reused to create another order as some order lines will contain the will contain the formula and some will not. Is there a VBA code that will reapply my formula to the specific range of cells that the user can run.

Many thanks in advance.

Hi glenn,

Yes that is certainly possible. If you can give an example of the formula and define the range it needs to be applied to, it will be easier to help tailor the code for you.

 

[glenn0004]

Hi glenn,

Yes that is certainly possible. If you can give an example of the formula and define the range it needs to be applied to, it will be easier to help tailor the code for you.

Hi,

The formula I need applying are as follows:

Summary T31:T52 =IF(AND(Contract!$C$61<>"",Summary!E32="MFD"),Contract!$C$61,"")

Summary W31:W52 =IF(AND(Contract!$F$61<>"",Summary!E31="MFD"),Contract!$F$61,"")

Summary Z31:Z52 =IF(AND(Contract!$I$61<>"",Summary!E31="MFD"),Contract!$I$61,"")

Summary AC31:AC52 =IF(AND(Contract!$L$61<>"",Summary!E31="MFD"),Contract!$L$61,"")

One problem area that I have, is that addition lines can be added to the above ranges.

Hope that you can still help

 

[Michael M]

addition lines can be added to the above ranges

So is the cell reference going to be the last row of data ?
for example, I'm guessing data will be input into other columns and then this formula will have to match those rows of data ??

 

[glenn0004]

So is the cell reference going to be the last row of data ?
for example, I'm guessing data will be input into other columns and then this formula will have to match those rows of data ??

Unfortunatly Not:

A new row could be entered anywhere after row 31, the worksheet is set up for 21 rows as default. Yes data is entered into a set of cells in worksheet called contract (C61 F61 I61 L61), each of cells within the range on the Summary worksheet then need to pull through C61 F61 I61 L61 from the contracts worksheet respectivley.

Thanks in adavnce

 

[pjmorris]

Hi Glenn,

I wonder if a different approach might be better. Your comment

the workbook is now erroneous if reused

suggests that you could use a template which would allow New workbooks to be created that are clean.

Just a thought.

Regards

 

[Michael M]

@Peter
....and a good thought at that...

 

[glenn0004]

@Peter
....and a good thought at that...

Hi All
Yes, we have thought about this. However we are wary that the users are sales people (and sorry to tar all sales people)and very likley to revert to a previous completed worksheet and overtype the data.

I'm keeping this in the bag as a last resort.

Regards

 

[pjmorris]

Understand that issue - getting users to behave is a challenge, even if you give them a userguide!!

Given that you're happy using VBA, would using conditional formating to highlight non-computed cells be helpful?

If so, enter this as a function in VBA:

Function isFormula(aCell As Range) As Boolean
    If Left(aCell.Formula, 1) = "=" Then
        isFormula = True
    Else
        isFormula = False
    End If
End Function

Then use: =NOT(isFormula(A1)) as the CF Formula.

Just a thought.

Regards

 

[ukmikeb]

@pjmorris
Could your function be shortened to :-

Function isFormula(aCell As Range) As Boolean
    isFormula = (Left(aCell.Formula, 1) = "=")
End Function

hth