Results 1 to 7 of 7

Thread: VBA Run-time error '1004'

  1. #1
    New Member
    Join Date
    Sep 2015
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Run-time error '1004'

    Hello everyone.

    I'm trying to finish the most complex macro I've ever done and have run into an error that is blowing my mind. I'm learning VBA so I would like to understand the error and not just get a fix, if that makes sense, so I don't make it again.

    I get an the '1004' error on my Boo variable and I don't know why.

    Code:
    Sub RunScores
    
    Dim Boo As String
    Set XYZWorkbook = ActiveWorkbook
    Boo = "A8"
    'Goes through each subsegment
    'If it is blank, it stops
    
    
    Do While IsEmpty(Range(Boo).Value) = False
        Range(Boo).UnMerge
        Call FillInScores
        Range(Boo).Select
        Boo = Range(Boo).Offset(3, 0)
    Loop
    
    End Sub
    Previously I wasn't using a loop and was renaming the Boo variable manually as well as well as repeating the code and it ran fine. But for what i am using it for, there could be 2 subsegments, or 100 so I really need to use a loop.

    Thank you for any help.
    Last edited by RoryA; Aug 5th, 2019 at 09:45 AM. Reason: Fix code tags

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: VBA Run-time error '1004'

    Try making this change
    Code:
    Boo = Range(Boo).Offset(3, 0).Address
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,671
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA Run-time error '1004'

    This line will just give you the value of what is in A11:

    Code:
    boo = Range(boo).Offset(3, 0)
    so maybe use .Address

    Code:
    boo = Range(boo).Offset(3, 0).Address
    Looking for opportunities

  4. #4
    New Member
    Join Date
    Sep 2015
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Run-time error '1004'

    For this, the error it gives is on the first "Do While IsEmpty(Range(Boo).Value) = False" As it shows there is no value in the Boo variable. Why would this be if I have declared it and given it a string input? The Offset line would be once it has completed the first run and checks for the next.

  5. #5
    New Member
    Join Date
    Sep 2015
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Run-time error '1004'

    Never mind! I see it's giving the error after on second loop.

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: VBA Run-time error '1004'

    I suspect that A11 is a blank cell
    - 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
    New Member
    Join Date
    Sep 2015
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Run-time error '1004'

    Yes and no. It's a weird workbook, the sort you really wish you could change to make more logical. Cells (A8:A10) are merged together. So I found that instead of (3,0) offset I really need to use (1,0) offset as (A11:A13) would have a value.

    Overall I think I understand the error I was making. I wasn't fully telling it what cell to go to next to rename that variable.

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
  •