Macro to extend parameters in workbook function

[Username1234]

Hello All,

I currently have a worksheet containing a number of different functions in Excel 2010.
I am after a macro that will be able to extend the parameters for a specified workbook function.

For example...

I would like to have a macro that finds all the CONCATENATE functions in my worksheet and then adds in a number of additional parameters.

So =CONCATENATE(A1,A2) would become =CONCATENATE(A1,A2,A3,A4)

To clarify, I would like "A3" and "A4" added to the end of my CONCATENATE function but not on any other function in the worksheet.

In my actual workbook each CONCATENATE function has parameters in different orders and from different sources so I can not use 'find and replace'.

Any help would be greatly appreciated.

 

[johnjohns]

Perhaps this. If it is to add the two immediate right cells

Sub ExtendConcat()
 Dim OneCell As Range
 Dim TArray As Variant
 Dim LastCell, NewFormula As String
 For Each OneCell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, 23)
   If Left(OneCell.Formula, 12) = "=CONCATENATE" Then
      TArray = Split(OneCell.Formula, ",", -1, vbTextCompare)
      LastCell = Left(TArray(UBound(TArray, 1)), InStr(1, TArray(UBound(TArray, 1)), ")") - 1)
      NewFormula = ""
      For i = 0 To UBound(TArray, 1) - 1
         NewFormula = NewFormula & TArray(i) & ","
      Next i
      NewFormula = NewFormula & Range(LastCell).Address(False, False) _
                              & "," & Range(LastCell).Offset(0, 1).Address(False, False) _
                               & "," & Range(LastCell).Offset(0, 2).Address(False, False) & ")"
      OneCell.Formula = NewFormula
   End If
 Next OneCell
End Sub

 

[Username1234]

Thanks, this is very close to what I am after.

Would it be possible to amend this so the new parameters can be specifically selected opposed to the two cells to the right of the workbook function?

So the two extended parameters could come from anywhere in the worksheet. For example A3 and E9 as the extended parameters with =CONCATENATE(A1,A2) entered in B6.

The finished function coming out as =CONCATENATE(A1,A2,A3,E9)

 

[johnjohns]

Where do you define A3 and E9? Is it a user input while running the macro? Or do you put them as text data in the immediate right cells of the formula?

 

[Username1234]

I was hoping that A3 and E9 could be defined in the macro coding, so instead of having coding that adds the columns to the right of the CONCATENATE function as a parameter it allows you to add A3 and E9 to all CONCATENATE functions in the worksheet.

For example if I had three CONCATENATE functions in a worksheet which were:
=CONCATENATE(A1,A2)
=CONCATENATE(B5,D8)
=CONCATENATE(L3,E5,A7)

I would like to run a macro that would extend these to have the same
extended reference as below:
=CONCATENATE(A1,A2,A3,E9)
=CONCATENATE(B5,D8,A3,E9)
=CONCATENATE(L3,E5,A7,A3,E9)

so A3 and E9 is the extension on all the CONCATENATE functions.

 

[johnjohns]

Change the line

NewFormula = NewFormula & Range(LastCell).Address(False, False) _
                              & "," & Range(LastCell).Offset(0, 1).Address(False, False) _
                               & "," & Range(LastCell).Offset(0, 2).Address(False, False) & ")"

to

NewFormula = NewFormula & Range(LastCell).Address(False, False) _
                              & "," & "$A$3,$E$9)"

 

[Username1234]

Perfect, thanks for your help.