Can you spot the errors?

[joharmenezes]

I have the following VBA code:

Sub NumberSplit()
For Each Cell In Range("a1:a22")
Dim count As Integer
count = 0
AreThereSpaces = 0
col = 0
CellValue = CStr(Cell.Value)
On Error Resume Next
For i = (count + 1) To Len(CellValue)

AreThereSpaces = WorksheetFunction.Find(" ", CellValue, i)
If AreThereSpaces > 0 Then
LotteryNum = Mid(CellValue, i, AreThereSpaces - 1)
col = col + 1
PrintOnTheScreen LotteryNum, Cell.Row, Cell.Column + col
count = count + AreThereSpaces
End If
Next i
Next Cell
End Sub



Sub PrintOnTheScreen(NumToPrint, RowNum, ColNum)
Range(Cells(RowNum, ColNum), Cells(RowNum, ColNum)).Value = NumToPrint
End Sub


I am trying to split characters in a string whenever a space is encountered and paste the splits in adjacent rows

For example:

If cell A1 has "ab cd ef"
then the cells should be , B1="ab", C1="cd", D1="ef"



Thanks

 

[SirJB7]

Hi, joharmenezes!

First of all, a good practice is to use Option Explicit which will force you to declare all the used variables. You can place it manually or set VBA to use it by default (VBA editor, Tools, Options, 1st tab Editor, 2nd entry Require Variable Declaration).

Second, structure a bit your code. Move the Dim statement to the beginning of the procedure, removing it from the For...Next loop. Writing comments to separate main sections improves readability too.

Third, are you sure that you're not mixing 2 things? I don't get printed any lottery numbers but a bunch of zeroes, and I don't get "ab cd ef" split neither.

Give a look at the uploaded file.

Regards!

 

[joharmenezes]

Hi SirJB7,

Thanks for structuring my code. It looks so neat and tidy. I think u were getting zeroes because u declared LotteryNum as an Integer. but it has still not resolved my problem.

I would like the VBA code to split strings in cell A1 based on the spaces in between. for example if Cell A1 contains the string "ab cd ef" then the cells B1, C1, D1 should be filled with ab, cd, ef respectively.

 

[SirJB7]

Hi, joharmenezes!

I changed it to String and retrieve letters, lots but not how you want them. So I cut to the chase. Check the new uploaded file. This is the code:

Option Explicit

Sub NumberSplit()
    ' constants
    Const ksWS = "Hoja1"
    Const ksRng = "A1:A22"
    ' declarations
    Dim rng As Range
    Dim I As Long, J As Integer, K As Integer, L As Integer, A As String
    ' start
    Set rng = Worksheets(ksWS).Range(ksRng)
    With rng.Offset(0, 1)
        Range(.Columns(1), .Columns(.Parent.Columns.count - 1)).ClearContents
    End With
    ' process
    With rng
        For I = 1 To .Rows.count
            A = .Cells(I, 1).Value & " "
            If A = "" Then Exit For
            J = 0
            L = 1
            Do
                K = InStr(J + 1, A, " ")
                L = L + 1
                .Cells(I, L).Value = Mid(A, J + 1, K - J - 1)
                J = K
            Loop Until J = Len(A)
        Next I
    End With
    ' end
    Set rng = Nothing
End Sub

Sub PrintOnTheScreen(NumToPrint, RowNum, ColNum)
    Range(Cells(RowNum, ColNum), Cells(RowNum, ColNum)).Value = NumToPrint
End Sub

NumberSplit procedure has been slightly and subtly tweaked and the other isn't used.

Regards!

 

[Debraj]

Can you please try the below for me..

Sub BetterToRecodeThanSpotting()
deb = Range("A1").CurrentRegion
For I = 1 To UBound(deb)
    raj = Split(Cells(I, 1), " ")
    Range(Cells(I, 2), Cells(I, UBound(raj) + 2)) = raj
Next I
End Sub

 

[SirJB7]

@Debraj(ex-Roy)
Hi, buddy!
I did it, and first it didn't even compile as your so minimalistic style forbids you to use Option Explicit.
Then, after commenting that excess of luxury statement, it run smooth and neat.
Regards!

 

[joharmenezes]

Thanks DEb that was fantastic. But SirB7 I have a question. Can u please explain this line in your code:

.Columns(.Parent.Columns.count - 1)).ClearContents

I see that it returns the XFD1:XFD22 range. How does it get this range? And what does the parent property do?


One more question to Deb. How can I type cast " raj = Split(Cells(I, 1), " ") " to a string. I know raj is an array so how do I change it?


Thanks

 

[SirJB7]

Hi, joharmenezes!

It clears the columns to the right of the input range in case there were previous data that doesn't correspond to the actual input data.

In fact the thing starts here:
rng.Offset(0, 1)
this selects the 1st column next to the input range.

Then this:
Range(.Columns(1), .Columns(.Parent.Columns.count - 1)).ClearContents
takes a range from the 1st column of the previously defined range, i.e., column 1, to the last column available... where? At the previously-defined-range's parent, i.e., the worksheet, so it ends up at column XFD.

Regards!