Jan 01 2021 01:55 PM
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