Autoamate Concatenate Function

[Cheffred]

I want to use the Concatenate function below, but I need to use it multiple
times on a large spreadsheet. How can I get Excel to do this automatialcly
based on a particular word in Column a?

Range("A9").Select
Selection.Insert Shift:=xlToRight
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(RC[1],R[1]C[1],R[1]C[2],R[1]C[3],R[1]C[4])"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("B9:J9").Select
Application.CutCopyMode = False
Selection.ClearContents

 

[Jacob Skaria]

Dear Cheffred

Use & (ampersand) instead.
Range("E1")=Range("A1") & Range("B1") & Range("C1") & Range("D1")

From VBA if you have more cells use a loop to loop through the range and
concateneate to a variable.

If this post helps click Yes

 

[Cheffred]

That part makes it easier, but what I need is for excel to look through my
spreadsheet and every time col 1 = "machine", for example, to run the
function.

 

[Jacob Skaria]

Please write the code in the below procedure.

Open VBE using Alt+F11, Double click on This Workbook, List Workbook_Sheet
change event and past the below code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Range("A1") = "machine" then
Range("E1")=Range("A1") & Range("B1") & Range("C1") & Range("D1")
End If
End Sub


If this post helps click Yes