Applying a Macro to a range of cells

[JACOBB9900]

Hello,

I need to apply my split macro to range of cells. I currently have cell D4 selected but I need the range D4:D100 to be affected by the macro. How do I type that range out? I've tried typing MyText=Range(Cells(3,4),Cells(100,4)) but it did not work. See below for my current macro.

Thank you.

Sub Split_Example1()

Dim MyText As String
Dim i As Integer
Dim MyResult() As String

MyText = Cells(3, 4)
MyResult = Split(MyText, ":", 3)

For i = 0 To UBound(MyResult)
Cells(3, i + 4).Value = MyResult(i)

Next i

End Sub

 

[Joe4]

Can you explain what your data looks like and what your Macro is supposed to be doing?
Note that if you are just trying to split up data into multiple columns, using ":" as a delimter, you can just use Text to Columns to do this (no VBA necessary).
Even if you wanted to use VBA to do it, you could avoid loops by using Text to Columns in your code.

 

[JoeMo]

I agree with Joe4, Text to Columns is the most efficient way to go. If you want to see a macro, the one below loops through each cell in your range. Note that I changed the number in bold red from 4 to 5 so the results don't overwrite the string you are splitting.

Sub Split_Example1()
Dim c As Range
Dim i As Long
Dim MyResult As Variant

For Each c In Range("D3:D100")
    MyResult = Split(c.Value, ":", 3)
    For i = 0 To UBound(MyResult)
        Cells(c.Row, i + 5).Value = MyResult(i)
    Next i
Next c
End Sub

 

[Fluff]

The problem with Text to Columns is that it will split the data on every ":" whereas the op is limiting it to 2 ":", so anything after the 2nd colon will not get split.

 

[JoeMo]

The problem with Text to Columns is that it will split the data on every ":" whereas the op is limiting it to 2 ":", so anything after the 2nd colon will not get split.

Good point Fluff, I missed the optional Limit argument the OP used.
EDIT: Of course, with Text to Columns you could use the Do not Import button to skip anything after the 2nd colon.

 

[Fluff]

It's not something I've ever used, so had to check what it was & what it did.

 

[Joe4]

It's not something I've ever used, so had to check what it was & what it did.

I haven't used that either.

But I asked for some sample data and a description of what they want to do. I learned a long time to not always trust that the posted code does exactly what they want. I often come across instances in which people copied the code from somewhere else, and sometimes inherit "unintended" parts of it (because they do not know what it does or how to change it). So I don't like to assume anything.

 

[Fluff]

Totally agree, it may also be there are only ever 2 colons, so text to columns would then work.

 

[JACOBB9900]

Thank you everyone for your input. The edit done by JoeMowas the exact fix I was looking for. It is true, this a macro I copied andtweaked to fit my needs. I am very new to VBA and am attempting to teach myselfthrough forums like this, Face Book groups, and Google searches. Although verytedious, it has been enjoyable to learn. I am familiar with Text to Columns,but was unfamiliar with the Do not import option. This is actually the secondpart to a macro that extracts emails from outlook. I am using the Splitfunction to pull the specific text from the body of the email. I am unable toupload samples through this computer due to the fact that it’s a companycomputer and we do not have access to Drop Box.

 

[Fluff]

As long as your happy, that's fine

For future info, there are some add-ins available here https://www.mrexcel.com/forum/about-board/508133-attachments.html that enable you to include sample data in your posts.