Autofill the values from found columns

Goku1

New Member
Joined
Mar 18, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
VBA 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.

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

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,181
Messages
5,640,640
Members
417,159
Latest member
Mayozero

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top