Problem with Run Time 1004 Error

[jconkl02]

To start, I know just enough VBA to be dangerous. I know my code is sloppy and ineffecient. I'm working on that. I understand that .Select is kind of frowned upon, especially with ranges but I have not figured out the way around it yet.


With that said here is my problem. I have a formula that Board Regulars AlanY and etaf were nice enough to help me with. Now I'm trying to paste that formula in every row when there is something in column A of that row.

Columns("F:F").Select    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Shift"
    Range("F2:F" & Cells(Rows.Count, "A").End(xlUp).Row).FormulaR1C1 = _
        "=IF(AND(RC[-1]>=INDEX('Chart Data-Email Shift'!C3,MATCH(""Day Shift Start"",'Chart Data-Email Shift'!C2,0)),RC[-1]<=INDEX('Chart Data-Email Shift'!C3,MATCH(""Day Shift Start"",'Chart Data-Email Shift'!C2,0))+8/24),""Days"",IF(AND(RC[-1]>=INDEX('Chart Data-Email Shift'!C3,MATCH(""Swing Shift Start"",'Chart Data-Email Shift'!C2,0)),RC[-1]<=INDEX('Chart Data-Email Shif" & _
        "TCH(""Swing Shift Start"",'Chart Data-Email Shift'!C2,0))+8/24),""Swings"",""Mids""))"

Excel 2012

	A	B	C	D	E	F
SEQ	Notification Status	Date	Date	Time	Shift
7489523-Update-42968.7826388889	Update
7489523-Final-42969.1486111111	Final
7445261-Initial-42969.3805555556	Initial
7445261-Final-42969.4104166667	Final
7084220-Update-42969.6527777778	Update
7341942-Final-42969.6791666667	Final
7084220-Update-42969.6944444444	Update
7084220-Final-42969.7159722222	Final

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]7489523[/TD]

[TD="align: right"]08/21/2017[/TD]
[TD="align: right"]6:47:00 PM[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]7489523[/TD]

[TD="align: right"]08/22/2017[/TD]
[TD="align: right"]3:34:00 AM[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]7445261[/TD]

[TD="align: right"]08/22/2017[/TD]
[TD="align: right"]9:08:00 AM[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]7445261[/TD]

[TD="align: right"]08/22/2017[/TD]
[TD="align: right"]9:51:00 AM[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]7084220[/TD]

[TD="align: right"]08/22/2017[/TD]
[TD="align: right"]3:40:00 PM[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]7341942[/TD]

[TD="align: right"]08/22/2017[/TD]
[TD="align: right"]4:18:00 PM[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]7084220[/TD]

[TD="align: right"]08/22/2017[/TD]
[TD="align: right"]4:40:00 PM[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]7084220[/TD]

[TD="align: right"]08/22/2017[/TD]
[TD="align: right"]5:11:00 PM[/TD]
[TD="align: right"][/TD]

</tbody>

IMPORT-EMAIL​

But I get the Run time 1004 error. I have tried it in blank workbooks as well with the same result. Here is the worksheet:

 

[JLGWhiz]

'Chart Data-Email Shif" & _
"TCH(""Swing Shift Start"",'Chart Data-Email Shift'!C2,0))+8/24),""Swings"",""Mids""))"

Looks like two incomplete words. That would throw the error.

 

[jconkl02]

Looks like two incomplete words. That would throw the error.

That is weird. The misspelled words were not in the original formula. It wasn't until I was recording the macro and pasted the formula into the cell that they showed up misspelled.

However, that did not fix my problem. I'm still getting the Run Time 1004 at the same spot. Here is the original formula.

=IF(AND(F2>=INDEX('Chart Data-Email Shift'!$C:$C,MATCH("Day Shift Start",'Chart Data-Email Shift'!$B:$B,0)),F2<=INDEX('Chart Data-Email Shift'!$C:$C,MATCH("Day Shift Start",'Chart Data-Email Shift'!$B:$B,0))+8/24),"Days",IF(AND(F2>=INDEX('Chart Data-Email Shift'!$C:$C,MATCH("Swing Shift Start",'Chart Data-Email Shift'!$B:$B,0)),F2<=INDEX('Chart Data-Email Shift'!$C:$C,MATCH("Swing Shift Start",'Chart Data-Email Shift'!$B:$B,0))+8/24),"Swings","Mids"))

It's looking for the start/stop times of the different shifts that are on the "Chart Data-Email Shift" worksheet:
Excel 2012

	B	C
Shift	Time
Day Shift Start
Day Shift End
Swing Shift Start
Swing Shift End
Mid Shift Start
Mid Shift End

<colgroup><col style="width: 25pxpx" https:="" www.mrexcel.com="" forum="" usertag.php?do="list&action=hash&hash=DAE7F5"" target="_blank"></colgroup><colgroup><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]7:00 AM[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2:59 PM[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3:00 PM[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]10:59 PM[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]11:00 PM[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]11:59 PM[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]12:00 AM[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]6:59 AM[/TD]

</tbody>

Chart Data-Email Shift​

I want it to populate the cell in column F if there is anything in Column A.

 

[JLGWhiz]

The 1004 run time error is normally attributable to incorrect or incomplete user input such as missing double, double quote marks in formulas to be pasted into cells, misspelled sheet names or difference in upper and lower case for case sensitive strings. The sheet names must be exact matches to the name on the sheet name tab and the sheets must exist. Also, if .Formula is used to assign the value to the cell the the A1 format must be used for the range references. If .FormulaR1C1 is used then the range references must be in R1C1 format.

 

[steve the fish]

There is a bug in the macro recorder that will miss about 8 digits of long formulas if you record those formulas being committed to a cell.

 

[steve the fish]

You need to add this to your formula string:

t'!C2,MA

so it becomes:

"=IF(AND(RC[-1]>=INDEX('Chart Data-Email Shift'!C3,MATCH(""Day Shift Start"",'Chart Data-Email Shift'!C2,0)),RC[-1]<=INDEX('Chart Data-Email Shift'!C3,MATCH(""Day Shift Start"",'Chart Data-Email Shift'!C2,0))+8/24),""Days"",IF(AND(RC[-1]>=INDEX('Chart Data-Email Shift'!C3,MATCH(""Swing Shift Start"",'Chart Data-Email Shift'!C2,0)),RC[-1]<=INDEX('Chart Data-Email Shift'!C2,MA" & _
"TCH(""Swing Shift Start"",'Chart Data-Email Shift'!C2,0))+8/24),""Swings"",""Mids""))"

 

[jconkl02]

You need to add this to your formula string:

t'!C2,MA

so it becomes:

"=IF(AND(RC[-1]>=INDEX('Chart Data-Email Shift'!C3,MATCH(""Day Shift Start"",'Chart Data-Email Shift'!C2,0)),RC[-1]<=INDEX('Chart Data-Email Shift'!C3,MATCH(""Day Shift Start"",'Chart Data-Email Shift'!C2,0))+8/24),""Days"",IF(AND(RC[-1]>=INDEX('Chart Data-Email Shift'!C3,MATCH(""Swing Shift Start"",'Chart Data-Email Shift'!C2,0)),RC[-1]<=INDEX('Chart Data-Email Shift'!C2,MA" & _
"TCH(""Swing Shift Start"",'Chart Data-Email Shift'!C2,0))+8/24),""Swings"",""Mids""))"

steve the fish,

You were close. Actually what I needed to add was t'!C3,MA not t'!C2,MA. You got me on the right track and I truly appreciate your help.

Jason