Results 1 to 10 of 10

Thread: Applying a Macro to a range of cells
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2019
    Location
    Texas
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Applying a Macro to a range of cells

    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

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,743
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Applying a Macro to a range of cells

    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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,460
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Applying a Macro to a range of cells

    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.
    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
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,123
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Applying a Macro to a range of cells

    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.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,460
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Applying a Macro to a range of cells

    Quote Originally Posted by Fluff View Post
    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.
    Last edited by JoeMo; Sep 17th, 2019 at 02:11 PM.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,123
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Applying a Macro to a range of cells

    It's not something I've ever used, so had to check what it was & what it did.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,743
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Applying a Macro to a range of cells

    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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,123
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Applying a Macro to a range of cells

    Totally agree, it may also be there are only ever 2 colons, so text to columns would then work.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    New Member
    Join Date
    Apr 2019
    Location
    Texas
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Applying a Macro to a range of cells

    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.


  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,123
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Applying a Macro to a range of cells

    As long as your happy, that's fine

    For future info, there are some add-ins available here https://www.mrexcel.com/forum/about-...tachments.html that enable you to include sample data in your posts.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •