Concatenate 2 column values if conditions met using VBA

[edwardj3]

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

 

[Fluff]

Replace ActiveCell with cell

 

[davesexcel]

You are using activecell.offset(,1)

But activecell is A1

try

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]

You are using activecell.offset(,1)

But activecell is A1

try

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.