Concatenate 2 column values if conditions met using VBA

edwardj3

New Member
Joined
Jan 16, 2018
Messages
30
Hi all,

I want to concatenate 2 columns if column A is Not blank and column B cells contains the text "Enquiry" which is part of a longer string but will always be present.

I want to go down all of Column A and Column B and return result column A but this isn't happening with the code below. Any suggestions to resolve this would be appreciated.

Sub concat()

Dim LR As Long 'LR = Last Row
LR = Cells(Rows.Count, 1).End(xlUp).Row

Range("a1").Select

For Each cell In ActiveSheet.Range("a1:a" & LR)
If cell <> "" And (ActiveCell.Offset(0, 1)) Like "*Enq*" Then
cell.Value = cell.Value & ActiveCell.Offset(0, 1).Value
End If
Next cell

End Sub
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows
Replace ActiveCell with cell
 

davesexcel

Well-known Member
Joined
Feb 26, 2006
Messages
1,089
You are using activecell.offset(,1)

But activecell is A1

try
VBA Code:
Sub concat()

    Dim LR As Long                               'LR = Last Row
    LR = Cells(Rows.Count, 1).End(xlUp).Row

    'Range("a1").Select

    For Each cell In ActiveSheet.Range("a1:a" & LR)
        If cell <> "" And cell.Offset(0, 1) Like "*Enq*" Then
            cell.Value = cell.Value & cell.Offset(0, 1).Value
        End If
    Next cell

End Sub
 

edwardj3

New Member
Joined
Jan 16, 2018
Messages
30
You are using activecell.offset(,1)

But activecell is A1

try
VBA Code:
Sub concat()

    Dim LR As Long                               'LR = Last Row
    LR = Cells(Rows.Count, 1).End(xlUp).Row

    'Range("a1").Select

    For Each cell In ActiveSheet.Range("a1:a" & LR)
        If cell <> "" And cell.Offset(0, 1) Like "*Enq*" Then
            cell.Value = cell.Value & cell.Offset(0, 1).Value
        End If
    Next cell

End Sub

Thank you for providing the updated code. Worked exactly as I hoped.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,957
Messages
5,621,823
Members
415,859
Latest member
Vain

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