(Solved) – Need to concatenate with specific condition using VBA user form

  • by
(solved)-–-need-to-concatenate-with-specific-condition-using-vba-user-form

I have 3 columns two have name and surname and 3rd one have an alphabet on the basis of the alphabet we can create concatenate like below:
suppose in column 3 we have an alphabet “I” then Conditions:

1) =CONCATENATE("Are you looking for "&A3&"? Search for "&C3&" here on MSN.com")
2) =CONCATENATE(""&A3&" - Here's the latest information available. Research more on "&C3&" here.")
3) =CONCATENATE("Latest "&A3&" - Don’t look elsewhere for information. Search for "&C3&" here on MSN.com.")

And if we have an alphabet “P” then conditions:

1) =CONCATENATE("If "&A28&" is what you're looking for, these results have got you covered. Search for "&C28&" here.")
2) =CONCATENATE("Top Rated "&A28&" - Don’t waste your money before you see these results. Search for "&C28&" here.")
3) =CONCATENATE("Results for "&A28&", which everyone should see before buying! Research for "&C28&" here.")

So based on that I have created concatenate VBA code but how can I meet the above conditions? Please help me out

Sub Concatenate()
flag = 1
ActiveSheet.Range("A2").End(xlToRight).Offset(0, 2).Select
erow = ActiveCell.CurrentRegion.Rows.Count
srow = 1


Range("A2").End(xlToRight).Offset(0, 2).Select
scol = ActiveCell.Column
ecol = ActiveCell.End(xlToRight).Column


For i = srow To erow

    For j = scol To ecol

        If flag = 1 Then
            Cells(i, ecol   1).Value = Application.WorksheetFunction.Concat(Cells(i, ecol   1).Value, Cells(i, j).Value)
            flag = 0
        Else
            Cells(i, ecol   1).Value = Application.WorksheetFunction.Concat(Cells(i, ecol   1).Value, " ", Cells(i, j).Value)
        End If

    Next j
    flag = 1

Next i


End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *