Issues with a FOR loop & need help with a concatenate macro

[Cyali]

I have a FOR loop set up that basically copies column T from one sheet to column A on another sheet. With the macro I have written, it copies every other cell rather than every cell.

 Sub UpdateExport()
 
 For i = 1 To 50 Step 1
    ActiveWorkbook.Worksheets("Export").Cells(i, 1).FormulaR1C1 = _
        "=IF('Preferences Form'!R[" & i & "]C[19]="""","""",'Preferences Form'!R[" & i & "]C[19])"
 Next i
        
End Sub

gives me

A1: =IF('Preferences Form'!T2="","",'Preferences Form'!T2)
A2: =IF('Preferences Form'!T4="","",'Preferences Form'!T4)
A3: =IF('Preferences Form'!T6="","",'Preferences Form'!T6)

I'm not sure how to edit this so that I get the entire row copied instead of every even cell in the row. I've tried adding another variable x and incrementing that by 1 every iteration, but I got the same result.


Secondly, my client wants me to have some sort of output listing all employee emails as a comma separated list so he can quickly copy/paste that into a To: email field. I already have a code that gathers all of the employees into a single column. How can I take an entire column and have it automatically create a comma separated list that exports either into a specific cell or into a text document? The length of the column will be variable, as well, as it automatically updates any time there is a change in staff, but it should not go over 50 total employees.

[rylo]

Hi

1) issue is with the formula. The [] in the formula means that you are offsetting from the current position by the number contained within the []. So for your first row, it will offset row 1 by 1 thereby referring to row2. If you want to absolute things, then remove the [].

2) The code below will take data from column B and put the concatenated, comma separated string into A1.

Sub aaa()
  holder = ""
  For i = 1 To Cells(Rows.Count, 2).End(xlUp).Row
    holder = holder & Cells(i, 2).Value & ","
  Next i
  holder = Left(holder, Len(holder) - 1)
  
  Range("A1").Value = holder
End Sub

rylo

[Cyali]

The concatenate macro worked perfectly, and I'll be trying your solution to my first problem tonight. Thanks!