Vba - insert two columns after each occurrence of dept name

[aattwod]

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.

 

[Peter_SSs]

Welcome to the MrExcel board (posting at least )!

Does this do what you want?

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

 

[aattwod]

Hi Peter Tx and much appreciated. I will test it on Sunday and provide some feedback on the success rate.

 

[Peter_SSs]

OK, cheers.

 

[aattwod]

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.

 

[Peter_SSs]

.. department names starts in Row 3 in Colum D.
NB: Just a reminder, each dept name is replicated 4 times

Try

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

 

[aattwod]

Thanks Peter. You are a Super Star. I will definitely try it when I'm on my laptop and respond in due course.

 

[aattwod]

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.

 

[Peter_SSs]

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

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

 

[aattwod]

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