LinkBack URL
About LinkBacks
Hi
Does anyone know where it is possible to use a counta result in sheet1 to determine how many columns Column A should copied and autofilled over in macro?
Thanks
Jeskit
Hi
Does anyone know where it is possible to use a counta result in sheet1 to determine how many columns Column A should copied and autofilled over in macro?
Thanks
Jeskit
Last edited by jeskit; 02-08-2011 at 11:07 AM.
I'm sure it might be possible but I'm not sure it's necessary.
If you can describe your problem, maybe with a sample workbook there's probably another way of doing what you want.
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
Hi
Well the problem is i have a macro inside workbook 1 which opens workbook2. It thens loops through a folder and copies column e from each of the files which are on a list in workbook2 sheet 3 and pastes into sheet1 of workbook2 in a new column.
However in sheet2 of workbook2 i have a column full of formula. Also in Sheet 3 with the list is a macro which does a counta on that list. What i need to do is in the macro add a bit of code which takes the counta result and uses it to copy the forumla across that number (counta result) of columns.
Sorry i cant put a workbook as it has sensitive data in it.
Hope this helps.
Jeskit
Are you getting columns and rows mixed up in your description?
Here's an example of how you can populate a column with formula based on a variable length list:
Please Login or Register to view this content.
This fills column C2 downwards based on the number of rows of data in column A.
I've attached a sample.
Dom
Hi
I have added a template of my workbook. In sheet 1 is a list with a counta in cell H1.
I need to use the result of the counta in cell H1 to determine how many columns i need copy all the formula in Column E in the ExtractReport sheet and auto ill into.
Does this make more sense?
Thanks
Jeskit
So you want to copy the formula from column E on the ExtractReport sheet and paste it down the list in column E on Sheet1?
The formula in column E doesn't seem to be consistent which is a bit confusing.
Also you appear to still have a lot of data in your sample that's 'hidden' with a white font. I hope that's not the sensitive data you were talking about.
Dom
No what i want to do is do an auto fill using the forumla in column E on sheet ExtractReport and copy it along the columns in the ExtractReport sheet. But i need to use the counta result to determine how many columns i need to do teh autofill for.
There is a different forumla in each cell in column E but i need all the forumlas to be autofilled.
Currently i am using this piece of codeBut that just uses a selected destination, however i need change this so it determines the destination columns based on the number provided by the countaPlease Login or Register to view this content.
Got you, being a bit slow today.
Try this:
Please Login or Register to view this content.
Dom
hI
I tried your code and it produced an error. it said "Application defiend or object defiend error"
It should work on the sample you provided.
Are your sheets named the same on the workbook you tried it on?
Dom
Yep all the same
I've attached the workbook with the code that works for me.
Dom
OK had a look tried it again and i am still getting an error. Question what excel are you using as i am using 2010?
I'm using 2003 but pretty sure there's nothing in that code that won't work in later versions.
It doesn't work in the sample I just posted?
Dom
Also i am not running the code in the same workbook. The macro is being run from another workbook which acts as only a home for the macro.
My macro opens the template workbook and then runs the code to perform the count a in cell H1 and then performs the autofill.
I have pasted the code below if that helps:
Please Login or Register to view this content.
no it worked in the sample you sent me but it doesn' work in mine
Try this:
Please Login or Register to view this content.
Dom
Nope still not working still getting "Application defined or object defined error"
Looking back no further than Domski's last post, maybe
Please Login or Register to view this content.
Entia non sunt multiplicanda sine necessitate
Hi
Ok that works but doesn't do what i need it to. I need to do that for a defined numebr of columns. The number of columns is the result of the counta
What i need is for it to do thisBut instead of using thePlease Login or Register to view this content.i need it to do this but for the numebr of columns defined in the result of the countaPlease Login or Register to view this content.
Maybe
Please Login or Register to view this content.
Sorry it still produces an Application defined or object defined error
However i might have done the end bit wrong how to get it to look at the cell with counta result?
currently i have this, which could be wrong,Please Login or Register to view this content.
You're resizing columns
Please Login or Register to view this content.
Still getting the error message application defined or object defined error
Hi
Found the problem i had accedently deleted the counta forumla..fixed now!!!!
Thank you All for you help
I am extremely gratefull
THANKS
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks