VBA to concatenate based on Column header name

[Jonnathanjons]

Hello Dear ,

Could you please help me in concatenating the columns based on Column name and not by specific range as the data keep changing every week.

Result should be in the Column "Billing" This column should be created after last available column provided.

This is the input file


Result should be like this


Below is the vb script I wrote so far.

>>> You've already noted <<<
>>> use code - tags <<<​

Sub concat()

Dim Counter As Integer
    Dim Bill As Variant
    Dim Lab As Variant
    Dim Ops As Variant
    Dim Fin As Variant
  
    Set ws = ActiveSheet

    Rows(1).Find("*", , xlValues, , xlByColumns, xlPrevious).Offset(, 1).Value = ">>"
    Rows(1).Find("*", , xlValues, , xlByColumns, xlPrevious).Offset(, 1).Value = "Billing"
  
    Set Bill = Rows("1:1").Find("Billing", , xlValues, xlWhole, , , True).Offset(1, 0)
    Set Lab = Rows("1:1").Find("Lab", , xlValues, xlWhole, , , True)
    Set Ops = Rows("1:1").Find("Operations", , xlValues, xlWhole, , , True)
    Set Fin = Rows("1:1").Find("Finance", , xlValues, xlWhole, , , True)
  
For Counter = 2 To WorksheetFunction.CountA(Range("A:A"))
      
    Range(Bill & Counter) = Range(Lab & Counter) & "-" & Range(Ops & Counter) & "-" & Range(Fin & Counter)
    'Range("G" & Counter) = Range("C" & Counter) & "-" & Range("D" & Counter) & "-" & Range("E" & Counter)
  
Next Counter

End Sub

 

[Marc L]

Hello, why not using a very easy beginner level instant formula rather than a slower useless VBA procedure ?!​

​

 

[Jonnathanjons]

Hello, why not using a very easy beginner level instant formula rather than a slower useless VBA procedure ?!​

​

Well this is a part of a problem broken down to this piece. I got the other part done. Also what's more easier than a macro run it in less than a sec.

 

[Jonnathanjons]

@vletm : A script is required as explained above to Marc. Thank You.

Jonnathanjons: You should able to write something new here.
You've given some code, but ... is there something missing? ... or what is Your point?

 

[Marc L]

So according to « Result should be in the Column "Billing" This column should be created after last available column provided »​

according to your attachment an Excel basics formula VBA demonstration as a beginner starter :​

Sub Demo1()
         Const C = "&""-""&"
    With Sheet16.UsedRange.Columns
        .Item(.Count + 1) = .Parent.Evaluate(.Item(3).Address & C & .Item(4).Address & C & .Item(5).Address)
        .Cells(1, .Count + 1) = "Billing"
        .Cells(1, .Count + 1).Font.Bold = True
        .Cells(1, .Count + 1).Interior.Color = .Cells(1).Interior.Color
        .Item(.Count + 1).AutoFit
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[Jonnathanjons]

Hello Thanks for your response. However as mentioned in the topic "VBA to concatenate based on Column header name" Can you please modify the script to concatenate based on Column header name ie "Lab" , " Operations" ,"Finance" and not based on a specific range in the file .These columns keeps changing every time but these column name remain the same. Hence this vba script is required.
If you see my script I tried to create reference for each of these column header with
"Set Bill = Rows("1:1").Find("Billing", , xlValues, xlWhole, , , True)" This is for the result to appear
Set Lab = Rows("1:1").Find("Lab", , xlValues, xlWhole, , , True) . to pick the refernce for Column "Lab" and concatenate
similarly for other 2

You script looks Can you please modify your script or my script?

 

[Marc L]

According to your initial post I thought you were able to fit my starter demonstration …​

​

 

[Jonnathanjons]

The only change I would like to have in my initial post is to incorporate this line for dynamic changes in the input file.
Range("G" & Counter) = Range("C" & Counter) & "-" & Range("D" & Counter) & "-" & Range("E" & Counter)..

I tried to changed the line to
Range(Bill & Counter) = Range(Lab & Counter) & "-" & Range(Ops & Counter) & "-" & Range(Fin & Counter). But I am sure something is missing.

Of if you can rewrite the script to a more clever one , I will be grateful.

 

[Marc L]

According to Excel rules and basics - so without any useless slow loop - my demonstration revamped as the last beginner starter :​

Sub Demo1r()
         Const C = "&""-""&"
    With Sheet16.UsedRange.Columns
         V = Application.Match([{"Lab","Operations","Finance","Billing"}], .Rows(1), 0)
         If Application.Count(V) <> 3 Then Beep: Exit Sub
        .Item(.Count + 1) = .Parent.Evaluate(.Item(V(1)).Address & C & .Item(V(2)).Address & C & .Item(V(3)).Address)
        .Cells(1, .Count + 1) = "Billing"
        .Cells(1, .Count + 1).Font.Bold = True
        .Cells(1, .Count + 1).Interior.Color = .Cells(1).Interior.Color
        .Item(.Count + 1).AutoFit
    End With
End Sub

► ► Do you like it ? So thanks to click on bottom right Like ! ◄ ◄​

​

 

[Jonnathanjons]

Brilliant. Thanks much very helpful.

 

[Marc L]

Thanks !​

Before to go on VBA side learning Excel basics is more efficient, it could even avoid any VBA useless procedure or at least simplify the code …​

​

 

[Jonnathanjons]

Thanks !​

Before to go on VBA side learning Excel basics is more efficient, it could even avoid any VBA useless procedure or at least simplify the code …​

​

I wish I knew !!