Applying a Macro to a range of cells

JACOBB9900

New Member
Joined
Apr 8, 2019
Messages
38
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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.
 
Upvote 0
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.
Rich (BB code):
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
 
Upvote 0
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.
 
Upvote 0
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. :rolleyes:
EDIT: Of course, with Text to Columns you could use the Do not Import button to skip anything after the 2nd colon.
 
Last edited:
Upvote 0
It's not something I've ever used, so had to check what it was & what it did.
 
Upvote 0
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.
 
Upvote 0
Totally agree, it may also be there are only ever 2 colons, so text to columns would then work.
 
Upvote 0
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.

 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top