VBA Loop to convert Different Existing Formulas to Array Formulas and Increment a Value

[Freddybutler]

Hello,

I am new to VBA and have run into an issue with an array formula over 255 characters. The array formulas are different for each cell and are already present in each cell, so I would like to loop through a range, increment a number by 1, and then apply the cntrl+shift+enter to each cell.

An example of one of the formulas is (I am aware this code is incomplete in many ways, I just need a push in the right direction):

For Each cell In Range("G258:AK258")

Selection.FormulaArray = _​

=IFERROR(INDEX($C$221:$C$236,MATCH(1,($I$221:$I$236>=TIMEVALUE("8:59 PM"))*($I$221:$I$236<=TIMEVALUE("9:55 PM"))*($D$221:$D$236<>0)*(IF(ISNUMBER(SEARCH("'",$D$221:$D$236)), VALUE(LEFT($D$221:$D$236,FIND("'",$D$221:$D$236)-1)), "inch")=1),0)),INDEX($W$221:$W$236,MATCH(1,($AC$221:$AC$236>=TIMEVALUE("8:59 PM"))*($AC$221:$AC$236<=TIMEVALUE("9:55 PM"))*($Y$221:$Y$236<>0)*(IF(ISNUMBER(SEARCH("'",$Y$221:$Y$236)), VALUE(LEFT($Y$221:$Y$236,FIND("'",$Y$221:$Y$236)-1)), "inch")=1),0)))
​

My first issue is that the formula is so long I am given an error message.
For each next cell in the range I want the green number to increase by one

Thanks in advance,

 

[StephenCrump]

Welcome to the Forum!

You can generate 1,2,3 ... etc using COLUMNS($G258:G258).

The workaround for the 255 character limit is to start with a smaller formula, and use .REPLACE to build up to the complete formula.

If your formula is valid (I haven't tried to understand what it is doing), then the following code should do what you want, without any looping:

With Range("G258:AK258")
    .Formula = "=IFERROR(INDEX($C$221:$C$236,MATCH(1,($I$221:$I$236>=TIMEVALUE(""8:59 PM""))*($I$221:$I$236<=TIMEVALUE(""9:55 PM""))*($D$221:$D$236<>0)*(8888=COLUMNS($G258:G258)),0)),INDEX($W$221:$W$236,MATCH(1,7777*(9999=COLUMNS($G258:G258)),0)))"
    .FormulaArray = .FormulaR1C1
    .Replace "8888", "IF(ISNUMBER(SEARCH(""'"",$D$221:$D$236)), VALUE(LEFT($D$221:$D$236,FIND(""'"",$D$221:$D$236)-1)), ""inch"")", Lookat:=xlPart
    .Replace "9999", "IF(ISNUMBER(SEARCH(""'"",$Y$221:$Y$236)), VALUE(LEFT($Y$221:$Y$236,FIND(""'"",$Y$221:$Y$236)-1)), ""inch"")", Lookat:=xlPart
    .Replace "7777", "($AC$221:$AC$236>=TIMEVALUE(""8:59 PM""))*($AC$221:$AC$236<=TIMEVALUE(""9:55 PM""))*($Y$221:$Y$236<>0)", Lookat:=xlPart
End With

 

[StephenCrump]

Just as a general point, it's good practice to avoid hard-coding numbers into your formulae, e.g. TIMEVALUE("8:59 PM").

If instead you point to a cell to get the value (and better still in my opinion, give the cell a meaningful range name like StartTime) then if you have to change the value you need change only a single cell, rather than having to wade through a workbook checking/changing all occurrences.

 

[Freddybutler]

Thanks very much for taking the time to reply! I typically avoid-hard coding whenever I can, just new to VBA and it's nuances.