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

[VBAProIWish]

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!

 Range("C6").Select
ActiveCell.End(xlToRight).Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""REPORT"",'Customer Chart'!R1C1,""NAME"",""John"")"

 

[VBAProIWish]

Confirmed, I cannot get to that cell. Only when running the code you provided, will it let me select that cell.

 

[Joe4]

I don't think it is my section of code doing it, but rather other code that you have in there after that. You could easily confirm that by stepping through your code (using the F8 key to see which rows of code are being processed while watching what is happening on your sheet).

I would have to see the other code you have after that.

 

[VBAProIWish]

Ok, I'll check that out over the weekend and follow up next week.
Thanks much!

 

[Joe4]

If you are not sure, just post all your code, and we should hopefully be able to see what is going on.

 

[VBAProIWish]

Hello,

So, I have been analyzing the code and the worksheet and here's something interesting that I found...

(keeping in mind that cells C6 through N6 are the only cells that can be selected and edited).

The worksheet is protected and all cells are locked except for the following cells, which are unlocked...
Cells C6 to N6
Cells C14 to N14
Cells C22 to N22
Cells C30 to N30
Nothing else can be selected because all other cells are locked. Above are the only unlocked cells in the worksheet.



Some worthy things I discovered...


If cell C6 is NOT blank and D6 IS blank
If I am in cell C6 and I hit the right arrow on the keyboard while holding down the CTRL key on the keyboard, nothing changes. C6 remains the selected cell.

If both cells C6 & D6 are NOT blank
If I am in cell C6 and I hit the right arrow on the keyboard while holding down the CTRL key on the keyboard, D6 becomes the selected cell.

So, that means that the code only works correctly if the first two cells (in this case C6 and D6) are not blank.

If you can make this work with when both C6 & D6 are both blank, that would be great. I'd even take code that works when only cell D6 is blank.

I double-checked and I can tell you that there is nothing in any code both before and after that is causing this error.

Thanks for you help on this

 

[Joe4]

OK, you have described the set-up and current behavior, which I am table to reproduce.
So what is it exactly that you want to happen here?
Are you just trying to select the next blank cell in the row?

 

[VBAProIWish]

Yes, I believe that after some searching, I may have found the answer?

Do you think that this will work all the time in my current scenario?

Sub FirstBlankCell_in_a_Range()
     
    With ActiveSheet
        If IsEmpty(.Range("C6")) Then
            Application.Goto .Range("C6"), True
        ElseIf IsEmpty(.Range("D6")) Then
            Application.Goto .Range("D6"), True
        ElseIf IsEmpty(.Range("E6")) Then
            Application.Goto .Range("E6"), True
        ElseIf IsEmpty(.Range("F6")) Then
            Application.Goto .Range("F6"), True
        ElseIf IsEmpty(.Range("G6")) Then
            Application.Goto .Range("G6"), True
        ElseIf IsEmpty(.Range("H6")) Then
            Application.Goto .Range("H6"), True
        ElseIf IsEmpty(.Range("I6")) Then
            Application.Goto .Range("I6"), True
        ElseIf IsEmpty(.Range("J6")) Then
            Application.Goto .Range("J6"), True
        ElseIf IsEmpty(.Range("J6")) Then
            Application.Goto .Range("J6"), True
        ElseIf IsEmpty(.Range("K6")) Then
            Application.Goto .Range("K6"), True
        ElseIf IsEmpty(.Range("L6")) Then
            Application.Goto .Range("L6"), True
        ElseIf IsEmpty(.Range("M6")) Then
            Application.Goto .Range("M6"), True
        ElseIf IsEmpty(.Range("N6")) Then
            Application.Goto .Range("N6"), True
        Else
            Application.Goto .Range("C6")
        End If
    End With
     
End Sub

With the code above, might there be a condition where it won't work (i.e. locked cells, etc...)

 

[Joe4]

You can shorten that up a bit like this:

Sub FirstBlankCell()

    Dim cell As Range
    
    Range("C6").Select
    
    For Each cell In Range("C6:N6")
        If cell = "" Then
            cell.Select
            Exit For
        End If
    Next cell
    
End Sub

 

[VBAProIWish]

Ok, thanks for that. It's less code and works great.

On one last note with this...

That same code is repeated 3 more times for rows 14, 22 & 30. Formulas are then pasted into these dynamic cells. Is there a way to go back to each of the 4 populated cells and do a copy/paste special/values only....
actually, I just remembered that there's probably a way to say "values only" right from within the existing code you gave me. I just don't know where to put it?

Ahh, wait, so here's the current pasting not included in your last code...

   Range("C22").Select
    
    For Each cell In Range("C22:N22")
        If cell = "" Then
            cell.Select
            Exit For
        End If
    Next cell
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""REPORT"",Customer Chart'!R1C1,""Code"",""John Smith"")"

Thanks much

 

[VBAProIWish]

Never mind, I got it!

    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

Thanks for all your help Joe4!

Much appreciated