Converting xls formula to VBA function

[purple_ninja]

Hi
I am trying to reduce the processing time of a spreadsheet that contains the following formula. The constants need to remain constant but the rest will need to reflect the position in the worksheet e.g. as it would if it the formula were copied across columns.

=IF(AND($K10>=X$4,$K10<Y$4),$K$6,IF(X$3="XSD","Xmas",IF(AND($L10>=X$4,$L10< Y$4),$L$6,IF(AND($M10>=X$4,$M10<Y$4),$M$6,IF(AND($N10>=X$4,$N10<Y$4),$N$6,I F(AND($O10>=X$4,$O10<Y$4),$O$6,IF(AND($P10>=X$4,$P10<Y$4),$P$6,IF(AND($S10> =X$4,$S10<Y$4),$S$6,IF(AND($R10>=X$4,$R10<Y$4),$R$6,IF(AND($Q10>=X$4,$Q10<Y $4),$Q$6,IF(OR(Y10=$K$6,Y10=$L$6,Y10=$M$6,Y10=$N$6,Y10=$O$6,Y10=$P$6),SUM(M AX(Y10:AE10)+1),IF(AND(Y10>0,Y10<10),SUM(MAX(Y10:AE10)+1),IF(Y10="Aircon"," ",IF(Y10="","",IF(AND(Y10="Xmas",Z10="Xmas",OR(AA10="",AA10="Aircon")),"",S UM(MAX(Y10:AE10)+1))))))))))))))))

Any assistance converting this into a VBA function would be much appreciated.

[SamT]

if it the formula were copied across columns.

There are a lot of Column Relative references in that formula.

For instance in any Row in Column "A", $K10>=X$4 refers to Column "X", but if you copy that formula into Column "B" then it refers to Column "Y". ( $K10>=Y$4 )

Any Cell Reference without 2 dollar signs is a relative reference ($A$1)

[SamT]

This is the first attempt at parsing your formula. I can't proceed further since I don't know if your formula is copied across Columns or down Rows.

I do notice the references to Column "S" are out of step. Is this deliberate?

The possible results are in red. All known Constants (other than results) are in green. Note that if the formula is copied down Rows, then X$4 and Y$4 are Constants. What about Columns Y and AA: Are they relative Column references?

IF(AND($K10>=X$4,$K10<Y$4), $K$6,
IF(X$3="XSD", "Xmas",
IF(AND($L10>=X$4,$L10<Y$4), $L$6,
IF(AND($M10>=X$4,$M10<Y$4), $M$6,
IF(AND($N10>=X$4,$N10<Y$4), $N$6,
IF(AND($O10>=X$4,$O10<Y$4), $O$6,
IF(AND($P10>=X$4,$P10<Y$4), $P$6,
IF(AND($S10>=X$4,$S10<Y$4), $S$6,
IF(AND($R10>=X$4,$R10<Y$4), $R$6,
IF(AND($Q10>=X$4,$Q10<Y$4), $Q$6,
IF(OR(Y10=$K$6,Y10=$L$6,Y10=$M$6,Y10=$N$6,Y10=$O$6,Y10=$P$6), SUM(MAX(Y10:AE10)+1),
IF(AND(Y10>0,Y10<10), SUM(MAX(Y10:AE10)+1),
IF(Y10="Aircon", "",
IF(Y10="", "",
IF(AND(Y10="Xmas",Z10="Xmas",OR(AA10="",AA10="Aircon")), "",
SUM(MAX(Y10:AE10)+1))))))))))))))))



It will help if you explain the logic of the Formula and upload an example of the top 12 Rows of the Sheet.

[snb]

I'd rather see data and desired result than a formula.

[purple_ninja]

Hi all thanks for your responses so far.

I've sanitised the workbook over the weekend and attaching it here in the hope it might help. There are two lines of formula that run on the second and third lines of each group of 3.

Combined Test File.xlsm

A separate macro currently runs which colour codes the results of the formulas and in an ideal world I'd like to combine the two. My main objective though is to remove the formulas as they are causing Excel to hang for anything up to an hour while they calculate (combination of slow network/lack of local processing power). I've run the sheet on a more powerful machine and got it down to 5 mins running time, which is great , but I'm not likely to get anything that powerful to run it on any time soon!

I realise I may be asking the impossible (or at least it's not going to be as simple and straight forward as I had hoped and may be too complex?)

Any thoughts much appreciated!

[SamT]

Any response to my questions?

You also have a similar, or identical, formula in Row 9

Regarding this part:

IF(OR(Y10=$K$6,Y10=$L$6,Y10=$M$6,Y10=$N$6,Y10=$O$6,Y10=$P$6), SUM(MAX(Y10:AE10)+1),
IF(AND(Y10>0,Y10<10), SUM(MAX(Y10:AE10)+1),
IF(Y10="Aircon", "",
IF(Y10="", "",
IF(AND(Y10="Xmas",Z10="Xmas",OR(AA10="",AA10="Aircon")), "",

It appears that Row 10 contains letter combinations. How do the red parts fit in with the rest of the formula?

As far as using a macro, it only has to run when a cell in the first table is changed. IMO, that would be much better than using formulas in a 180+ by 800 cell table.

It would be nice if you would explain the logic of the tables so we don't have to analyze the formula to figure it out.

[purple_ninja]

Any response to my questions?

You also have a similar, or identical, formula in Row 9


It would be nice if you would explain the logic of the tables so we don't have to analyze the formula to figure it out.

Hi Sam

The code is checking the dates and inputting the milestone in the correct week/date. For the gaps in between it adds in a number so working through the sheet if the date matches one of the dates in the ref cells it enters the specified milestone , if it doesn't it checks for the maximum number in the row and adds 1. So the furthest milestone will have the lowest value.

These numbers are then used to colour code in the macro "worksheet activate" using case statements

[SamT]

That is a very complex worksheet. You used two sentences to explain it.

Here is a two sentence code for you to speed up your worksheet

Sub SamT()
For Each Cel In Sheets("Combined").SpecialCells(xlCellTypeFormulas)
  If Cel.Value <> "" Then Cel = Cel.Value
Next
End Sub