jonesgirl0122
New Member
- Joined
- Jul 13, 2010
- Messages
- 6
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
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