Problem with Run Time 1004 Error

J

jconkl02

Board Regular
Joined
May 25, 2016
Messages
55
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.


Code: 
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
ABCDEF
SEQNotification StatusDateDateTimeShift
7489523-Update-42968.7826388889Update
7489523-Final-42969.1486111111Final
7445261-Initial-42969.3805555556Initial
7445261-Final-42969.4104166667Final
7084220-Update-42969.6527777778Update
7341942-Final-42969.6791666667Final
7084220-Update-42969.6944444444Update
7084220-Final-42969.7159722222Final

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

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
'Chart Data-Email Shif" & _
"TCH(""Swing Shift Start"",'Chart Data-Email Shift'!C2,0))+8/24),""Swings"",""Mids""))"
Click to expand...

Looks like two incomplete words. That would throw the error.
 
Upvote 0
Solution
JLGWhiz said:
Looks like two incomplete words. That would throw the error.
Click to expand...

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.
Code: 
=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
BC
ShiftTime
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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""))"
 
Upvote 0
steve the fish said:
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""))"
Click to expand...

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

Similar threads

T
  • Question Question
Using Excel to create a schedule table from given data table.
Replies
1
Views
857
Herakles
H
J
  • Question Question
Need help calculating Day/Night Shift Hours.
Replies
4
Views
2K
jtedmonson42
J
D
  • Question Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
349
dilshod_k
D
F
  • Question Question
Enhance simply subtract of date/times of time spent, to including indexing and subtracting time blocked from another table (with multiple criteria)
Replies
0
Views
388
freeb1893
F
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

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