Syntax help

[rjc4]

Hello All,

Starting at AH8 downwards, I have a list of numbers.

The size of the list varies in the number of entries.
The numbers are in numerical order although this is not important.

How can I convert the list to this syntax...... 1,2,3,4,5 (no comma after the last entry) and put the result in cell AH5.

Either a formula or code would do the job if it is feasible.

Thanks
RC

 

[Tom Urtis]

See if this helps. Stick the following code in a standard VBA module:

Public Function Numbers() As String
With Application
.Volatile
.ScreenUpdating = 0
Dim LR&, strNum$, cell As Range
LR = Cells(Rows.Count, 34).End(xlUp).Row
strNum = ""
For Each cell In Range("AH8:AH" & LR)
If Len(cell.Value) > 0 Then _
strNum = strNum & cell.Value & ","
Next cell
Numbers = Mid(strNum, 1, Len(strNum) - 1)
.ScreenUpdating = 1
End With
End Function


Then in some other cell, AH5 or wherever (but not below AH7) enter this formula exactly as you see it here:
=Numbers()


I assume you want all values concatenated, numbers or text. If you only want numbers in case some other cells contain words, please post back. The code can be improved if we know whether or not column AH contains only constants or only formulas, but this stab at it might be a start.

 

[rjc4]

Hello Tom,

That code works perfectly. The cells contain Numbers only......no formulas or text.

But when I posted I didn't appreciate the complexity of it and was going to just copy any code the forum could assist with for doing the same thing in cells AF8 down and AG8 down, with the result in AF5 and AG5. In other words I wish to get results for 3 columns AF8... AG8..... and AH8.

However, the code is beyond my skill level and if you could advise how it could be amended to work with the 3 columns, I would be most thankful.

Apologies for my omission in the original post.

Thanks and cheers,
RC

See if this helps. Stick the following code in a standard VBA module:

Public Function Numbers() As String
With Application
.Volatile
.ScreenUpdating = 0
Dim LR&, strNum$, cell As Range
LR = Cells(Rows.Count, 34).End(xlUp).Row
strNum = ""
For Each cell In Range("AH8:AH" & LR)
If Len(cell.Value) > 0 Then _
strNum = strNum & cell.Value & ","
Next cell
Numbers = Mid(strNum, 1, Len(strNum) - 1)
.ScreenUpdating = 1
End With
End Function


Then in some other cell, AH5 or wherever (but not below AH7) enter this formula exactly as you see it here:
=Numbers()


I assume you want all values concatenated, numbers or text. If you only want numbers in case some other cells contain words, please post back. The code can be improved if we know whether or not column AH contains only constants or only formulas, but this stab at it might be a start.

 

[Tom Urtis]

Instead of 3 separate UDFs then, which would be an option but not efficient, I'd opt for one sheet level event procedure. Please say how the numbers get into those columns...are they manually entered, or copied and pasted from somewhere, or imported from an external source, or obtained by a web query, or are they formulas, or gotten some other way, if so how.


If they are manually entered, then do this:

(1)
Delete the UDF formula in cell AH5.

(2)
Delete the Function code from the module you pasted it into.

(3)
From your worksheet, right click on your sheet tab, left click on View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("AF8:AH" & Cells.Rows.Count)) Is Nothing _
Or Target.Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = 0
Dim i%, LR&, strNum$, cell As Range
For i = 32 To 34
LR = Cells(Rows.Count, i).End(xlUp).Row
If LR < 8 Then
Cells(5, i).ClearContents
Else
strNum = ""
For Each cell In Range(Cells(8, i), Cells(LR, i)).SpecialCells(2)
strNum = strNum & cell.Value & ","
Next cell
Cells(5, i).Value = Mid(strNum, 1, Len(strNum) - 1)
End If
Next i
Application.ScreenUpdating = 1
End Sub

 

[rjc4]

Hi Tom,
The numbers are copied and pasted from a manually entered list of numbers. They can also be added manually to increase or decrease the entries.

Regards,
RC

Instead of 3 separate UDFs then, which would be an option but not efficient, I'd opt for one sheet level event procedure. Please say how the numbers get into those columns...are they manually entered, or copied and pasted from somewhere, or imported from an external source, or obtained by a web query, or are they formulas, or gotten some other way, if so how.

 

[Tom Urtis]

In that case, substitute the code I posted in Step #3 to this:



Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("AF8:AH" & Cells.Rows.Count)) Is Nothing Then Exit Sub
Application.ScreenUpdating = 0
Dim i%, LR&, strNum$, cell As Range
For i = 32 To 34
LR = Cells(Rows.Count, i).End(xlUp).Row
If LR < 8 Then
Cells(5, i).ClearContents
Else
strNum = ""
For Each cell In Range(Cells(8, i), Cells(LR, i)).SpecialCells(2)
strNum = strNum & cell.Value & ","
Next cell
Cells(5, i).Value = Mid(strNum, 1, Len(strNum) - 1)
End If
Next i
Application.ScreenUpdating = 1
End Sub


Again, it goes into the sheet module, so follow those steps in #3 for that, and be sure to delete all the other code I posted that you may have copied.

 

[Norie]

deleted

 

[rjc4]

Hi Norie,
Thanks for your reply. I can't get the code to work mainly because I don't understand how to insert it. Where do I put the code? And do I enter in AF5,AG5 and AH5 the formula =MyConCat(COLUMN()). Do I just create a macro and insert the code. Sorry for the dumb questions but this is way above me at the moment. I can get Tom's code to work but with a minor problem which I'm responding to now.

