Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
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 December 20th, 2010, 05:02 PM
Registered User
 
Join Date: Dec 2010
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Inserting a column in VBA

Greetings,

I have some VBA code that is scanning "our original data" and building a filtered new file (workbook).

When I come to a new piece of data i.e. a new account that was not present in the previous year I want to insert a new column and insert the new data in between two existing pieces.

Meaning I have 2001's data all brought in.

Now I'm importing 2002 and I come across a new account that was not present in 2001. I would like this new data to fall in place where it does in the 2002 data. (Currently I'm appending it to the end and this is causing some unexpected problems.)

I have found some examples of inserting columns but they all specify a certain column location - my insert could occur anywhere or are unknown.

Does this make sense? I can clear this up as needed.

Thanks
Sparky_
 
Old December 21st, 2010, 04:41 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

No it doesn't, lol. Do you have 3 workbooks going? One each for 2001, 2002, and one for all accounts?
It sounds like you need to run a loop that goes through each column in the 2002 workbook, find the column before the one you need to add to your combined years workbook, then run a loop through each of the columns in your combined workbook looking for that same column and assigning the current column number to a variable. Once you find it, have an if statement within the loop exit out of the loop and then you can insert the new column where that variable is pointing to.

Alternatively, you might be able to use the find method (use the macro recorder to generate some working code) to find the right insert range. I am not that familiar with this way, I know there is one find or search or something in either VBA or Excel that will return the column and row of the found data.

If you try the looping method, I can probably help you, but will need more info.

gl
 
Old December 21st, 2010, 10:32 AM
Registered User
 
Join Date: Dec 2010
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

yes I have the loop portion you described working - I have many data files - 2001 through present.

I have the overall routine working IF I append new accounts in an empty column to the right but it doesn't "look" great I would like to have the new account fall where they do in order. What I want is:

scan - scan scan (loop) - appending data underneath the existing data - then - found new account that wasn't present last year - insert new column - add new data there - back to scan - loop appending data

next year that newly added data won't be a new one and would fall in line like the rest.

Like I said - I have it working by adding new accounts to the far right.

any thoughts on how to insert a column within VBA.

Thanks
Sparky_
 
Old December 21st, 2010, 12:01 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default Here's an example

The following goes down each column checking the account name against the new accoun name. Once the alphabetical insertion point is found, it exits the loop, then inserts a new column at that point and adds the new accounts name.

On Sheet1 I have the following in the first row:

acme bondoogle chuckie delite

Here's the code that finds the insertion point for "catville"

Code:
Option Explicit

Sub FoundNewAccount()
FindInsertColumn ("catville")
End Sub

Sub FindInsertColumn(NewAccount As String)

Dim lColumnNum As Long 'The current column we're looking at in the loop
Dim lTotColumns As Long 'Total number of columns with account
Dim stAccountInCurrentColumn As String

lColumnNum = 1

With Sheet1 'If you look in the project explorer, each sheet has two names. This reference
                   'is to the name NOT in parantheses.
                    'You can change the name not in parentheses in the properties window. 
                      'One advantage of using this over
                    ' Sheets("Sheet1") is that it is strongly typed meaning you get the pop-up
                     ' help when you code
    
    'Get the number of colums to look through
    lTotColumns = WorksheetFunction.CountA(.Range("1:1")) 'Get total columns
    
    For lColumnNum = 1 To lTotColumns
        stAccountInCurrentColumn = .Cells(1, lColumnNum) 
        
        If NewAccount < stAccountInCurrentColumn Then 'we've found the insertion point
            Exit For 'jump out of loop
        End If
    Next

    .Cells(1, lColumnNum).EntireColumn.Insert 'Insert a column
    .Cells(1, lColumnNum) = NewAccount 'Add the new account
End With

End Sub
One thing to note is that checking account names the way i did will differentiate between an uppercase "C" and a lowercase "c"

Hopefully that will get you started down the right path

Last edited by mtranchi; December 21st, 2010 at 12:09 PM..





Similar Threads
Thread Thread Starter Forum Replies Last Post
Linq to Sql - Retrieving the value of an identity column BEFORE inserting new row jmartarelli BOOK: Professional LINQ 2 March 11th, 2011 04:42 PM
Linq to Sql - Retrieving the value of an identity column BEFORE inserting new row jmartarelli LINQ 2 August 4th, 2010 12:59 PM
Linq to Sql - Retrieving the value of an identity column BEFORE inserting register jmartarelli BOOK: Professional ADO.NET 3.5 with LINQ and the Entity Framework ISBN: 978-0-470-22988-0 0 July 17th, 2009 04:33 PM
Inserting duplicate values in primary key column, Shuchik SQL Server ASP 1 August 31st, 2007 05:38 AM
inserting worksheet into an OLE field with VBA Mihai B Access VBA 0 November 23rd, 2004 10:28 AM





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