Wrox Programmer Forums
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 8th, 2011, 08:12 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default Coping cells

Hi

Does anyone know where it is possible to use a counta result in sheet1 to determine how many columns needs to be auto filled using the forumla in the cells in column E

Code:
 Range("E:E").Select
Selection.AutoFill Destination:=Columns("E:IU"), Type:=xlFillDefault
Range("A1").Select
So far i have the code above but instead of it have columns("E:IU") i need to do this but for the numebr of columns defined in the result of a counta formula

Thanks

Jeskit

Last edited by jeskit; February 8th, 2011 at 10:03 AM..
 
Old February 18th, 2011, 06:16 AM
Authorized User
 
Join Date: Mar 2008
Posts: 74
Thanks: 2
Thanked 0 Times in 0 Posts
Send a message via ICQ to sektor
Default

Please, describe in more detail what you want to do?
 
Old February 18th, 2011, 06:52 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

you should just use the counta function directly in your code instead. You know that excel 2010 has over a million rows though right? I'm trying to figure out how every cell in column E is part of the source range. Must have a lot of data
Code:
Dim lColCount as Long
Dim rgSource as Range
Dim rgDestination as Range


'this is how you use the counta function in code:
With Sheets("MySheet")
    Set rgSource = .Range("E:E")
    lColCount  = Worksheetfunction.CountA(.Range("WhateverRangeYouWantToUseTheCountAFunctionOn"))
    Set rgDestination = rgSource.Resize(1, lColCount)
    rgSource.AutoFill Destination:=rgDestination, Type:=xlFillDefault

End With





Similar Threads
Thread Thread Starter Forum Replies Last Post
Coping data from db to another on diff server eapranger SQL Language 1 November 12th, 2009 07:23 AM
Coping stored procedure shailu SQL Server 2000 4 June 30th, 2006 02:52 PM
Help me coping table from one Database to another shailu SQL Server 2000 1 May 19th, 2005 06:23 AM
Coping data to a zipped folder red4vbs VBScript 0 January 28th, 2005 06:37 PM
Cells MattLeek Excel VBA 2 December 12th, 2003 11:54 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.