wwhit
02-16-2012, 10:14 AM
I have the following code for a catenate:
Sub CatenateIt()
'replace A1:G2 below with your own range
'DEMO: concatenate A1 to G2
Call GetUserRange
ActiveCell.Value = Catenate(Range("A1:IU1"), " ")
End Sub
'<< Function to concatenate cells in a range >>
Public Function Catenate(MyRange As Range, _
Optional Delimiter As String) As String
Dim Cell As Range, N As Long
N = 1
'go thru MyRange cell by cell and concatenate
For Each Cell In MyRange
If N = MyRange.Cells.Count Then
'we don't need a delimiter after last cell
Else
'otherwise we do need a delimiter
End If
N = N + 1
Catenate = Catenate & "'" & Cell & "'" & ", "
Next Cell
Set Cell = Nothing
End Function
And also this code that is called from the above:
Sub GetUserRange()
Dim UserEntry As String
UserEntry = InputBox("Select cell column for the output - Like A1:G1.", "Select Range.")
If UserEntry <> " " Then ActiveCell.Value = UserEntry
End Sub
I would like to know how to get the ranges in the Sub CatenateIt to be the UserEntry from GetUserRange InputBox. So instead of
ActiveCell.Value = Catenate(Range("A1:IU1"), " ")
I would like it to have something like this:
ActiveCell.Value = Catenate(Range("UserEntry"), " ")
So if the UserEntry is A1:G1 I would like it to pull that information. Any ideas?
Sub CatenateIt()
'replace A1:G2 below with your own range
'DEMO: concatenate A1 to G2
Call GetUserRange
ActiveCell.Value = Catenate(Range("A1:IU1"), " ")
End Sub
'<< Function to concatenate cells in a range >>
Public Function Catenate(MyRange As Range, _
Optional Delimiter As String) As String
Dim Cell As Range, N As Long
N = 1
'go thru MyRange cell by cell and concatenate
For Each Cell In MyRange
If N = MyRange.Cells.Count Then
'we don't need a delimiter after last cell
Else
'otherwise we do need a delimiter
End If
N = N + 1
Catenate = Catenate & "'" & Cell & "'" & ", "
Next Cell
Set Cell = Nothing
End Function
And also this code that is called from the above:
Sub GetUserRange()
Dim UserEntry As String
UserEntry = InputBox("Select cell column for the output - Like A1:G1.", "Select Range.")
If UserEntry <> " " Then ActiveCell.Value = UserEntry
End Sub
I would like to know how to get the ranges in the Sub CatenateIt to be the UserEntry from GetUserRange InputBox. So instead of
ActiveCell.Value = Catenate(Range("A1:IU1"), " ")
I would like it to have something like this:
ActiveCell.Value = Catenate(Range("UserEntry"), " ")
So if the UserEntry is A1:G1 I would like it to pull that information. Any ideas?