Når jeg separerer mine data, med min macro, går det galt med det sidste koma tal.

 

Resultat af kørsel:

 

Ønsket resultat:

 

Hvad går der galt ????

 

Data i Col 1:

Sub separatecsvdata()

 Dim mydata As String
'define a variable that will hold the row-index or column-index values
 y = 2
'start a loop and let it run as long as there is data in the cell A1
 Do While Cells(1, 1) <> ""
 'assign the data in cell a1 to the variable my data
 mydata = [a1]
'find out the position of the comma
 mycomma = InStr(mydata, ",")
'to ensure that the data that is left-over does not have a comma is also worked upon by the program and shifted appropriately
 If mycomma = 0 Then
' the end data, for example, 25 goes to the next empty row or column
 Cells(1, y) = [a1]
'finally empty the cell a1 so that you now know that all the comma separated data has been properly transferred to different cells
 [a1] = ""
 'now end the program
 End
 End If
'the left function extracts the number of characters from the left upto the comma and then removes the comma
 Cells(1, y) = Left(mydata, (mycomma - 1))
'the mid function extracts the characters after the comma in our case
 Cells(1, 1) = Mid(mydata, (mycomma + 1))
' go to the next row or column
 y = y + 1
 Loop
 End Sub

 

Kunne desværre ikke Uploade fil pga. sikkerhedspolitiken i firmaet :-(

 

Mvh.

Peter

 

 

Fandt selv en løsning på det + gennemløb af alle Rows.

 

Sub separatecsvdata()
 Dim k As Long
 Dim l As Long
 Dim mydata As String
 Dim mydata1 As String
 
'count number of rows
 k = Cells(Rows.Count, "A").End(xlUp).Row
 Application.ScreenUpdating = False
'run through every row with data
 For i = 1 To k
'define a variable that will hold the row-index or column-index values
 y = 2
'count number of characters in actual data set
 l = Len(Cells(i, 1))
'read data set in row
 mydata1 = Cells(i, 1)
'ad comma in the end of data set
 Cells(i, 1) = Left(mydata1, (l + 1)) & ","
'start a loop and let it run as long as there is data in the cell actual row
 Do While Cells(i, 1) <> ""
'assign the data in cell a1 to the variable my data
 mydata = Cells(i, 1)
'find out the position of the comma
 mycomma = InStr(mydata, ",")
'to ensure that the data that is left-over does not have a comma is also worked upon by the program and shifted appropriately
 If mycomma = 0 Then
' the end data, goes to the next empty column
 Cells(i, y) = Cells(i, 1)
'finally empty the column, so that you now know that all the comma separated data has been properly transferred to different cells
 Cells(i, 1) = ""
 'now goto 10
 GoTo 10
 End If
'the left function extracts the number of characters from the left upto the comma and then removes the comma
 Cells(i, y) = Left(mydata, (mycomma - 1))
'the mid function extracts the characters after the comma in our case
 Cells(i, 1) = Mid(mydata, (mycomma + 1))
'go to the next column
 y = y + 1
 Loop
10
'go to the next row
 Next
 Application.ScreenUpdating = True
 End Sub