Need help setting range for formulas in dynamic table

[Narcie]

I'd like to set a formula and function (date, currency, etc) for an entire column in a dynamic table (I use a data entry UserForm to add new data to the table, automaticly creating new rows that should have the same formulas and functions in columns that aren't effected by the data entered through the UserForm.

In column C for example I want the function for the entire column to be set to the format 'Date' with the formula '=C2+14' which automaticly turns into '=C3+14' when a new row is added, etc. I just want to understand how to give a column a dynamic range for both formula and function so I can apply it to other columns as well.

If anything I wrote above isn't clear, or if you you need any more information please don't hesitate to ask. I'm here to learn.

Thanks in advance!

 

[Kamolga]

By selecting a culumn (or several), going on formula tab and "define name" you can name a range: RangeColumnX for example (it is important to have it referenced as =table1... and not =A1:C5). You can then do wathever you want to this dynamic range

Range("RangeColumnX").select

 

[Narcie]

By selecting a culumn (or several), going on formula tab and "define name" you can name a range: RangeColumnX for example (it is important to have it referenced as =table1... and not =A1:C5). You can then do wathever you want to this dynamic range

Range("RangeColumnX").select

So if I got a dynamic range for column D, how do I go about adding a formula to it? I only need one column ranges btw.

 

[Kamolga]

You can rather copy it

Range("A1").copy
Range([COLOR=#333333][I]"RangeColumnX"[/I][/COLOR]).Select
    ActiveSheet.Paste

or write it

Dim dCell as range
For each dCell in [COLOR=#333333][I]Range("RangeColumnX")
[/I][/COLOR]ActiveCell.FormulaR1C1 = "=R[-1]C[-1]+7"
Next

 

[Narcie]

You can rather copy it

Range("A1").copy
Range([COLOR=#333333][I]"RangeColumnX"[/I][/COLOR]).Select
    ActiveSheet.Paste

or write it

Dim dCell as range
For each dCell in [COLOR=#333333][I]Range("RangeColumnX")
[/I][/COLOR]ActiveCell.FormulaR1C1 = "=R[-1]C[-1]+7"
Next

Maybe I'm reading your code wrong, but I don't see how this offers a solution for my issue. I use a UserForm to enter new data into the table. That automaticly creates a new row, however some of the columns aren't being injected by the UserForm because they use a formula to determine the value of the cell in said columns in every next row. So for that to work, I need to assign a formula to a dynamic range.

For instance, I made a dynamic range for column D. Now I need to assign a formula to every cell in column D. The UserForm enters a date in column C and in Column D I want that date + 14 days (a expiry date). For that to work I need every cell in column D to have the formula '=C2+14' where C2 automaticly becomes C3 when a new row is added by the UserForm and so on. What I want to know is what I have to put in the VBA to make this work for column D. Could you write the code for column D as I described it, that way I can study how that code does what it does and then apply it myself to some other columns with a different formula (I want to learn, so I'm not going to ask you to write the code for all the columns I need to assign formulas to).

If the code you provided does exactly what I want it to do, maybe you can explain the code so I can understand how to apply it for the columns I need because right now I don't really understand the code you provided.

Thanks in advance!

 

[Narcie]

Or anyone? I can't get my head wrapped around this.