Applying a Macro to a range of cells

JACOBB9900

New Member
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
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
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
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
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
It's not something I've ever used, so had to check what it was & what it did.
 

Joe4

MrExcel MVP, Junior Admin
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
Totally agree, it may also be there are only ever 2 colons, so text to columns would then work.
 

JACOBB9900

New Member
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.

 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top