variable output into a new variable name and paste onto cell

[smakatura]

I have 2 variables/phrases that are needed and trying to combine them to be one string of text

dim Variable1 as string
dim Combined as string

' set old variable to be value in A1'
range ("A1").select
activecell.value = Variable1

'Set combined variable....this should be variable 1 and then the text below'
Combined = &Variable1& & ", Bob"

'put combined value in a2
Range("A2").select
activecell.value = combined

so...if "Doug" is the value in A1, the value that would be put in A2 is "Doug, Bob"

I am having issues with the combined part and I am sure it is just simple syntax

 

[Joe4]

Try this:

Dim Variable1 as String
Dim Combined as String

' set old variable to be value in A1'
Variable1 = Range("A1")

'Set combined variable....this should be variable 1 and then the text below'
Combined = Variable1 & ", Bob"

'put combined value in a2
Range("A2") =  Combined

But you actually can do this without any variables in a single line of code, like this:

Range("A2) = Range("A1") & ", Bob"

 

[smakatura]

that could work but I am actually doing a concatenate formula and not filling in multiple cell values. I think I need the variables.

I have column A that has an unknown number of rows. so today it could be 3, tomorrow it could be 40

I am using concatenate to do this and a counter

so I have
Column A
1 Bob
2 Doug
3 Sam

I want cell result to be "Bob, Doug, Sam" in cell B1


if there are 5 rows
Column A
1 Bob
2 Doug
3 Sam
4 Mary
5 Joan

I want the result to be "Bob, Doug, Sam, mary, Joan"

I wanted to use variable in the macro so that I am only filling in one cell

 

[Joe4]

that could work but I am actually doing a concatenate formula and not filling in multiple cell values. I think I need the variables.

That's fine. My first block of code in my last post shows you how to do that.

If you are looping through an unknown number of cells, you would have code something like this:

Sub MyCombine()

    Dim lrow As Long
    Dim cell As Range
    Dim combined As String
    
'   Find last cell in column A with data
    lrow = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all cells in range
    For Each cell In Range("A1:A" & lrow)
        combined = combined & cell & ", "
    Next cell
    
'   Remove last comma and space
    If Len(combined) > 2 Then
        combined = Left(combined, Len(combined) - 2)
    End If
    
'   Place in first blank cell underneath last row
    Range("A" & lrow + 1) = combined
        
End Sub

 

[smakatura]

thanks for the help

the only issue I was having with the Lrow that you have above is that when the data is pasted in it leaves formatting and funny things so I needed to add a cell that just had the max number of rows and use that as my counter. I did this by using a separate formula in a column on the sheet (=IF(C3="","",W2+1) ), the counter cell pulls the max value from column W.

I put together this code that works

Sub concatenate_unknown_number_of_rows()
'
    Dim NumColumns As Integer
    Dim OldFormula As String
    Dim NewFormula As String
    Dim Counter As Integer
    
' Select [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=rows]#rows[/URL]  with values from sheet and set as number of columns for loop
    Range("AA2").Select '**cell AA2 is set to the max value of a column with a counter in it - each row that has a value adds one to this number via a formula on sheet
    NumColumns = ActiveCell.Value
    
' set initial counter
    Counter = 2
' set initial old formula
    OldFormula = "=CONCATENATE(RC[-21],"", "",R[1]C[-21]"
   
' loop to create formula for cell
    Do While Counter <= NumColumns
               
        NewFormula = OldFormula & ","", "",R[" & Counter & "]C[-21]"
        Range("x2") = NewFormula & ")"
        OldFormula = NewFormula
            
        Counter = Counter + 1
    Loop
    
 
End Sub