Kind regards,
RC

I don't know if it helps but this seemed to work for me.

Mind you it does rely on the data starting in row 8 and using the COLUMN worksheet function.

=MyConCat(COLUMN())

Function MyConCat(col As Long) As String
Dim LastRow As Long
Dim arr

    LastRow = Cells(Rows.Count, col).End(xlUp).Row
    
    arr = Cells(8, col).Resize(LastRow - 7, 1)
    
    MyConCat = Join(Application.WorksheetFunction.Transpose(arr), ",")
    
End Function

This will allow you so set the start row, which currently defaults to 8 as was asked for in the original post.

Function MyConCat(col As Long, Optional StartRow = 8) As String
Dim LastRow As Long
Dim arr

    LastRow = Cells(Rows.Count, col).End(xlUp).Row
    
    arr = Cells(StartRow, col).Resize(LastRow - (StartRow - 1), 1)
    
    MyConCat = Join(Application.WorksheetFunction.Transpose(arr), ",")
    
End Function

 

[rjc4]

Hi Tom,
Thanks for the code. It works fine except there are trailing commas at the end of each cell. Like 1,2,,,,,,,, 1,2,3,4,,,,,,,,,,,, etc.

It needs to be 1,2 and 1,2,3,4 etc

Any clues to eliminate these, otherwise ok.

Also while thinking about it, I've used your original code slightly amended to run 3 times from a macro and this works also. The only down side is that it is not automatic but needs to be run from a macro button, but it does the job. Here it is, slightly modified from your original. If you can see any need to amend it please mail back. I'll wait to see though whether the trailing commas can be eliminated to decide which is the best option to use. I like all the input you guys put forward but at the end of the day, I try and use code I can understand.

This is what I have working now.
Sub ConvertAF()
With Application
.Volatile
.ScreenUpdating = 0
Dim LR&, strNum$, cell As Range
LR = Cells(Rows.Count, 32).End(xlUp).Row
strNum = ""
For Each cell In Range("Af8:Af" & LR)
If Len(cell.Value) > 0 Then _
strNum = strNum & cell.Value & ","
Next cell
Range("Af5").Value = Mid(strNum, 1, Len(strNum) - 1)
.ScreenUpdating = 1
End With

Call ConvertAG
Call ConvertAH

End Sub

Sub ConvertAG()
With Application
.Volatile
.ScreenUpdating = 0
Dim LR&, strNum$, cell As Range
LR = Cells(Rows.Count, 33).End(xlUp).Row
strNum = ""
For Each cell In Range("AG8:AG" & LR)
If Len(cell.Value) > 0 Then _
strNum = strNum & cell.Value & ","
Next cell
Range("AG5").Value = Mid(strNum, 1, Len(strNum) - 1)
.ScreenUpdating = 1
End With
End Sub

Sub ConvertAH()
With Application
.Volatile
.ScreenUpdating = 0
Dim LR&, strNum$, cell As Range
LR = Cells(Rows.Count, 34).End(xlUp).Row
strNum = ""
For Each cell In Range("AH8:AH" & LR)
If Len(cell.Value) > 0 Then _
strNum = strNum & cell.Value & ","
Next cell
Range("AH5").Value = Mid(strNum, 1, Len(strNum) - 1)
.ScreenUpdating = 1
End With
End Sub


Cheers for now and thanks Tom
RC

In that case, substitute the code I posted in Step #3 to this:



Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("AF8:AH" & Cells.Rows.Count)) Is Nothing Then Exit Sub
Application.ScreenUpdating = 0
Dim i%, LR&, strNum$, cell As Range
For i = 32 To 34
LR = Cells(Rows.Count, i).End(xlUp).Row
If LR < 8 Then
Cells(5, i).ClearContents
Else
strNum = ""
For Each cell In Range(Cells(8, i), Cells(LR, i)).SpecialCells(2)
strNum = strNum & cell.Value & ","
Next cell
Cells(5, i).Value = Mid(strNum, 1, Len(strNum) - 1)
End If
Next i
Application.ScreenUpdating = 1
End Sub


Again, it goes into the sheet module, so follow those steps in #3 for that, and be sure to delete all the other code I posted that you may have copied.

 

[Tom Urtis]

Hi Tom,
Thanks for the code. It works fine except there are trailing commas at the end of each cell. Like 1,2,,,,,,,, 1,2,3,4,,,,,,,,,,,, etc.

It needs to be 1,2 and 1,2,3,4 etc

Any clues to eliminate these, otherwise ok.

Extra comments do not get produced in my code but when I tested Norie's code there were extra commas in AF5 AG5 and AH5 etc when empty cells are involved in the columns, though with his they were preceding commas, not trailing commas as you describe. Maybe you are seeing what you are seeing because you are attempting 2 solutions in the same workbook at the same time with data in the columns that is not contiguous. Try using the code I posted, just as I said to install it, and see if that fixes that issue.

Also, my code was not a macro and does not need any button, just stick it in the worksheet module and it will automatically be triggerred whenever cells are changed in AF:AH below row 7.

Otherwise I cannot duplicate the behavior you are seeing, at least not just when my code was used as directed...I get the exact results you wrote that you say it needs to be, as quoted above.

If you need a sample workbook let me know, though you can produce one also by placing the most recent code I posted into the sheet module of a fresh new workbook, and testing that.