Adding conditional column title - VBA

[ana_c]

Hi,
I'm new to VBA and would greatly appreciate your help with this query

I have a sheet that looks like this

I would like to add column titles in the highlighted cells based on the first left column that is not blank. For e.g. D1 will have the title -> C2+"_1" a.k.a "Column2_1" and cell E1 will be "Colum2_2".
Similarly in cell O1, it will be "Column5_1", P1="Column5_2" etc.

In total I have over 100 columns to go through and apply the same formula, however since these are header titles, we can restrict it to row 1.

Final desired output -

My data starts in B1

Thanks

 

[Peter_SSs]

Assuming
- that cell A1 has something in it
- there is at least one blank cell in row 1
try this with a copy of your workbook.

Sub Add_Titles()
  Dim c As Range
  
  For Each c In Rows(1).SpecialCells(xlBlanks)
    c.Value = Split(c.Offset(, -1).Value, "_")(0) & "_" & Split(c.Offset(, -1).Value & "_0", "_")(1) + 1
  Next c
End Sub

 

[ana_c]

Brilliant! Thanks a million Peter
And yes, your assumptions are right

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[ana_c]

Hi Peter,

Just wanted to follow up with a related question.

For some reason the code above does not work with a column title "S1Q7_r1".
I get a run-time debug error.
It works fine for column titles that are in the format "S1Q1_1" for example

Can you help me understand what is wrong?

The blank cells are well I would like the code to work and display
cell AV1 should be: "S1Q7_r1_1"
cell AW1 should be: "S1Q7_r1_2"
etc.

 

[Peter_SSs]

For some reason the code above does not work with a column title "S1Q7_r1".
I get a run-time debug error.
It works fine for column titles that are in the format "S1Q1_1" for example

Can you help me understand what is wrong?

The reason is that the logic before was to take the number after the "_" and increment it by 1. In this latest scenario, what is after the "_" is not a number so you cannot add 1 to it.

Try this instead

Sub Add_Titles_v2()
  Dim c As Range
  Dim Bits As Variant, Suff As Variant
  
  For Each c In Rows(1).SpecialCells(xlBlanks)
    Bits = Split(c.Offset(, -1).Value, "_")
    Suff = Bits(UBound(Bits))
    If UBound(Bits) = 0 Or Not IsNumeric(Suff) Then
      c.Value = c.Offset(, -1).Value & "_1"
    Else
      Bits(UBound(Bits)) = vbNullString
      c.Value = Join(Bits, "_") & Suff + 1
    End If
  Next c
End Sub

 

[ana_c]

Thanks for the explanation and the updated code.

It works well except at the end of my sheet where there are about 160 columns headers added even though there is no data in any of those columns.
In the picture below, the macro has added titles in BV-CQ, however I don't want the macro to run in BV since that column is a blank column as a whole

 

[Peter_SSs]

It works well except at the end of my sheet where there are about 160 columns headers added even though there is no data in any of those columns.

There must be, or have been, something in those columns somewhere, sometime for them to be included in SpecialCells(xlBlanks).
If we are not to use SpecialCells(xlBlanks) in row 1 to determine where to end, how will we determine where to end?
What is the method/logic to determine the final column that you want processed?

 

[ana_c]

There must be, or have been, something in those columns somewhere, sometime for them to be included in SpecialCells(xlBlanks).
If we are not to use SpecialCells(xlBlanks) in row 1 to determine where to end, how will we determine where to end?
What is the method/logic to determine the final column that you want processed?

I think the logic would be to scan and check the last column that has no values in it (example column BV:BV) and stop there.

 

[Peter_SSs]

I think the logic would be to scan and check the last column that has no values in it

Surely that would be column XFD?

Can I safely use row 2 to determine the last column that does have data and stop there?
Or might row 2 have last data in column BV but row 10 might go further, say to column CA?