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

V

VBAProIWish

Well-known Member
Joined
Jul 6, 2009
Messages
1,027
Office Version
  1. 365
Platform
  1. Windows
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:

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
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
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
Click to expand...
Any suggestions?

Thanks
 
Upvote 0
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.
Click to expand...
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:
Upvote 0
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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Lock cells when text entered into another vba not working
Replies
0
Views
387
P4444
P
O
  • Solved
Code works EXCEPT when code is running??
Replies
6
Views
176
OxfordCurmudgeon
O
I
  • Solved
Cell above is selected when i duplicate row
Replies
2
Views
63
ipbr21054
I
A
  • Solved
Error with working days in a month
Replies
5
Views
242
Anthony47
A
D
  • Solved
Unable to select a cell in a worksheet
Replies
8
Views
201
rlv01
R

Forum statistics

Threads
1,214,522
Messages
6,120,022
Members
448,939
Latest member
Leon Leenders

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top