How to choose a range to concatenate

Copper Contributor

So I am trying to concatenate a range of cells in the first row to add "positive returns" to them. 

 

The data is in the first row but the number of columns may vary. I was able to do a manual one (Macro with some modifications to achieve the below). But this is for a fixed number of cells. 

 

 

Sub PositiveNegativeReturnsTitles()
'
' PositiveReturnsCopiedData Macro

'
    range("E1").FormulaR1C1 = "=CONCAT(""Positive returns"","" "",R1C[-3])"
    range("F1").FormulaR1C1 = "=CONCAT(""Positive returns"","" "",R1C[-3])"
    range("G1").FormulaR1C1 = "=CONCAT(""Positive returns"","" "",R1C[-3])"
    range("H1").FormulaR1C1 = "=CONCAT(""Negative returns"","" "",R1C[-6])"
    range("I1").FormulaR1C1 = "=CONCAT(""Negative returns"","" "",R1C[-6])"
    range("J1").FormulaR1C1 = "=CONCAT(""Negative returns"","" "",R1C[-6])"

End Sub

 

 

 

I tried creating an array for the values in the range but this did not seem to work (I got this from a Youtube guide but changed the cell reference to get all the values in the single row of every column up until the last non-blank cell).

 

 

Sub range()
' 1. Find the range of data (headers).
' Create a variable that searches a range
    Dim headers As range
    Set headers = shData.range(rows(1)).CurrentRegion
    
    
    ' In the first row of every column up until the last non-blank column
    Dim arr As Variant
    ' Get the values for each cell for each value in the range as an array
    arr = headers.Value

End Sub

 

 

Any assistance? 

 

Thanks

0 Replies