Mar 01 2022 01:34 PM - edited Mar 01 2022 03:21 PM
Hello,
I have been searching the net for a possible solution but I couldn't find what I need wherever I looked.
I need to increment all cell references in formulas by X or 1 in a selected or defined range in a specific sheet or active sheet.
A note:
The structure of all cells and formulas doesn't allow autofill.
Could anyone help me with that?
I've tried this but it doesn't do anything.
Sub Increment() Range("A2:A400").Select Selection.Offset(1, 0).Select End Sub
I'd really appreciate your help.
Mar 01 2022 01:56 PM
=INDIRECT("A"&ROW($A$3)+(ROW(A1)-1)*$F$1)
Maybe like this? In cell F1 you can enter the X or 1 by which you want the cell reference to increment.
Mar 01 2022 01:58 PM
Mar 01 2022 02:15 PM
Can you share the other formulas? In principle, this should also be possible with other formulas.
Mar 01 2022 02:23 PM - edited Mar 01 2022 02:26 PM
Well they are fairly simple since they are to build a lookup table from a horribly designed table.
=IF('Queue Assignment - Mid'!A27=0,"",'Queue Assignment - Mid'!A27)
=IF(OR('Queue Assignment - Mid'!H6=0,'Queue Assignment - Mid'!H6="No Lunch"),"",'Queue Assignment - Mid'!H6)
=LET(calc,'Queue Assignment - Morning'!E6,IF(calc=0,"",calc))
I switched to LET after awhile because I got lazy with copy pasting. should have done it earlier .
But I should let you know that I'm resorting to a macro because I can't just click and drag formulas because almost all of them are different in a way that it doesn't allow flashfill.
Mar 01 2022 03:28 PM
Sub Makro1()
ActiveCell.FormulaR1C1 = _
"=IF('Queue Assignment - Mid'!R[20]C[-1]=0,"""",'Queue Assignment - Mid'!R[20]C[-1])"
End Sub
These lines were recorded with the macro recorder. You can activate the cell where you want to enter the formula and run the macro. This could be what you want to do.
Mar 01 2022 04:40 PM