sassriverrat
Well-known Member
- Joined
- Oct 4, 2018
- Messages
- 655
Hello- I have a "Sheet Creator" that runs and creates a sheet, formats it, inserts formulas, and then allows user input of a few pieces of data to create a fairly in-depth report. Anyway, near the end of the coding the sheet is hanging up on a formula, see the red section of coding below. I have pulled just a small section of the code- I haven't tweaked this at all so I'm not sure why it's hanging up. Also with the hangup, it only hangs up when the "Noon" sheet (the code checks for) does not exist- so it's the same line of code. It's throwing the 1004 error application or user-defined error
Note: I gave you just enough code to see that other, similar formulas/code run without a problem.
Note: I gave you just enough code to see that other, similar formulas/code run without a problem.
Rich (BB code):
'Hours + Min Run Range("D8").FormulaR1C1 = ""
Const sFrm9 As String = "=IF(R[18]C[19]="""",(HOUR(ABS(((R4C4+(TIME(R5C3,0,0)))+R4C6)-(IF(PrevSheet(RC)<>0,((PrevSheet(R4C4)+(TIME(PrevSheet(R5C3),0,0))+PrevSheet(R4C6))),))))),R[18]C[19])"
Const sFrm10 As String = "=IF(R[-4]C[2]=""No Data Input"",0,IF(R[18]C[19]="""",((INT(ABS((((((R[-4]C+((IF(R[-1]C[1]=""E"",-TIME(R[-3]C[14],0,0),TIME(R[-3]C[14],0,0)))))+R[-4]C[2])-(('Voyage Specifics'!R[-2]C[-1]+((IF((VLOOKUP('Voyage Specifics'!R[-3]C[-1],Ports!R[-5]C[-2]:R[44]C[5],8,FALSE))<0,-TIME(-((VLOOKUP('Voyage Specifics'!R[-3]C[-1],Ports!R3C2:R52C9,8,FALSE))+(IF(R[13]C[22]=""Yes"",-1" & _
",0))),0,0),TIME(((VLOOKUP('Voyage Specifics'!R[-3]C[-1],Ports!R3C2:R52C9,8,FALSE))+(IF(R[13]C[22]=""Yes"",-1,0))),0,0)))))+'Voyage Specifics'!R[-1]C[-1])))))))*24)+(HOUR(ABS((((((R[-4]C+((IF(R[-1]C[1]=""E"",-TIME(R[-3]C[14],0,0),TIME(R[-3]C[14],0,0)))))+R[-4]C[2])-(('Voyage Specifics'!R[-2]C[-1]+((IF((VLOOKUP('Voyage Specifics'!R[-3]C[-1],Ports!R[-5]C[-2]:R[44]C[5]," & _
"8,FALSE))<0,-TIME(-((VLOOKUP('Voyage Specifics'!R[-3]C[-1],Ports!R3C2:R52C9,8,FALSE))+(IF(R[13]C[22]=""Yes"",-1,0))),0,0),TIME(((VLOOKUP('Voyage Specifics'!R[-3]C[-1],Ports!R3C2:R52C9,8,FALSE))+(IF(R[13]C[22]=""Yes"",-1,0))),0,0)))))+'Voyage Specifics'!R[-1]C[-1]))))))),R[18]C[19]))" & _
""
Const sFrm11 As String = "=IF(R[18]C[19]="""",(MINUTE(ABS(((R4C4+(TIME(R5C3,0,0)))+R4C6)-(IF(PrevSheet(RC)<>0,((PrevSheet(R4C4)+(TIME(PrevSheet(R5C3),0,0))+PrevSheet(R4C6))),))))),R[18]C[19])"
Const sFrm12 As String = "=IF(R[-4]C=""No Data Input"",0,IF(R[18]C[19]="""",(MINUTE(ABS((((((R[-4]C[-2]+((IF(R[-1]C[-1]=""E"",-TIME(R[-3]C[12],0,0),TIME(R[-3]C[12],0,0)))))+R[-4]C)-(('Voyage Specifics'!R[-2]C[-3]+((IF((VLOOKUP('Voyage Specifics'!R[-3]C[-3],Ports!R[-5]C[-4]:R[44]C[3],8,FALSE))<0,-TIME(-((VLOOKUP('Voyage Specifics'!R[-3]C[-3],Ports!R3C2:R52C9,8,FALSE))+(IF(R[13]C[20]=""Yes"",-" & _
"1,0))),0,0),TIME(((VLOOKUP('Voyage Specifics'!R[-3]C[-3],Ports!R3C2:R52C9,8,FALSE))+(IF(R[13]C[20]=""Yes"",-1,0))),0,0)))))+'Voyage Specifics'!R[-1]C[-3]))))))),R[18]C[19]))" & _
""
If LCase(ActiveSheet.Previous.Name) Like "noon*" Then
Range("D8") = sFrm9
Range("F8") = sFrm11
Else:
Range("D8") = sFrm10
Range("F8") = sFrm12
End If
Range("D25").FormulaR1C1 = "=R[-17]C"
Range("D42").FormulaR1C1 = "=R[-17]C"
Range("F25").FormulaR1C1 = "=R[-17]C"
Range("F42").FormulaR1C1 = "=R[-17]C"
'Total Hours + Min Run
Range("D9").FormulaR1C1 = ""
Const sFrm5 As String = "=IF(R[18]C[19]="""",R[-1]C+PrevSheet(RC)+IF((R[-1]C[2]+PrevSheet(RC[2]))>=60,(INT((R[-1]C[2]+PrevSheet(RC[2]))/60)),),R[18]C[19])"
Const sFrm6 As String = "=IF(R[18]C[19]="""",R[-1]C,R[18]C[19])"
Const sFrm7 As String = "=IF(R[18]C[19]="""",ABS(IF((R[-1]C+PrevSheet(RC))>=60,((INT((R[-1]C+PrevSheet(RC))/60)*60)-(R[-1]C+PrevSheet(RC))),R[-1]C+PrevSheet(RC))),R[18]C[19])"
Const sFrm8 As String = "=IF(R[18]C[19]="""",R[-1]C,R[18]C[19])"
If LCase(ActiveSheet.Previous.Name) Like "noon*" Then
Range("D9") = sFrm5
Range("F9") = sFrm7
Else:
Range("D9") = sFrm6
Range("F9") = sFrm8
End If
Range("D26").FormulaR1C1 = "=R[-17]C"
Range("D43").FormulaR1C1 = "=R[-17]C"
Range("F26").FormulaR1C1 = "=R[-17]C"
Range("F43").FormulaR1C1 = "=R[-17]C"
'Wind = R9
Range("D10").FormulaR1C1 = "=R[-2]C[14]"
Range("D27").FormulaR1C1 = "=R[-17]C"
Range("D44").FormulaR1C1 = "=R[-17]C"
'Wave = R10
Range("D11").FormulaR1C1 = "=R[-2]C[14]"
Range("D28").FormulaR1C1 = "=R[-17]C"
Range("D45").FormulaR1C1 = "=R[-17]C"
'Swell = R11
Range("D12").FormulaR1C1 = "=R[-2]C[14]"
Range("D29").FormulaR1C1 = "=R[-17]C"
Range("D46").FormulaR1C1 = "=R[-17]C"
'Temp = R12
Range("D13").FormulaR1C1 = "=R[-2]C[14]"
Range("D30").FormulaR1C1 = "=R[-17]C"
Range("D47").FormulaR1C1 = "=R[-17]C"
'Pressure = R13
Range("D14").FormulaR1C1 = "=R[-2]C[14]"
Range("D31").FormulaR1C1 = "=R[-17]C"
Range("D48").FormulaR1C1 = "=R[-17]C"
'Officer = R14
Range("J15").FormulaR1C1 = "=R[-2]C[8]"
Range("J32").FormulaR1C1 = "=R[-17]C"
Range("J49").FormulaR1C1 = "=R[-17]C"
'Selected Arrival
Range("I5:J5").FormulaR1C1 = "=IF('Voyage Specifics'!R[3]C[-6]="""",""No Port Selected"",'Voyage Specifics'!R[3]C[-6])"
Range("I22:J22").FormulaR1C1 = "=R[-17]C"
Range("I39:J39").FormulaR1C1 = "=R[-17]C"
'Voyage # = R8
Range("N4").FormulaR1C1 = "='Voyage Specifics'!RC[-11]"
Range("N21").FormulaR1C1 = "=R[-17]C"
Range("N38").FormulaR1C1 = "=R[-17]C"
'Course = R8
Range("N5").FormulaR1C1 = "=R[2]C[4]"
Range("N22").FormulaR1C1 = "=R[-17]C"
Range("N39").FormulaR1C1 = "=R[-17]C"
'Speed
Range("N6").FormulaR1C1 = "=IF((R[22]C[9]=""""),(R[1]C[]/(R[2]C[-10]+(R[2]C[-8]/60))),R[22]C[9])"
Range("N23").FormulaR1C1 = "=R[-17]C"
Range("N40").FormulaR1C1 = "=R[-17]C"
'Trip Distance + Total Distance
'Trip
Const sFrm1 As String = "=IF(R[22]C[10]="""",R[1]C-PrevSheet(R[1]C),R[22]C[10])"
Const sFrm2 As String = "=R[1]C"
'Total
Const sFrm3 As String = "=IF(R[22]C[10]="""",IF(R[21]C[10]="""",(IF(R[12]C[12]=""Yes"",(IF(PrevSheet(R[3]C)='Voyage Specifics'!R[3]C[-11],'Voyage Specifics'!R[3]C[-11],PrevSheet(R[3]C))),R[-2]C[4]+'Voyage Specifics'!R[4]C[-11])),(IF(R[12]C[12]=""Yes"",(IF(PrevSheet(R[3]C)='Voyage Specifics'!R[3]C[-11],'Voyage Specifics'!R[3]C[-11]+R[-1]C,PrevSheet(R[3]C)+R[-1]C)),R[-2]C[4]+'Voyage Specifics'-11]+R[-1]C))),R[22]C[10])"
Const sFrm4 As String = "=IF(R[22]C[10]="""",'Voyage Specifics'!R[3]C[-11],R[22]C[10]"
If LCase(ActiveSheet.Previous.Name) Like "noon*" Then
Range("N7") = sFrm1
Range("N8") = sFrm3
Else:
Range("N7") = sFrm2
Range("N8") = sFrm4
End If
Range("N24").FormulaR1C1 = "=R[-17]C"
Range("N41").FormulaR1C1 = "=R[-17]C"
Range("N25").FormulaR1C1 = "=R[-17]C"
Range("N42").FormulaR1C1 = "=R[-17]C"
'TAS
Range("N9").FormulaR1C1 = "=IF(R[22]C[10]="""",R[-1]C/(RC[-10]+(RC[-8]/60)),R[22]C[10])"
Range("N26").FormulaR1C1 = "=R[-17]C"
Range("N43").FormulaR1C1 = "=R[-17]C"
'Heavy Weather Time
Range("K10").FormulaR1C1 = "=R[7]C[7]"
Range("K27").FormulaR1C1 = "=R[-17]C"
Range("K44").FormulaR1C1 = "=R[-17]C"
'Heavy Weather Total Time
WS_Count = ActiveWorkbook.Worksheets.Count
Eqat1 = "="
nooncnt = 0
For i = 1 To WS_Count
Tname = ActiveWorkbook.Worksheets(i).Name
If Left(Tname, 4) = "Noon" Then
Eqat1 = Eqat1 & "+" & Tname & "!N12"
nooncnt = nooncnt + 1
End If
Next i
If nooncnt > 0 Then
Range("N10").Formula = Eqat1 & "+R17"
End If
If IsNumeric(Range("D8").Value) Then
If Range("R17").Value > (Range("D8").Value + (Range("F8").Value / 60)) Then
Range("N10").Value = "Error"
End If
End If
Range("N27").FormulaR1C1 = "=R[-17]C"
Range("N44").FormulaR1C1 = "=R[-17]C"