Vba - insert two columns after each occurrence of dept name

aattwod

New Member
Joined
Mar 13, 2009
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
I need help with simple VBA Code. I have a SAP BW Excel Extract. In Row 3 there are are column headers of ten Depts. Each Dept's name occurs four times. I want to find the last occurrence of each Dept's name and insert two blank columns for each Dept. All the first blank column's header must be "Reasons for Variance" and the second must be "Corrective Action". Can someone help please.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Welcome to the MrExcel board (posting at least :))!

Does this do what you want?

Code:
Sub Insert_Columns()
  Dim c As Long
  
  For c = Cells(3, Columns.Count).End(xlToLeft).Column To 1 Step -1
    If Cells(3, c).Value <> Cells(3, c + 1).Value Then
      Columns(c + 1).Resize(, 2).Insert
      Cells(3, c + 1).Resize(, 2).Value = Array("Reasons for Variance", "Corrective Action")
    End If
  Next c
End Sub
 
Last edited:
Upvote 0
Hi Peter Tx and much appreciated. I will test it on Sunday and provide some feedback on the success rate.
 
Upvote 0
OK, cheers.

Oh wow! Peter, You are a genius without seeing the actual file. It works perfectly. The only thing that I did not mention in my plea for help was to say that the department names starts in Row 3 in Colum D. I want to retain the data in Colums A to C as is but now the code is adding the extra 2 columns in Columns C & D and Columns F & G. Sorry, my mistake Peter. Can you rewrite the code to keep Columns A, B & C. I you can't, no worries as I can simply delete the 4 columns.

NB: Just a reminder, each dept name is replicated 4 times (the reason for this is below each occurrence of the dept name it has the following columns: Budget, YTD Budget, Variance and % Variance therefore Rows 3, 4 and 5 actually have all column headers beacuse it is a SAP BW Extract. I am not bothered much because your code is just working perfectly and I only want to retain the data in Columns A, B and C as per my request above.
 
Upvote 0
.. department names starts in Row 3 in Colum D.
NB: Just a reminder, each dept name is replicated 4 times
Try
Code:
Sub Insert_Columns_v2()
  Dim c As Long
  
  For c = Cells(3, Columns.Count).End(xlToLeft).Column + 1 To 8 Step -4
    Columns(c).Resize(, 2).Insert
    Cells(3, c).Resize(, 2).Value = Array("Reasons for Variance", "Corrective Action")
  Next c
End Sub
 
Upvote 0
Thanks Peter. You are a Super Star. I will definitely try it when I'm on my laptop and respond in due course.
 
Upvote 0
Hi Peter. Mind my ignorance but I have realised that I actually have two files. In the one I used the intial code you have given me and all I changed was "Column To 3 Step -3". It works for me in this file because each Depts name appears 6 times.

However, in my other file I want to retain the data in columns A, B & C, then the Depts names occurs 7 times fom Column D3, so after the 7th occurrence of each depts name I want to insert the two columns with their respective headers. Can you pretty please send me updated code to make this one work and include code that make all the added columns "align left" and "wrap text" so that I can type the reasons and corrective action without manual formatting.
 
Upvote 0
Hi Peter. Mind my ignorance but I have realised that I actually have two files. In the one I used the intial code you have given me and all I changed was "Column To 3 Step -3". It works for me in this file because each Depts name appears 6 times.
If the names appear 6 times each it would be more efficient to 'Step -6'



However, in my other file I want to retain the data in columns A, B & C, then the Depts names occurs 7 times fom Column D3, so after the 7th occurrence of each depts name I want to insert the two columns with their respective headers. Can you pretty please send me updated code to make this one work and include code that make all the added columns "align left" and "wrap text" so that I can type the reasons and corrective action without manual formatting.
Try
Code:
Sub Insert_Columns_v3()
  Dim c As Long
  
  For c = Cells(3, Columns.Count).End(xlToLeft).Column + 1 To 8 Step -7
    Columns(c).Resize(, 2).Insert
    With Columns(c).Resize(, 2)
      .Rows(3).Value = Array("Reasons for Variance", "Corrective Action")
      .HorizontalAlignment = xlLeft
      .WrapText = True
    End With
  Next c
End Sub
 
Upvote 0
Both works, Wow! Thanks Peter, you are very good hey. It will save me 10+ minutes now. One last thing though. I would very much appreciate it if you coud kindly add the "Align Left" and "Wrap Text" to your code below - I tried to add it myself, but it just won't work.

Sub Insert_Columns()

Dim c As Long
For c = Cells(3,Columns.Count).End(xlToLeft).Column To 3 Step -6
If Cells(3,c).Value <> Cells(3, c + 1).Value Then
Columns(c +1).Resize(, 2).Insert
Cells(3, c +1).Resize(, 2).Value = Array("Reasons for Variance", "CorrectiveAction")
End If
Next c
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top