ISERROR Runtime error "1004" issue

[jonesgirl0122]

I have been trying to get this code to work for 2 hours now. I have to columns of dates/times that I need to find the difference between and put that variable in another column that is formated as time. If there is a blank cell in either of those columns it gives me a #Value error. I'm trying to put a space if it returns that error. I'm new to excel and am trying to write a macro with this code and I keep getting Runtime error 1004. Please help.

Sub hoursdown()

Dim myRange As Range, lngLastRow As Long

Range("M1").Select
ActiveCell.FormulaR1C1 = "HH:MM:SS Time Down"
Range("M2").Select


Range("M2").FormulaR1C1 = "=if(iserror((DATE(MID(RC[-12],7,4),LEFT(RC[-12],2),MID(RC[-12],4,2))+TIMEVALUE(RIGHT(RC[-12],8)))-(DATE(MID(RC[-3],7,4),LEFT(RC[-3],2),MID(RC[-3],4,2))+TIMEVALUE(RIGHT(RC[-3],8)))),"""",(DATE(MID(RC[-12],7,4),LEFT(RC[-12],2),MID(RC[-12],4,2))+TIMEVALUE(RIGHT(RC[-12],8)))-(DATE(MID(RC[-3],7,4),LEFT(RC[-3],2),MID(RC[-3],4,2))+TIMEVALUE(RIGHT(RC[-3],8)))"

LastRow = ActiveSheet.UsedRange.Rows.Count
Range("M2").NumberFormat = "[h]:mm:ss;@"
Range("M2").AutoFill Destination:=Range("M2:M" & LastRow)

End Sub

 

[Jonmo1]

Welcome to the board.

You're missing a parenthesis )

In every formula, you must have equal # of ( and )
With the exeption if the ( is part of a text string inside quotes.

I added a ) at the very end.
And it at least compiles, but not sure if it results in the formula you want..

try

Range("M2").FormulaR1C1 = "=if(iserror((DATE(MID(RC[-12],7,4),LEFT(RC[-12],2),MID(RC[-12],4,2))+TIMEVALUE(RIGHT(RC[-12],8)))-(DATE(MID(RC[-3],7,4),LEFT(RC[-3],2),MID(RC[-3],4,2))+TIMEVALUE(RIGHT(RC[-3],8)))),"""",(DATE(MID(RC[-12],7,4),LEFT(RC[-12],2),MID(RC[-12],4,2))+TIMEVALUE(RIGHT(RC[-12],8)))-(DATE(MID(RC[-3],7,4),LEFT(RC[-3],2),MID(RC[-3],4,2))+TIMEVALUE(RIGHT(RC[-3],8))))"


The result is this formula in M2

=IF(ISERROR((DATE(MID(A2,7,4),LEFT(A2,2),MID(A2,4,2))+TIMEVALUE(RIGHT(A2,8)))-(DATE(MID(J2,7,4),LEFT(J2,2),MID(J2,4,2))+TIMEVALUE(RIGHT(J2,8)))),"",(DATE(MID(A2,7,4),LEFT(A2,2),MID(A2,4,2))+TIMEVALUE(RIGHT(A2,8)))-(DATE(MID(J2,7,4),LEFT(J2,2),MID(J2,4,2))+TIMEVALUE(RIGHT(J2,8))))

 

[Jonmo1]

Although, I now have a sneeking suspition that:

That whole huge formula can be replaced with

Range("M2").Formula = "=IF(ISERROR(A2-J2),"""",A2-J2)"

 

[jonesgirl0122]

Thanks so much for quick responses! The reason I had the formula that way is because the dates are in "general" format (from data exported) - and the formula worked fine until I put the if(iserror part. Jonmo, I tried the code you suggested and I didn't get the runtime 1004 anymore but did get a generic box with a red X and just 400 on it. What does this mean?

Thanks!

 

[Jonmo1]

OK, start over.

When using vba to enter formulas, I find it best to

First, enter the formula normally by hand.
When it works, THEN convert it to VBA

Can you post a formula that works as intended, that was entered manually by hand ?

Also post sample date/time data from A2 and J2

 

[jonesgirl0122]

Here it is


Range("M2").FormulaR1C1 = "=(DATE(MID(RC[-12],7,4),LEFT(RC[-12],2),MID(RC[-12],4,2))+TIMEVALUE(RIGHT(RC[-12],8)))-(DATE(MID(RC[-3],7,4),LEFT(RC[-3],2),MID(RC[-3],4,2))+TIMEVALUE(RIGHT(RC[-3],8)))"

 

[Jonmo1]

So that code works, now you want it added in an IF(ISERROR structure...

Try

Range("M2").FormulaR1C1 = "=if(iserror((DATE(MID(RC[-12],7,4),LEFT(RC[-12],2),MID(RC[-12],4,2))+TIMEVALUE(RIGHT(RC[-12],8)))-(DATE(MID(RC[-3],7,4),LEFT(RC[-3],2),MID(RC[-3],4,2))+TIMEVALUE(RIGHT(RC[-3],8)))),"""",(DATE(MID(RC[-12],7,4),LEFT(RC[-12],2),MID(RC[-12],4,2))+TIMEVALUE(RIGHT(RC[-12],8)))-(DATE(MID(RC[-3],7,4),LEFT(RC[-3],2),MID(RC[-3],4,2))+TIMEVALUE(RIGHT(RC[-3],8))))"

 

[jonesgirl0122]

Worked PERFECTLY!!!!! THANKS!!!~!~!