Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Insert Formula into every column with data 1

Status
Not open for further replies.

DOMAL

Technical User
Apr 12, 2007
30
GB
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
 


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]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
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
 

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?
Code:
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]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 



If you only want formulas of VISIBLE rows...
Code:
...
for each r in range([A2], [A2].end(xldown)).specialcells(xlcelltypevisible)
...


Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
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.

 


Code:
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]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top