Combine and copy user selected columns

[Richie70]

Hi everyone,

I'm having trouble with this and my noob brain can't figure it out.

I want my macro to look for two columns, Part and Rev. Then combine the values with "_" in between and put the result in a new column and copy the formula to the last row.
This code does what i want but only if i hard code the columns - instead of using the user selected one.

If you can find a few mins to help me fix this I'd be very grateful

Sub macro6() 'posted to MrExcel for help
    Dim ws As Worksheet
    Dim aCell As Range, aRng As Range
    Dim bCell As Range, bRng As Range
    Dim aCol As Long, alRow As Long
    Dim bCol As Long, blRow As Long
    Dim aColName As String
    Dim bColName As String

    '~~> Change this to the relevant sheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    With ws
        Set aCell = .Range("A1:ZZ1").Find(What:="Part", LookIn:=xlValues, LookAt:=xlWhole, _
                    MatchCase:=False, SearchFormat:=False)
        '~~> If Found
        If Not aCell Is Nothing Then
            Else
            MsgBox "Part Column Not Not Found"
        End If
    End With
   
    With ws
        Set bCell = .Range("A1:ZZ1").Find(What:="Rev", LookIn:=xlValues, LookAt:=xlWhole, _
                    MatchCase:=False, SearchFormat:=False)
        '~~> If Found
        If Not bCell Is Nothing Then
            Else
            MsgBox "Rev Column Not Not Found"
        End If
    End With

    'get lastrow
    Dim LastRow As Long
    LastRow = Cells(Rows.Count, "A:A").End(xlUp).Row
    'MsgBox LastRow
   
    'Combine & place part# & rev# in col AA
    Range("AA2:AA" & LastRow) = Evaluate("C2&""_""&I2:I" & LastRow)
 
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub macro6() 'posted to MrExcel for help
    Dim ws As Worksheet
    Dim aCell As Range, aRng As Range
    Dim bCell As Range, bRng As Range
    Dim aCol As Long, alRow As Long
    Dim bCol As Long, blRow As Long
    Dim aColName As String
    Dim bColName As String

    '~~> Change this to the relevant sheet
    Set ws = ThisWorkbook.Sheets("sheet1")
    With ws
        Set aCell = .Range("A1:ZZ1").Find(What:="Part", LookIn:=xlValues, LookAt:=xlWhole, _
                    MatchCase:=False, SearchFormat:=False)
        '~~> If Found
        If aCell Is Nothing Then
           MsgBox "Part Column Not Not Found"
           Exit Sub
        End If
    
        Set bCell = .Range("A1:ZZ1").Find(What:="Rev", LookIn:=xlValues, LookAt:=xlWhole, _
                    MatchCase:=False, SearchFormat:=False)
        '~~> If Found
        If bCell Is Nothing Then
            MsgBox "Rev Column Not Not Found"
            Exit Sub
        End If

    'get lastrow
    Dim LastRow As Long
    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    'MsgBox LastRow
   
    'Combine & place part# & rev# in col AA
    .Range("AA2:AA" & LastRow).FormulaR1C1 = "=rc" & aCell.Column & "&""_""&rc" & bCell.Column
   End With
 
End Sub

 

[Richie70]

That sir, is fabulous! Thank you very much indeed.

 

[Fluff]

You're welcome & thanks for the feedback.