CODEWITHSUNDEEP
vbaexcel
2095377
2095377

Reputation: 47

VBA code won't work if at least one of the cells is not below 2 characters

I have this code,

I use it to transpose a list of numbers and then separate them with a comma, It works well for every list, if numbers are between 0 and 99.

However if the list of numbers contains numbers with more than 2 characters, then, the code will give the attended result, only if at least one number in the list has 2 characters or less.

If I have a list of numbers with > 2 characters, then it will give me the transposed number without the comma separator.

Could you please advise

Option Explicit
Sub colonne_a_ligne()

'On compte le nombre de Trades
Dim Number_of_Trade As Integer
Number_of_Trade = Worksheets("Feuil1").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
Dim m As Integer
m = Number_of_Trade + 1

'On transpose les trades en ligne
  Dim sourceRange As Range
  Dim targetRange As Range


  Set sourceRange = ActiveSheet.Range(Cells(1, 1), Cells(Number_of_Trade, 1))
  Set targetRange = ActiveSheet.Cells(2, 2)

  sourceRange.Copy
  targetRange.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

'On met en forme dans le format 
   Dim sisk As String
    Dim row As Long
    Dim col As Long

    For row = 2 To 2
        sisk = vbNullString
        For col = 2 To m
            If VBA.Len(sisk) Then sisk = sisk & ","
            sisk = sisk & Cells(row, col)
        Next col

        Worksheets("Feuil1").Cells(3, 2) = sisk

    Next row

End Sub

Case 1

Case 2

Case 3

Upvotes: 2

Views: 187

Answers (2)

DisplayName
DisplayName

Reputation: 13386

Based on data you shown (not tested)

Sub colonne_a_ligne()
    With Worksheets("Feuil1")
        .Range("A2").Value = Join(Application.Transpose(.Range("A1", .Cells(.Rows.Count, 1).End(xlUp)).Value), ",")
    End With
End Sub

Upvotes: 2

user4039065
user4039065

Reputation:

This is a number formatting issue; specifically with the thousands separator. The second image is showing a true number with a space as the thousands separator but rounded off to 15 digit precision.

This should resolve the attempted auto-conversion to a number.

Dim sisk As String
Dim row As Long
Dim col As Long

For row = 2 To 2
    sisk = vbNullString
    For col = 2 To m
        sisk = sisk & "," & Cells(row, col).TEXT
    Next col

    Worksheets("Feuil1").Cells(3, 2).NUMBERFORMAT = "@"
    Worksheets("Feuil1").Cells(3, 2) = MID(sisk, 2)

Next row

Upvotes: 1

Related Questions