Number Add To Comma Separated Data In Cell

  • Cell(i,1)have 3 Numbers
    Each Number Not Allowed Greater Than 10
    Each Number In Cell(i,1) Will Be Added 1 In Cell(i,3) And Cell(i+1,3)....
    How Can I Seperate Numbers And Make Three Variables To Run Macro
    A
    1,3,10
    2,5,9
    C
    2,3,10
    1,4,10
    3,5,9
    2,6,9
    2,5,10
    Thanks In Previous

  • Re: Number Add To Comma Seperated Data In Cell


    Does this macro do what you are asking?
    [vba]Sub DoNumbers()
    Dim i As Long, j As Long, k As Long, s As String, n(1 To 3) As Integer
    i = 1 'first row of numbers
    k = i 'to mark row in column C to put next set
    While Len(Cells(i, 1)) > 0
    s = Cells(i, 1)
    j = InStr(s, ",")
    If j = 0 Then
    MsgBox "Row " & i & " value of " & Cells(i, 1).Value & " not in expected form."
    Exit Sub
    End If
    n(1) = CInt(Trim(Left(s, j - 1)))
    s = Mid(s, j + 1)
    j = InStr(s, ",")
    If j = 0 Then
    MsgBox "Row " & i & " value of " & Cells(i, 1).Value & " not in expected form."
    Exit Sub
    End If
    n(2) = CInt(Trim(Left(s, j - 1)))
    n(3) = CInt(Trim(Mid(s, j + 1)))
    For j = 1 To 3
    If n(j) < 10 Then
    Cells(k, 3) = n(1) + IIf(j = 1, 1, 0) & "," & n(2) + IIf(j = 2, 1, 0) & "," & n(3) + IIf(j = 3, 1, 0)
    k = k + 1
    End If
    Next j
    i = i + 1
    Wend
    End Sub[/vba]

  • Re: Number Add To Comma Separated Data In Cell


    Owe To Your Help I Find That (0,0,0) To (10,10,10) With 30 Steps Have 5550996791340 Paths. Thanks Very Much.

  • Re: Number Add To Comma Separated Data In Cell


    Glad to have helped. I would have thought the answer was only 6^10 or 66,466,176 paths, but seeing your larger number and thinking some more, I see where my logic was incorrect.

  • Re: Number Add To Comma Separated Data In Cell


    I've been pondering this problem and finally saw it the correct way and agree with your answer. Here is my reasoning, which may be different from yours.
    There are 30 "moves" to be made to get from (0,0,0) to (10,10,10). Each starting value will change 10 times, so there are 30C10, or in Excel function talk Combin(30,10), possible locations in the 30 moves for the first starting value to change. Of the 20 move positions remaining, the second starting value has 20C10, or Combin(20,10), possible locations. The third starting number takes whatever is left. Multiplying everything together, and trusting Excel's calculations, we get the total number of possible "paths" is 30,045,015 * 184,756 = 5,550,996,791,340.


    Thanks for an interesting problem

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!