0

If the activecell is not in the first column, it is supposed to create a new column in column A and insert the rounded-up values of the row numbers divided by 10.000. Don't really find how to insert this variable into the formula

If ActiveCell.Column < 1 Then _
   Range("A:A").Insert
   Range("A10").EntireColumn.Insert
   Range("A:A").Formula = "=ROUNDUP(ROW(A" & rowNum & ")/10000;0)"
Camone
  • 13
  • 2
  • Change the `;` to `,`, or change the `.Formula` to `.FormulaLocal`. – BigBen Nov 20 '21 at 14:22
  • Also, `Range("A:A").Formula` writes the formula to every single cell in column A. You could perhaps [find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) first, if that's not the intended behavior. – BigBen Nov 20 '21 at 14:25
  • Still not working when i change to FormulaLocal. Excel receives this in the formula bar : =ROUNDUP(@ROW(A)/10000;0) – Camone Nov 20 '21 at 14:54
  • Then use `.Formula2Local` since your version of Excel supports Dynamic Array formulas. – BigBen Nov 20 '21 at 14:56
  • Also, how are you determining `rowNum`? Where do you have `rowNum = ...`? – BigBen Nov 20 '21 at 14:58
  • Still not working BigBen, since the output is excel is #NAME and the corresponding formula is indeed =ROUNDUP(@ROW(A)/10000;0), I suppose there must me smth wrong in the way that I want to input the row numbers, as it can't read it. No? – Camone Nov 20 '21 at 15:00
  • I haven't defined rowNum... I thought it would for each cell refer to the rowNumber it is in... – Camone Nov 20 '21 at 15:01
  • Then use `"=ROUNDUP(ROW()/10000;0)"` – BigBen Nov 20 '21 at 15:02
  • I wanted it to be a variable input for all cells differently – Camone Nov 20 '21 at 15:03
  • `ROW()` without parameters will refer to the row the formula is in. – BigBen Nov 20 '21 at 15:05
  • 1
    BigBen thanks a lot man! Great help! – Camone Nov 20 '21 at 15:05

0 Answers0