Autofill the values from found columns

[Goku1]

Hi all,
I am trying to find two columns by their name in a sheet, viz. Column "Fruits" and column "Vegetables" and then trying to paste these columns's merged value in a new column.


My code

Sub Merge_B&C()
Dim WrkBk1 As Workbook
Dim WrkBk2 As Workbook
Set WrkBk2 = ActiveWorkbook
Set WrkBk1 = ThisWorkbook
Dim ws2 As Worksheet
Set ws2 = WrkBk2.Sheets(1)
    Dim xRg As Range
    Dim xRgUni As Range
    Dim xFirstAddress As String
    Dim xStr As String
    On Error Resume Next
    
    
    
    ' Insert a column in the beginning
  ws2.Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
'Search for Fruits Column
    xStr = "Fruits"
    Set xRg = ws2.Range("A1:CA1").Find(xStr, , xlValues, xlWhole, , , True)
    If Not xRg Is Nothing Then
        xFirstAddress = xRg.Address
        Do
            Set xRg = ws2.Range("A1:CA1").FindNext(xRg)
            If xRgUni Is Nothing Then
                Set xRgUni = xRg
            Else
                Set xRgUni = Application.Union(xRgUni, xRg)
            End If
        Loop While (Not xRg Is Nothing) And (xRg.Address <> xFirstAddress)
    End If
    

'Search for Vegetables Column

Dim xRg1 As Range
    Dim xRgUni1 As Range
    Dim xFirstAddress1 As String
    Dim xStr1 As String

    xStr1 = "Vegetables"
    Set xRg1 = ws2.Range("A1:CA1").Find(xStr1, , xlValues, xlWhole, , , True)
    If Not xRg1 Is Nothing Then
        xFirstAddress = xRg1.Address
        Do
            Set xRg1 = ws2.Range("A1:CA1").FindNext(xRg1)
            If xRgUni1 Is Nothing Then
                Set xRgUni1 = xRg1
            Else
                Set xRgUni1 = Application.Union(xRgUni1, xRg1)
            End If
        Loop While (Not xRg1 Is Nothing) And (xRg1.Address <> xFirstAddress)
    End If
    
    
'Select A2
   ws2.Range("A2").Select

'Write A2 = B2&C2 and Autofill
With ws2.Range("a2", Range("a" & Rows.Count).End(xlUp))
        .FormulaR1C1 = "=RC[xRgUni.Offset(1, 0)]&RC[xRgUni1.Offset(1, 0)]"
        End With

        
End Sub

What I tried is this, but it pastes the values only and I cannot Autofill for the whole column! It would be great if someone can set me on the right track. Thank you.

With ws2.Range("a2", Range("a" & Rows.Count).End(xlUp))
        .Value = ((xRgUni.Offset(1, 0).Value) & (xRgUni1.Offset(1, 0).Value))
        End With

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Autofill the values from found columns
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.