Alter VBA for Cell Addressing in Excel 2013

[kyleno]

I found a VBA script to convert a column of text to a single cell separated by spaces (see below), BUT it will only select a list of data from column A and only return the data to column B.

I was able to figure out that altering the numbers in rows 7 and 8 (dataRow =# and listRow =#) will change the row numbers.

I would like to be able to specify a cell number for both the row/column that the list is located as well as the output cell for the new single cell list.

Any ideas?

Please and thank you.

~ Ky
----------------------------------
Sub generatecsv()

Dim dataRow As Integer
Dim listRow As Integer
Dim data As String

dataRow = 1: Rem the row that it is being read from column A otherwise known as 1 in vb script
listRow = 1: Rem the row in column B that is getting written

Do Until Cells(dataRow, 1).Value = "" And Cells(dataRow + 1, 1).Value = ""
If (data = "") Then
data = Cells(dataRow, 1).Value
Else
If Cells(dataRow, 1).Value <> "" Then
data = data & " " & " " & Cells(dataRow, 1).Value
Else
Cells(listRow, 2).Value = data
data = ""
listRow = listRow + 1
End If
End If
dataRow = dataRow + 1
Loop

Cells(listRow, 2).Value = data

End Sub

 

[kyleno]

Works great! Thanks so much!

Sure, but that takes a little more code to do...

Function Combine(Rng As Range) As String
  Dim vNum As Variant
  Combine = Join(Application.Transpose(Rng), ",")
  For Each vNum In Array(9841, 121, 13, 5, 3, 3, 2)
    Combine = Replace(Combine, String(vNum, ","), ",")
  Next
  If Right(Combine, 1) = "," Then Combine = Left(Combine, Len(Combine) - 1)
End Function

Note: I could do this as a one-liner, but the above is more efficient.

 

[kyleno]

Small problem. I tried the function as is and it worked fine, but then I tried renaming the function and the cell contents do not appear. I am doing two versions of the code (one comma separated and one space separated).

I changed it to the following and had issues:

Function Combine3(Rng As Range) As String
  Dim vNum As Variant
  Combine = Join(Application.Transpose(Rng), ",")
  For Each vNum In Array(9841, 121, 13, 5, 3, 3, 2)
    Combine = Replace(Combine, String(vNum, ","), ",")
  Next
  If Right(Combine, 1) = "," Then Combine = Left(Combine, Len(Combine) - 1)
End Function

Any thoughts?

Sure, but that takes a little more code to do...

Function Combine(Rng As Range) As String
  Dim vNum As Variant
  Combine = Join(Application.Transpose(Rng), ",")
  For Each vNum In Array(9841, 121, 13, 5, 3, 3, 2)
    Combine = Replace(Combine, String(vNum, ","), ",")
  Next
  If Right(Combine, 1) = "," Then Combine = Left(Combine, Len(Combine) - 1)
End Function

Note: I could do this as a one-liner, but the above is more efficient.

 

[Rick Rothstein]

Small problem. I tried the function as is and it worked fine, but then I tried renaming the function and the cell contents do not appear. I am doing two versions of the code (one comma separated and one space separated).

I changed it to the following and had issues:

Function Combine3(Rng As Range) As String
  Dim vNum As Variant
  Combine = Join(Application.Transpose(Rng), ",")
  For Each vNum In Array(9841, 121, 13, 5, 3, 3, 2)
    Combine = Replace(Combine, String(vNum, ","), ",")
  Next
  If Right(Combine, 1) = "," Then Combine = Left(Combine, Len(Combine) - 1)
End Function

Unlike a macro, you have to change the function name and that name's appearance within the code as well. Here is the revised function with the name change you have indicated (I have highlighted in red each change that was necessary...

Function [B][COLOR="#FF0000"]Combine3[/COLOR][/B](Rng As Range) As String
  Dim vNum As Variant
  [B][COLOR="#FF0000"]Combine3[/COLOR][/B] = Join(Application.Transpose(Rng), ",")
  For Each vNum In Array(9841, 121, 13, 5, 3, 3, 2)
    [B][COLOR="#FF0000"]Combine3[/COLOR][/B] = Replace([B][COLOR="#FF0000"]Combine3[/COLOR][/B], String(vNum, ","), ",")
  Next
  If Right([B][COLOR="#FF0000"]Combine3[/COLOR][/B], 1) = "," Then [B][COLOR="#FF0000"]Combine3[/COLOR][/B] = Left([B][COLOR="#FF0000"]Combine3[/COLOR][/B], Len([B][COLOR="#FF0000"]Combine3[/COLOR][/B]) - 1)
End Function

 

[kyleno]

That worked. Thanks again.

 

[kyleno]

I still seem to be having difficulty with this. I have altered the code slightly to be renamed CombineCSV and made it ", " instead of just ",", but I didn't think that would matter much.

I have been using a version of this to just do space separated values and am just now starting to work with the comma separated version.

So this is what I have currently

Function CombineCSV(Rng As Range) As String
  Dim vNum As Variant
  CombineCSV = Join(Application.Transpose(Rng), ", ")
  For Each vNum In Array(9841, 121, 13, 5, 3, 3, 2)
    CombineCSV = Replace(CombineCSV, String(vNum, ", "), ", ")
  Next
  If Right(CombineCSV, 1) = ", " Then CombineCSV = Left(CombineCSV, Len(CombineCSV) - 1)
End Function

I have a list of numbers (spaces are intentional):
24
29
28
23
22

23
17
25

20



26

When I run the function I get the following result:
24, 29, 28, 23, 22, , 23, 17, 25, , 20, , , , 26

I was wondering if there was a way to alter the code so that it would appear as such:
24, 29, 28, 23, 22, 23, 17, 25, 20, 26

Thanks!

Sure, but that takes a little more code to do...

Function Combine(Rng As Range) As String
  Dim vNum As Variant
  Combine = Join(Application.Transpose(Rng), ",")
  For Each vNum In Array(9841, 121, 13, 5, 3, 3, 2)
    Combine = Replace(Combine, String(vNum, ","), ",")
  Next
  If Right(Combine, 1) = "," Then Combine = Left(Combine, Len(Combine) - 1)
End Function

Note: I could do this as a one-liner, but the above is more efficient.

 

[Rick Rothstein]

I still seem to be having difficulty with this. I have altered the code slightly to be renamed CombineCSV and made it ", " instead of just ",", but I didn't think that would matter much.

I have been using a version of this to just do space separated values and am just now starting to work with the comma separated version.

So this is what I have currently

[table="width: 500"]
[tr]
	[td]Function CombineCSV(Rng As Range) As String
  Dim vNum As Variant
  CombineCSV = Join(Application.Transpose(Rng), ", ")
  For Each vNum In Array(9841, 121, 13, 5, 3, 3, 2)
    CombineCSV = Replace(CombineCSV, [B][COLOR="#FF0000"]String[/COLOR][/B](vNum, ", "), ", ")
  Next
  If Right(CombineCSV, 1) = ", " Then CombineCSV = Left(CombineCSV, Len(CombineCSV) - 1)
End Function[/td]
[/tr]
[/table]

The problem is the VBA String function (highlighted in red above) can only repeat as single character, so the code worked for a single comma but cannot work (as written) for the two-character comma/space. If we replace the Sting function with a call to Excel's REPT function (requires swapping the arguments), that the code will work fine...

[table="width: 500"]
[tr]
	[td]Function CombineCSV(Rng As Range) As String
  Dim vNum As Variant
  CombineCSV = Join(Application.Transpose(Rng), ", ")
  For Each vNum In Array(9841, 121, 13, 5, 3, 3, 2)
    CombineCSV = Replace(CombineCSV, Application.Rept(", ", vNum), ", ")
  Next
  If Right(CombineCSV, 1) = ", " Then CombineCSV = Left(CombineCSV, Len(CombineCSV) - 1)
End Function[/td]
[/tr]
[/table]

 

[kyleno]

I tried that and it works. Thanks!

 

[kyleno]

I was wondering if I could get some help adjusting this UDF. I now need it to generate a defined range of vertical cells. My purpose is to compile a list of search terms to copy into a SQL statement, so that each term is on its on separate line so that it looks clean.


Function CombineSSV(Rng As Range) As String
Dim vNum As Variant
CombineSSV = Join(Application.Transpose(Rng), " ")
For Each vNum In Array(9841, 121, 13, 5, 3, 3, 2)
CombineSSV = Replace(CombineSSV, String(vNum, " "), " ")
Next
If Right(CombineSSV, 1) = " " Then CombineSSV = Left(CombineSSV, Len(CombineSSV) - 1)
End Function


Thanks for the help!

 

[Scott Huish]

Rick, what is the purpose of that array and how did you come up with those values?

 

[Rick Rothstein]

Rick, what is the purpose of that array and how did you come up with those values?

Originally, it was used in the old compiled days of VB to reduce text with multiple consecutive spaces down to single space (in essence, what the worksheet function TRIM does in Excel). In this thread, it was modified to collapse multiple consecutive commas down to single commas. Here is a copy of the old newsgroup message thread where those numbers were first derived (you will have to read through 4 or 5 or so introductory messages until you get to the ones discussing the values to use in the array...

https://groups.google.com/forum/#!topic/microsoft.public.vb.general.discussion/TqZHK9cPnpU