Insert Formula into every column with data 1

[DOMAL]

Hi,

I think this should be fairly easy, but I cant work it out.

I have my own UDF setup "=autocrit(A2)" that pulls back the autofilter criteria for the autofilters in place. In short I want to incorporate this into an easy run macro to insert a row and paste the formula into the cell above the autofilter. Thats not a problem.

The bit I am struggling with is entering the formula in every column with data and setting the reference correctly.

I.e. Column A =autocrit(A2), Column B =autocrit(B2) etc.

All help appreicated

DOMAL

 

[SkipVought]

Hi,

Why not simply use the List or Table feature of Excel, where Excel automatically populates formulas in new rows of data AS THE DATA IS ENTERED?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DOMAL]

I often get spreadsheets that people ask me to look at with autofilters on. This is the purpose of the UDF, to show me what criteria has been specified without having to go into each individual filter.

This means the data will already have been entered when I get the spreadsheet

Thanks

DOMAL

 

[SkipVought]

something like this, where your headings are in row 1 and you want the forumla to begin in column c, right to the last column of data?

dim r as range, c as range

for each r in range([A2], [A2].end(xldown))
   for each c in range([c1], [c1].end(xltoright))
      with cells(r.row, c.column)
         .formula = "=autocrit(" & .address & ")"
      end with
   next
next

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

If you only want formulas of VISIBLE rows...

...
for each r in range([A2], [A2].end(xldown)).specialcells(xlcelltypevisible)
...

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DOMAL]

I'll have all my headings in row 2 with the autofilter applied on row 2, I then want to put the formula into row 1 above all the columns with data in.

So:
A1 "=autocrit(A2)"
B1 "=autocrit(B2)"
C1 "=autocrit(C2)"
D1 "=autocrit(D2)"

Through to the last column the autofilter is applied to (i.e. I'm working on a spreadsheet that has an autofilter up to column Y at the moment) So I would expect:
Y1 "=autocrit(Y2)"

Hope this makes sense, thanks for the help.

 

[SkipVought]

dim c as range

   for each c in range([a2], [a2].end(xltoright))
      with cells(1, c.column)
         .formula = "=autocrit(" & c.address & ")"
      end with
   next

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DOMAL]

Works like a charm.

Cheers