Inserting a column in VBA

Sparky_ · December 20th, 2010, 05:02 PM

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_

mtranchi · December 21st, 2010, 04:41 AM

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

Sparky_ · December 21st, 2010, 10:32 AM

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_

mtranchi · December 21st, 2010, 12:01 PM

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