Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autoamate Concatenate Function
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autoamate Concatenate Function
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 --------------- Jacob Skaria |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autoamate Concatenate Function
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" wrote: 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 --------------- Jacob Skaria |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autoamate Concatenate Function
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 --------------- Jacob Skaria |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenate: in an IF Function | Excel Worksheet Functions | |||
CONCATENATE/& Function ? | Excel Discussion (Misc queries) | |||
concatenate function help | Excel Discussion (Misc queries) | |||
Concatenate Function | Excel Discussion (Misc queries) | |||
Concatenate Function | Excel Programming |