Forum Discussion

Copper Contributor

Jun 21, 2023

Solved

Excel VBA assistance and storing formulas in a table

Had someone assist with creating a macro to generate files. The person is no longer reachable and I feel like this is something simple I'm over looking. First off when the files are generated it foll...

Macros and VBA

JKPieterse
Jun 21, 2023
itsmatta

To fix the numbering, change this bit:

If D < 10 Then array192(Z) = x.Offset(0, -1) & "00" & D Z = Z + 1 Else array192(Z) = x.Offset(0, -1) & "0" & D Z = Z + 1 End If

To this:

array192(Z) = x.Offset(0, -1) & Format(D,"0000")

You cannot change the way tables work, to edit their column formulas you must have at least one row with content in any of the cells. When editing a column formula, make sure that:

- The table has at least two rows

- When you edit a formula, make sure to copy it across all rows of that column

JKPieterse

Silver Contributor

Jun 21, 2023

itsmatta

To fix the numbering, change this bit:

            If D < 10 Then
                array192(Z) = x.Offset(0, -1) & "00" & D
                Z = Z + 1
            Else
                array192(Z) = x.Offset(0, -1) & "0" & D
                Z = Z + 1
            End If

To this:

                array192(Z) = x.Offset(0, -1) & Format(D,"0000")

You cannot change the way tables work, to edit their column formulas you must have at least one row with content in any of the cells. When editing a column formula, make sure that:

- The table has at least two rows

- When you edit a formula, make sure to copy it across all rows of that column

itsmatta
Copper Contributor
Jun 21, 2023
JKPieterse

That code worked! Thank you!

I don't think I explain well on the table part. So I'll include photos:

When cell I15 is selected. No formula is displayed. Once data is inputted into cell C15, I15 auto-populates a formula. He said the formula is stored in the table....
- JKPieterse
  Silver Contributor
  Jun 21, 2023
  That is "By design".