Remove a letter from a column and add with the data in the same column

[Jagdev Singh]

Hi Experts,

I need your help in this. I want to fine a column says Sample3, N as per the attached sample in sheet1. Once it find the respective column, remove “, N” from the column header and add the letter N with the data in the entire column Sample3. Please refer the output sheet for better understanding.


Regards,

JD

 

[chirayu]

In the inputbox you put the name of the header you want & it will find the column for it from row 1

Sub N_Exchange()

Dim LstRow As Integer
Dim ValA As Variant
Dim ValB As String
Dim ValC As String

ValA = Application.InputBox("What header name are you looking for?", "Search Criteria")

Range("1:1").Find(What:=ValA, LookIn:=xlValues, LookAt:=xlPart).Activate
ValB = Split(ActiveCell, ", ")(0)
ValC = Split(ActiveCell, ", ")(1)
ActiveCell = ValB
LstRow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row

Do Until ActiveCell.Row = LstRow
    ActiveCell.Offset(1, 0).Select
    ActiveCell = ActiveCell & ValC
Loop

MsgBox "Done", vbInformation, ""

End Sub

 

[jindon]

Do you mean like this?

Sub test()
    Dim r As Range, temp As String
    With Cells(1).CurrentRegion
        For Each r In .Rows(1).Cells
            If r.Value Like "*,*" Then
                temp = Trim$(Split(r.Value, ",")(1))
                r.Value = Split(r.Value, ",")(0)
                With .Columns(r.Column).Offset(1).Resize(.Rows.Count - 1)
                    .Value = Evaluate(.Address & "&""" & temp & """")
                End With
            End If
        Next
    End With
End Sub