CONCATENATE in VBA?

[sleggeauto]

Hello guys.

Writing here, hoping that someone can help me out.

I have two formuls on "Sheet 1" that looks like this:

=CONCATENATE(A13;",";B13;",";C13;",";D13;",";E13;",";F13;",";G13;",";H13;",";I13)
=CONCATENATE(A14;",";B14;",";C14;",";D14;",";E14;",";F14;",";G14;",";H14;",";I14)

I want ONE button which I can click to make the results of those formulas to appear in cell "G2" and "H2" at "Sheet 2".

From what I have understand, the only way that is possible is by creating a macro. I have tried for 3 days now googling and tinkering, but I cannot get it right.

Biiiiig thank you in advance!

 

[Joe4]

Welcome to the Board!

Here is one way:

Sub MyConcatenate()

    Dim myCol As Long
    Dim myRow As Long
    Dim myString As String
    
    For myRow = 13 To 14
        myString = ""
        For myCol = 1 To 9
            myString = myString & Sheets("Sheet1").Cells(myRow, myCol) & ","
        Next myCol
        myString = Left(myString, Len(myString) - 1)
        Sheets("Sheet2").Cells(2, myRow - 6) = myString
    Next myRow
        
End Sub

 

[Fluff]

As you haven't said where in sheet1 the formulae are I've assumed J13 & J14

Sub GetValues()

With Sheets("Sheet2")
   .Range("G2").Value = Sheets("sheet1").Range("J13").Value
   .Range("H2").Value = Sheets("sheet1").Range("J14").Value
End With
End Sub

 

[Rick Rothstein]

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub RangeConcatenation()
  Sheets("Sheet2").Range("G2") = Join(Application.Index(Sheets("Sheet2").Range("A13:I13").Value, 1, 0), ",")
  Sheets("Sheet2").Range("H2") = Join(Application.Index(Sheets("Sheet2").Range("A14:I14").Value, 1, 0), ",")
End Sub[/td]
[/tr]
[/table]

 

[sleggeauto]

Give this macro a try...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub RangeConcatenation()
  Sheets("Sheet2").Range("G2") = Join(Application.Index(Sheets("Sheet2").Range("A13:I13").Value, 1, 0), ",")
  Sheets("Sheet2").Range("H2") = Join(Application.Index(Sheets("Sheet2").Range("A14:I14").Value, 1, 0), ",")
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Thank you everyone, I am overwhelmingly impressed by your skills and fast responses!

I decided to try Rick´s script, as it looked for me like the easiest one for me to understand.



I hope I am allowed to ask for a follow-up request :

Cell A2 on "Sheet 2" contains the referance number (100000).
Cell A3 on "Sheet 2" contains the referance number (100001).
Cell A4 on "Sheet 2" contains the referance number (100002).
And so on..

I have a data validation drop-down list in "J18" at "Sheet 1" where I can find all the referance numbers in the "A"-column on "Sheet 2".

Is it possible to have a macro that does the following?

On activation, use the referance number at "J18" list (Sheet 1) to move back the concatenated values from the correct row at "Sheet 2" and back into A13 to I13 at "Sheet 1" ?

The referance number will be used on "Sheet 2" to figure out which row my data is stored at.

For example:

Number 100000 = G2 and H2
Number 100001 = G3 and H3
Number 100002 = G4 and H4




I know this is probably a tough one, and I should be very pleased if I even managed to describe it so that you can understand it.

Thank you in advance.

 

[arunsjain]

Hi,

Very useful post on concatenate VBA

https://www.excelcampus.com/keyboard-shortcuts/concatenate-range-of-cells/

Cheers !!!!!!