programming web query

**excellentexcel** · 03-08-2009, 02:04 PM

hi guys
im wondering if any one could enlighten me as to why sheet summary goes all wafty when i try to do a new input on the input sheet even though i recorded a macro to make the cells do what they need to.
i thought i had sussed it but to no avail
here is the link for you cleverer people
kind regards
http://www.sendspace.com/file/1uk1fb

**excellentexcel** · 03-08-2009, 03:01 PM

would someone be able to explain the difference between this

Please Login or Register  to view this content.

and this

Please Login or Register  to view this content.

the top one is a macro i recorded and the bottom one the actual formula in the cell
why isr1c5?
does that mean row 1 and column 5 which is what it would be and why has it converted?

**excellentexcel** · 03-09-2009, 03:25 AM

bump bump bump

**excellentexcel** · 03-09-2009, 02:26 PM

someone out there must surely be able to help me with this please

**NBVC** · 03-09-2009, 02:53 PM

Yes, the macro recorder uses the R1C1 format which means Row/Column format...

If you want to use your formula just replace the R1C5 with $E$1 in the VBA editor but it should still give same final result

**excellentexcel** · 03-09-2009, 02:55 PM

nbvc
have you tried pressing the button as maybe you can see something in the code thats making it do what it does?
thanks for the reply
its so close but just not quite right
thanks

**NBVC** · 03-09-2009, 03:40 PM

Try:

Please Login or Register  to view this content.

**T-J** · 03-10-2009, 10:56 AM

Originally Posted by excellentexcel

why sheet summary goes all wafty when i try to do a new input on the input sheet even though i recorded a macro to make the cells do what they need to.

I can't help with the question (you should state what you are trying to do and what is going wrong) but one thing I notice, after refreshing the query tables, you've added code to insert cell formulas and fill down to row 1000+. This isn't necessary because if you right-click a query table and choose Data Range Properties there is an option to 'Fill down formulas in columns adjacent to data'.

If this is ticked then all formulas to the right of the query table get filled down automatically when the refreshing the query table.

On each sheet with a query table enter formula in the column(s) immediately to the right of the query table, e.g. using the first table from http://www.soccerbase.com/head2.sd?t...42&team2id=154 if the query table is in A1:D5, enter formulas in E2, F2, G2, etc. then click Refresh Data.

To get the macro to fill down formulas automatically, change this

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

**excellentexcel** · 03-11-2009, 02:54 PM

hi t-j thanks for the reply
i have deleted all my old formulas
re saved as another name but its not doing as you said it should
it doesnt copy the formulas down
i did what you said word for word
kind regards