VBA: Insert Missing Columns in a Series

[byehp]

Hi VBA Gurus,

First of all, great website!
It really helps newbie Excel-VBA users like me.

I am currently using MS Excel 2007.
Is there a way in VBA to automatically insert missing columns based on a defined series or range of values?

For example, we have a report that displays columns of: Year 2004 to Year 2012 per column (9 columns in total).
Sometimes, it displays missing a year or years (thus; our report has less columns). For example, it only shows 2008 to 2012 (missing 2004 to 2007). At times, it misses some years in between. What we do is manually add those missing years by manually inserting columns then inputting the missing year as their column headers.

Is it posisble for VBA to check if the columns contain all of the defined Year range (for example, you've initially defined: Year 2004 to Year 2012)? Afterwards, all missing columns are automoatically inserted (in chronological order or based on your defined values or range).

Kindly excuse me if this has been raised before (I can't seem to find any related post).

Appreciate your time,
- byehp

 

[General Ledger]

byehp.

Maybe something like the below but no doubt needs some work. I approached a similar senario in this way:
Test one by one to see if each word in a list exists in a range.
If a word does not exist, go to the last empty cell on that row.
Enter the missing word in the cell.
Continue testing words in the list.
After all needed words are present, sort the columns.

Best of luck,

GL

'Are required fields present?

For Each Word In Array("2004", "2005", "2006", "2007", "2008", "2009", "2010", "2011", "2012")
Set aRange = Range("a1:iv1").Find(What:=Word, LookAt:=xlWhole, MatchCase:=False)
If aRange Is Nothing Then
Cells(1, Columns.Count).End(xlToLeft)=Word
End If
Next Word

'Sort columns
Cells.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _
DataOption1:=xlSortNormal

 

[byehp]

Hi GL,

Thanks for your reply.
I still need your help though.

Kindly excuse me, I forgot to tell you that the year is not at the end of the report (there are two more columns after them). Unfortunately, I can't use "Nothing".
For example, Cell AH2 is year 2004 to Cell AP2 for year 2012. And then there're two other columns for totals (columns AQ2 and AR2).

Is there a way to search for missing year(s) then insert a whole column by moving the other columns to the right? Then continue on searching for other years chronologically.
In this way, the year will all be sequentially arranged.

Cheers,
- bye

 

[General Ledger]

bye,

In Excel, all things are possible. Sometimes you just need to approach a problem from a different side.

It sounds like you need a routine that flows like this:

If AH2<>"2004" then
ActivateCell.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
AH2 = "2004"
End If

If AI2<>"2005" then
ActivateCell.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
AI2 = "2005"
End If

and so on....

End sub

 

[byehp]

Hi GL,

Thanks for the two ideas.
Kindly excuse me for asking like a beginner though.

I'll try the arrays and also the IF conditions tomorrow.

Thanks again and season's greetings,
- bye

 

[General Ledger]

bye,

I just noticed typos: ActivateCell should be ActiveCell

I am sure the VBA can be more efficiently written but you have just a few items you are looking for so hopefully it will run quickly.

To speed up execution of VBA, put this at the beginning: Application.ScreenUpdate=False
This tells Excel not to display updates during the running of the macro on your screen. Only show the final results on screen when the code is done running.

GL

 

[byehp]

Hi GL,

I'll take note of the ActiveCell tomorrow.

Currently, I am just adding this feature to our current macro.
I've included the toggling of ScreenUpdate but thanks for reminding that becaus it surely helps performance.

Cheers, GL.
- bye