How to choose a range to concatenate

%3CLINGO-SUB%20id%3D%22lingo-sub-2026171%22%20slang%3D%22en-US%22%3EHow%20to%20choose%20a%20range%20to%20concatenate%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2026171%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20I%20am%20trying%20to%20concatenate%20a%20range%20of%20cells%20in%20the%20first%20row%20to%20add%20%22positive%20returns%22%20to%20them.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20data%20is%20in%20the%20first%20row%20but%20the%20number%20of%20columns%20may%20vary.%20I%20was%20able%20to%20do%20a%20manual%20one%20(Macro%20with%20some%20modifications%20to%20achieve%20the%20below).%20But%20this%20is%20for%20a%20fixed%20number%20of%20cells.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20PositiveNegativeReturnsTitles()%0A'%0A'%20PositiveReturnsCopiedData%20Macro%0A%0A'%0A%20%20%20%20range(%22E1%22).FormulaR1C1%20%3D%20%22%3DCONCAT(%22%22Positive%20returns%22%22%2C%22%22%20%22%22%2CR1C%5B-3%5D)%22%0A%20%20%20%20range(%22F1%22).FormulaR1C1%20%3D%20%22%3DCONCAT(%22%22Positive%20returns%22%22%2C%22%22%20%22%22%2CR1C%5B-3%5D)%22%0A%20%20%20%20range(%22G1%22).FormulaR1C1%20%3D%20%22%3DCONCAT(%22%22Positive%20returns%22%22%2C%22%22%20%22%22%2CR1C%5B-3%5D)%22%0A%20%20%20%20range(%22H1%22).FormulaR1C1%20%3D%20%22%3DCONCAT(%22%22Negative%20returns%22%22%2C%22%22%20%22%22%2CR1C%5B-6%5D)%22%0A%20%20%20%20range(%22I1%22).FormulaR1C1%20%3D%20%22%3DCONCAT(%22%22Negative%20returns%22%22%2C%22%22%20%22%22%2CR1C%5B-6%5D)%22%0A%20%20%20%20range(%22J1%22).FormulaR1C1%20%3D%20%22%3DCONCAT(%22%22Negative%20returns%22%22%2C%22%22%20%22%22%2CR1C%5B-6%5D)%22%0A%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20creating%20an%20array%20for%20the%20values%20in%20the%20range%20but%20this%20did%20not%20seem%20to%20work%20(I%20got%20this%20from%20a%20Youtube%20guide%20but%20changed%20the%20cell%20reference%20to%20get%20all%20the%20values%20in%20the%20single%20row%20of%20every%20column%20up%20until%20the%20last%20non-blank%20cell).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESub%20range()%0A'%201.%20Find%20the%20range%20of%20data%20(headers).%0A'%20Create%20a%20variable%20that%20searches%20a%20range%0A%20%20%20%20Dim%20headers%20As%20range%0A%20%20%20%20Set%20headers%20%3D%20shData.range(rows(1)).CurrentRegion%0A%20%20%20%20%0A%20%20%20%20%0A%20%20%20%20'%20In%20the%20first%20row%20of%20every%20column%20up%20until%20the%20last%20non-blank%20column%0A%20%20%20%20Dim%20arr%20As%20Variant%0A%20%20%20%20'%20Get%20the%20values%20for%20each%20cell%20for%20each%20value%20in%20the%20range%20as%20an%20array%0A%20%20%20%20arr%20%3D%20headers.Value%0A%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20assistance%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2026171%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional 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