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
1	SEQ	Notification Status	Date	Date	Time	Shift
2	7489523-Update-42968.7826388889	7489523	Update	08/21/2017	6:47:00 PM
3	7489523-Final-42969.1486111111	7489523	Final	08/22/2017	3:34:00 AM
4	7445261-Initial-42969.3805555556	7445261	Initial	08/22/2017	9:08:00 AM
5	7445261-Final-42969.4104166667	7445261	Final	08/22/2017	9:51:00 AM
6	7084220-Update-42969.6527777778	7084220	Update	08/22/2017	3:40:00 PM
7	7341942-Final-42969.6791666667	7341942	Final	08/22/2017	4:18:00 PM
8	7084220-Update-42969.6944444444	7084220	Update	08/22/2017	4:40:00 PM
9	7084220-Final-42969.7159722222	7084220	Final	08/22/2017	5:11:00 PM

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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
1	Shift	Time
2	Day Shift Start	7:00 AM
3	Day Shift End	2:59 PM
4	Swing Shift Start	3:00 PM
5	Swing Shift End	10:59 PM
6	Mid Shift Start	11:00 PM
7		11:59 PM
8		12:00 AM
9	Mid Shift End	6:59 AM

<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>
</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