Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: Code works when 2 cells are populated, but doesn't work when they are empty. I get Run-Time Error 1004

  1. #1
    Board Regular
    Join Date
    Jul 2009
    Posts
    926
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question Code works when 2 cells are populated, but doesn't work when they are empty. I get Run-Time Error 1004

    Hello All,

    The code below is for 12 cells only (all other cells in row 6 are locked), one for each month in row 6.

    The "GETPIVOTDATA" code will populate the each month below.
    Jan data will go in the C6 cell
    Feb data will go in the D6 cell
    Mar data will go in the E6 cell, etc....

    The issue is that when there is no data in January (C6) or February (D6), the code below gives me an error.

    The error I get is "Run-Time Error 1004".

    It is also worthy to note that in row 6, all cells are locked from editing except cells C6 through N6.

    Again, the code works perfectly if there is already data in BOTH the C6 AND D6 cells. I would like this to work even if one or both of them are blank.

    Can anyone make this happen?

    Thanks much!


    Code:
     Range("C6").Select
    ActiveCell.End(xlToRight).Offset(0, 1).Select
        ActiveCell.FormulaR1C1 = _
            "=GETPIVOTDATA(""REPORT"",'Customer Chart'!R1C1,""NAME"",""John"")"
    Last edited by VBAProIWish; Mar 3rd, 2017 at 10:08 AM.
    I can't wait to change my name to
    "VBAPROIAM"


    Using Excel 2010

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    47,890
    Post Thanks / Like
    Mentioned
    40 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Code works when 2 cells are populated, but doesn't work when they are empty. I get Run-Time Error 1004

    First off, most VBA code where one row ends with Select and the next begins with Selection or ActiveCell can be combined into one, i.e.
    Code:
        Range("C6").Select
        ActiveCell.End(xlToRight).Offset(0, 1).Select
        ActiveCell.FormulaR1C1 = _
            "=GETPIVOTDATA(""REPORT"",'Customer Chart'!R1C1,""NAME"",""John"")"
    can be simplified to this:
    Code:
     Range("C6").End(xlToRight).Offset(0, 1).FormulaR1C1 = _
            "=GETPIVOTDATA(""REPORT"",'Customer Chart'!R1C1,""NAME"",""John"")"
    It is important to understand what this code is doing:
    Code:
     Range("C6").Select
    ActiveCell.End(xlToRight).Offset(0, 1).Select
    This is saying, starting in cell C6, move to the right and select the next cell that exists BEFORE the first blank cell in that row.
    So if in row 6, only cells C6, D6, E6, and F6 were populated, it would move to cell F6 (the cell before the first blank cell when moving to the right in that row).
    However, if all the cells after C6 in that row are blank, it will move to the last possible cell in that row (XFD6 in Excel 2007 and later).
    There are no more columns to the right of that. So, then the .Offset(0,1) would error out, because if you are in the last possible column in a row, you cannot move anymore to the right.
    I suspect that is what is happening.

    I would recommend doing a check to make sure that column C is not the last populated cell in that row before proceeding with that step, i.e.
    Code:
    If Cells(6,Columns.Count).End(xlToLeft).Column > 3 Then
        Range("C6").End(xlToRight).Offset(0, 1).FormulaR1C1 = _
            "=GETPIVOTDATA(""REPORT"",'Customer Chart'!R1C1,""NAME"",""John"")"
    End If
    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
    Board Regular
    Join Date
    Nov 2010
    Location
    Lafayette, Indiana
    Posts
    541
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code works when 2 cells are populated, but doesn't work when they are empty. I get Run-Time Error 1004

    Set your macro to record and edit the formula you have and insert iferror( at the beginning of the formula, but after the Equal Sign. Then at the end, type a comma and then 2 double-quotation marks and a right parenthesis. Once you save that formula, then stop recording and copy the macro activecell.formular1c1 info into your existing macro. Now it returns nothing if there is an error.
    Mike Hirsch
    Using Excel 2016

  4. #4
    Board Regular
    Join Date
    Jul 2009
    Posts
    926
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code works when 2 cells are populated, but doesn't work when they are empty. I get Run-Time Error 1004

    Joe4,
    Thanks very much for that detailed explanation!
    So, in row 6....ALL of my cells are locked in row 6 except C6 through N6.
    If I'm in the N6 cell and I hit the right arrow (or the tab button), then C14 will be the next cell that will be selected because I have 4 people that I'm tracking.
    So, If I'm running a report that needs February data (which is cell D6), then D6 is the first blank cell and the cell that I want to populate the data in.

    If I'm running a report for January, C6 will be the first blank cell in the C6 to N6 range, so I would want the formula to populate the C6 cell.
    If I'm running a report for February, D6 will be the first blank cell in the C6 to N6 range, so I would want the formula to populate the D6 cell.

    I haven't run your new code yet, but I assume that your code will work with all that I have just said?



    MikeDBMan,
    Also...thanks for your explanation. Would you mind just putting all of what you just said into code format so that I can test?
    I find it so interesting that there appear to be many solutions to a problem when it comes to VBA.


    Thanks much to both!
    Last edited by VBAProIWish; Mar 3rd, 2017 at 12:00 PM.
    I can't wait to change my name to
    "VBAPROIAM"


    Using Excel 2010

  5. #5
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    47,890
    Post Thanks / Like
    Mentioned
    40 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Code works when 2 cells are populated, but doesn't work when they are empty. I get Run-Time Error 1004

    If all columns past column N are locked/protected and blocked from selection, then I think you will need to make a slight modification to the code I gave you above, i.e.
    Code:
        If Range("C6").End(xlToRight).Column < 14 Then
            Range("C6").End(xlToRight).Offset(0, 1).FormulaR1C1 = _
                "=GETPIVOTDATA(""REPORT"",'Customer Chart'!R1C1,""NAME"",""John"")"
        End If
    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!"

  6. #6
    Board Regular
    Join Date
    Nov 2010
    Location
    Lafayette, Indiana
    Posts
    541
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code works when 2 cells are populated, but doesn't work when they are empty. I get Run-Time Error 1004

    It is difficult to do so because of the way Excel refers to the chart in your formula. I was having difficulty duplicating your chart to be able to get the exact same formula. So you will just have to build it yourself. Not that difficult.
    Mike Hirsch
    Using Excel 2016

  7. #7
    Board Regular
    Join Date
    Jul 2009
    Posts
    926
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code works when 2 cells are populated, but doesn't work when they are empty. I get Run-Time Error 1004

    Joe4,

    I tried that and here's what happened...

    The cell went to XFD6

    I received this error here...
    Run Time Error 1004
    The cell or chart that you are trying to change is protected and therefore is read-only.
    To modify a protected cell or chart, first remove protection using the Unprotect Sheet command (Review tab, Changes group). You may be prompted for a password.
    Any suggestions?

    Thanks
    I can't wait to change my name to
    "VBAPROIAM"


    Using Excel 2010

  8. #8
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    47,890
    Post Thanks / Like
    Mentioned
    40 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Code works when 2 cells are populated, but doesn't work when they are empty. I get Run-Time Error 1004

    You need to explain this in more detail, because it can mean many different things:
    So, in row 6....ALL of my cells are locked in row 6 except C6 through N6.
    Locked cells without Protecting the Sheet does nothing.
    When you Lock and Protect cells, you can lock/protect many different things (it is customizable).
    So, when you click "Protect Sheet", which options do you have selected under the heading "Allow all users of this worksheet to:"?
    Last edited by Joe4; Mar 3rd, 2017 at 02:10 PM.
    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!"

  9. #9
    Board Regular
    Join Date
    Jul 2009
    Posts
    926
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code works when 2 cells are populated, but doesn't work when they are empty. I get Run-Time Error 1004

    Select Unlocked Cells only
    I can't wait to change my name to
    "VBAPROIAM"


    Using Excel 2010

  10. #10
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    47,890
    Post Thanks / Like
    Mentioned
    40 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Code works when 2 cells are populated, but doesn't work when they are empty. I get Run-Time Error 1004

    That doesn't make any sense to me. If all cells other than C6:N6 in row 6 are locked and protected, and your Protect setting is "Select Unlocked Cells only"), you should not be able select cell XFD6.
    Can you confirm that cell XFD6 is locked?
    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!"

Some videos you may like

User Tag List

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
  •