VBA Help - Loop, referencing, concatenate

[raph87]

Hello,

This is my first post. I believe I am pretty good at excel, but I joined the forum in hopes for some assistance.

I have a spreadsheet with column B containing user names (aa1234, ab1234, ac1234, ad1234, etc) There are 500 rows of user names. I would like to concatenate all 500 user names separated by semi colon (aa1234;ab1234;ac1234;ad1234...) Sometimes there is not 500 user names, and sometimes they are not in column B. I created a macro that asks the user which column they would like to concatenate (B:B or C:C or etc), and based on their input the macro loops through the column finding all the user names, adds a ; after them and concatenates all of them until it’s got to 500. It’s not working for me and would like assistance

Let me know if you have any ideas.

I have tried
=concatenate() but it doesn’t work for an array that is vertical
=transpose() for some reason will not work for me, not sure why

What has worked is B1&;&B2&;&B3&;&B4&;...etc but I don’t want to write that formula all the way to 500 and for a static B column.

Suggestions please.

 

[JoeMo]

Here's some code you can adapt. Assumes user names are in column B, starting in B1 and provides output in C1.

Sub ConcatenateRange()
Dim lR As Long, R As Range, cA()
lR = Range("B" & Rows.Count).End(xlUp).Row
Set R = Range("B1", "B" & lR)
ReDim cA(1 To R.Rows.Count)
cA = WorksheetFunction.Transpose(R)
Range("C1").Value = Join(cA, ";")
End Sub

 

[raph87]

Omg. First time post, best time answer.

THank you so much. Helps a ton.

Is there a way I can have the user input "Where the data is" and "where they want the data to be"

1R = InputBox ("Enter the range you would like to concatenate")
I want them to be able to choose a range - Ex B3:B45 but I dont know how to incorporate "& Rows.Count).End(xlUp).Row"

2R = InputBox ("enter the cell you would like to output")
Range(2R).Value = Join(cA, ";")
Is 2R an interget or range?

Thanks again!

 

[raph87]

Omg. First time post, best time answer.

THank you so much. Helps a ton.

Is there a way I can have the user input "Where the data is" and "where they want the data to be"

1R = InputBox ("Enter the range you would like to concatenate")
I want them to be able to choose a range - Ex B3:B45 but I dont know how to incorporate "& Rows.Count).End(xlUp).Row"

2R = InputBox ("enter the cell you would like to output")
Range(2R).Value = Join(cA, ";")
Is 2R an interget or range?

Thanks again!

This is what I have so far. Buttt its not working

Sub ConcatenateRange()
Dim lR As Long, R As Range, cA()
Dim Column As String
Dim Row As Variant
Dim Output As Variant
Dim InputCell As Variant
Dim Prompt As String
Dim Prompt2 As String
Dim Prompt3 As String

Prompt = "Please indicate what column your data begins"
Column = InputBox(Prompt)
Prompt2 = "Please indicate what row your data begins"
Row = InputBox(Prompt2)
lR = Range(Column & Rows.Count).End(xlUp).Row
'InputCell = InputBox(Column, Row)
Set R = Range("B3", Column & lR)
ReDim cA(1 To R.Rows.Count)
cA = WorksheetFunction.Transpose(R)
Prompt3 = "Please indicate what cell you would like your data"
Output = InputBox(Prompt3)
Range(Output).Value = Join(cA, ";")
End Sub

The InputCell part is not working.

In "Set R = Range("B3", Column & lR)" the B3 is want it to be the inputs of the both column and row. So I made InputCell as a range, but its not working.

Need more help please. Thanks a ton

 

[JoeMo]

This version will allow the user to select the data range with the mouse. Note that it places the concatenated string in cell C1. Change the destination cell to suit by changing the last line before "End Sub".

Sub ConcatenateRange()
Dim R As Range, cA()
Set R = Application.InputBox("Select the range with your mouse", Type:=8)
On Error Resume Next
If R Is Nothing Then Exit Sub
On Error GoTo 0
lR = Range("B" & Rows.Count).End(xlUp).Row
ReDim cA(1 To R.Rows.Count)
cA = WorksheetFunction.Transpose(R)
Range("C1").Value = Join(cA, ";")
End Sub

 

[raph87]

This has been very helpful. I have an added complexity that I require assistance. I have placed the execl spreadsheet on a sharepoint site. On a different website users can click "here" and the document will open from the sharepoint. When they try and use my macros it does not work.

My theory: since the spreadsheet is opening from the sharepoint, and the file is not on their computer, the macro doesnt know what to do. Could this be possible? The macros are no referencing the sheet name, but I am confused as to why the macros dont work.
If someone saves the file to thier harddrive, the macros work.

Is there a way to have someone OPEN ONLY your excel doc, from a sharepoint, enable macros, and have the document work?

THank you

This version will allow the user to select the data range with the mouse. Note that it places the concatenated string in cell C1. Change the destination cell to suit by changing the last line before "End Sub".

Sub ConcatenateRange()
Dim R As Range, cA()
Set R = Application.InputBox("Select the range with your mouse", Type:=8)
On Error Resume Next
If R Is Nothing Then Exit Sub
On Error GoTo 0
lR = Range("B" & Rows.Count).End(xlUp).Row
ReDim cA(1 To R.Rows.Count)
cA = WorksheetFunction.Transpose(R)
Range("C1").Value = Join(cA, ";")
End Sub

 

[raph87]

Is there a way to change the file attribute?

The file comes from the Sharepoint and its named getContent [Read Only]. Can I remove the read only?

Set Attr (ThisWorkbook, vbNormal)

THis is not working for me

This has been very helpful. I have an added complexity that I require assistance. I have placed the execl spreadsheet on a sharepoint site. On a different website users can click "here" and the document will open from the sharepoint. When they try and use my macros it does not work.

My theory: since the spreadsheet is opening from the sharepoint, and the file is not on their computer, the macro doesnt know what to do. Could this be possible? The macros are no referencing the sheet name, but I am confused as to why the macros dont work.
If someone saves the file to thier harddrive, the macros work.

Is there a way to have someone OPEN ONLY your excel doc, from a sharepoint, enable macros, and have the document work?

THank you

 

[JoeMo]

Sorry, I have no knowledge of sharepoint. Presumably it is disabling macros.