Applying a Macro to a range of cells

JACOBB9900

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,177
Office Version
365
Platform
Windows
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

MrExcel MVP
Joined
May 26, 2009
Messages
16,645
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,562
Office Version
365
Platform
Windows
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

MrExcel MVP
Joined
May 26, 2009
Messages
16,645
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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,562
Office Version
365
Platform
Windows
It's not something I've ever used, so had to check what it was & what it did.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,177
Office Version
365
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,562
Office Version
365
Platform
Windows
Totally agree, it may also be there are only ever 2 colons, so text to columns would then work.
 

JACOBB9900

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

 

Forum statistics

Threads
1,078,494
Messages
5,340,691
Members
399,390
Latest member
newexcel12

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top