Using VBA to combine headers (based on name) and concatenate columns based on conditions

Tom224

New Member
Joined
Mar 9, 2020
Messages
2
Office Version
365
Platform
Windows
In ponctualy receive a file that I would like to reorganize.

I am thus looking for specific headers names and either renaming them and copying data or doing more complex operations.

In the simplest case, I am only renaming columns and pasting the new columns headers and content in a second sheet.
I am looking for a column named "Spec A" and renaming it "Nabou"

For a more complex case, I am creating a new column by concatenating columns.
However, based on wether or not the information is present in other columns, I am adding a specific text, which can change in various cases.
For example, I am concatenating a sevaral columns "nup", "nap", and adding "WAGA" for rows with values located below some specific headers, and adding "CIOCOLATO" for the rows with no values located in these same headers.
The two possible results being:
  • nup_nap_WAGA_Snip (for the caeses when specific rows have values below)
  • nup_nap_CIOCOLATO_Snip (for the cases when rows below specific headers have no values)
For the worst case, in this same file, I am creating new columns, by concatenating these columns, but I am also appending a specific number in some cases.
In oder to know the number that I am incrementing, I need to look on another exel file (another worksheet) to add a specific input in the increment, which should be increment based on specific condition.

Finally, I am also concatenating columns and their content with a line break.


I have the following code

VBA Code:
Option Explicit

Sub Snouba()

    Const q = """"

' get source data table from sheet 1
    With ThisWorkbook.Sheets(1).Cells(1, 1).CurrentRegion
        ' check if data exists

        If .Rows.Count < 2 Or .Columns.Count < 2 Then
            MsgBox "No data table"
            Exit Sub
        End If

        ' retrieve headers name and column numbers dictionary
        Dim headers As Object
        Set headers = CreateObject("Scripting.Dictionary")
        Dim headCell
        For Each headCell In .Rows(1).Cells
            headers(headCell.Value) = headers.Count + 1
        Next

        ' check mandatory headers
        For Each headCell In Array("Nabou", "Wurp", "Scope 1", "Scope 2", "Scope 3”, "Scope 4", "NipandNup")
            If Not headers.Exists(headCell) Then
                MsgBox "Header '" & headCell & "' doesn't exists"
                Exit Sub
            End If

        Next

        Dim data

        ' retrieve table data
        data = .Resize(.Rows.Count - 1).Offset(1).Value
    End With

    ' process each row in table data
    Dim result As Object
    Set result = CreateObject("Scripting.Dictionary")
    Dim i
    For i = 1 To UBound(data, 1)


     Select Case True
            Case _
                data(i, headers("NipandNup")) = "Nip"
                    MsgBox "Empty row"
                    Exit For

            Case _
                  result(result.Count) = "Nip"

            Case Else
                     result(result.Count) = "Nup"

               End Select








        Select Case True
            Case _
                data(i, headers("Nabou")) = "" Or _
                data(i, headers(""Wurp")) = "" Or _
                data(i, headers("NipandNup")) = ""
                    MsgBox "Empty row"
                    Exit For
Case _
                data(i, headers("Scope 1")) = "" And _
                data(i, headers("Scope 2")) = "" And _
                data(i, headers("Scope 3")) = "" And _
                data(i, headers("Scope 4")) = ""
                    result(result.Count) = _
                        data(i, headers("Nabou")) & _
                        "_Alpha" & _
                        "_" & data(i, headers("Wurp")) & _
                        "_" & data(i, headers("NipandNup"))



Case Else
result(result.Count) = _
                        data(i, headers("Nabou")) & _
                        "_Alphabet" & _
                        "_" & data(i, headers("Wurp")) & _
                        "_" & data(i, headers("NipandNup"))

End Select

Next

' output result data to sheet 2
If result.Count = 0 Then
        MsgBox "No result data for output"
        Exit Sub
End If
With ThisWorkbook.Sheets(2)
.Cells.Delete
.Cells(1, 1).Resize(result.Count).Value = _
WorksheetFunction.Transpose(result.Items())
End With
    MsgBox "Completed"

End Sub
And I am trying to merge it and improve the code to change the colomns name:

VBA Code:
Option Explicit

Sub Changeheadername()

    Dim lastCol As Long, idCount As Long, nameCount As Long, headerRow As Long
    Dim rng As Range, cel As Range

    headerRow = 1       'row number with headers
    lastCol = Cells(headerRow, Columns.Count).End(xlToLeft).Column 'last column in header row
    idCount = 1
    nameCount = 1
    Set rng = Sheets("Sheet1").Range(Cells(headerRow, 1), Cells(headerRow, lastCol)) 'header range

    For Each cel In rng                     'loop through each cell in header
        If cel = "Wurp" Then             'check if header is "Wurp"
            cel = "Snouba"                    'rename 

        ElseIf cel = "Nabou" Then       'check if header is "Nabou"
            cel = "WAGD"                     'rename 

              ElseIf cel = "Scope 1" Then       'check if header is "Scope 1"
            cel = "I am an a wise rabbit"             

        End If
    Next cel
End Sub

I am also trying to improve and merge this macro in which I am concatenating columns with a line break.

Do you know to merge these VBA macro ?
Do you know how these macros could be improved ?
 

Attachments

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Watch MrExcel Video

Forum statistics

Threads
1,102,907
Messages
5,489,644
Members
407,703
Latest member
Chibuzo

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top