Excel 2010 tidy formula producing code

[PWD]

Good afternoon, I’m trying to automate a job that seems to have defeated the way we previously did it. Basically we get sample on a weekly basis and allocate flags to excluded records based on various criteria within the data – but only one flag per record. At present we run code that puts formulae in each row of each ‘exclusion reason’ column, Autofill all of them to the end of the column, calculate the sheet, do the next & finally copy the cells with formulae & paste as values. This (still) works fine on a weekly basis with some 3,000 records x 120 columns (16 of which are the ‘exclusion flag’ columns) but not when aggregating 9 months – around 112,000 records x 120 columns – either my PC or Excel doesn’t appear to be able to cope.

So, having written this question so far I’ve decided to break it down so we handle and complete only one column at a time now.

This has just taken around 100 seconds to execute. So far, so good. Now, unfortunately, the reasons for exclusion columns don’t run in the same order that the exclusions are processed so some of the formulae just look a bit clunky :-

Sub incorrectlength()

'8) "No/Incomplete Tel No/Tel No Length"
        
    With Rows("1:1")
    XCol8 = .Find(What:="incorrectlength", After:=[A1], LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Column
    End With

    Range("DT2").FormulaR1C1 = _
        "=IF(OR(RC[2]=""Yes"",RC[3]=""Yes"",RC[4]=""Yes"",RC[5]=""Yes"",RC[-3]=""Yes"",RC[-2]=""Yes"",RC[-1]=""Yes""),""No"" " & _
        ",IF(RC" & XCol8 & "=""Yes"",""Yes"",""No""))"
        'If anything else = "Yes", "No", If(incorrectlength = "Yes",No/Incomplete Tel No/Tel No Length
        
        Range("DT2").AutoFill Destination:=Range("DT2:DT" & EndRow), Type:=xlFillValues 'Retain current cell format
        ActiveSheet.Calculate
        
        Range(("DT2"), Range("DT" & EndRow)).Copy
        Range("DT2").PasteSpecial Paste:=xlPasteValues
        
End Sub

A previous version used:

"=IF(COUNTIF(RC93:RC[-1],""Yes"")>0,""No""

Which is fine for columns to the right of previously populated columns. Would anyone have any suggestions for any improvements?


Many thanks,
D€$

 

[SkipVought]

hi,

So you have an order of processing prescidence issue. How about multiple iterations?

How about redesigning the process (column sequence)? Think in terms of using one row for mapping this process. What column can be processed first, without any dependent prior calculation in that row?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Hi Skip, thanks for the suggestions - not that I understand what you mean by multiple iterations; unless it's what I've done & broken it down into 16 consecutive processes. I appear to be bound by the order that we've got, as our (internal) customer wants it that way . So I have put this formula in EG2 - the next column after the final column:

 =IF(COUNTIF(DQ2:EF2,"Yes")>0,1)

Then my formula (all my formulae will now be similar) looks like this:

    Range("DT2").FormulaR1C1 = _
        "=IF(RC137=""1"",""No"" ,IF(RC" & XCol8 & "=""Yes"",""Yes"",""No""))"

Many thanks,
D€$

 

[SkipVought]

Multiple Iterations:
Office Button > Excel Options > Formulas > Calculation Options >> Enable iterative calculation -- and whatever iteration parameters you choose.

BUT.......

what about the redesign question? So you kee the order by use VBA to calculate the COLUMNS in the most logical order, like

range("DY:DY").Calculate
range("DT:DT").Calculate
'....

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

oh, yes and the overall process might be

1) turn on MANUAL calculation
2) load ALL the columns with formulas
3) run the column-by-column calculate
4) turn on AUTOMATIC calculation

Also check out the HELP on

Formula calculation, performance, and error handling options
Change formula recalculation, iteration, or precision



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[zelgar]

It looks like you have a lot of logic statements in your spreadsheet. I'm guessing you're either manually copy/pasting them or have it being done in a macro. Wouldn't it be simplier, take up a lot less space & be faster to have the macro populate the cells with the result with the logic being determined in the macro (e.g., a IF statement in the macro to give the Yes/No results)?

 

[PWD]

Hi Skip, this is all very new to me. The Enable iterative calculation appears to have got round the circular reference issue but I can't find a straight answer for what values exactly I should be setting.

From what I've setup here I think I'd need to calculate Column EG each time after I'd entered the formulae on each column.

It's almost quitting time so I'll revisit this on Monday - thanx.

Many thanks,
D€$