VBA stuck on formula

S

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.

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"
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
Code: 
[COLOR=#333333]Const sFrm4 As String = "=IF(R[22]C[10]="""",'Voyage Specifics'!R[3]C[-11],R[22]C[10]"[/COLOR]

I don't see a closing bracket in this line
 
Upvote 0
Check if you have sufficient brackets on that one. Also, I'd recommend using a Formula1 style and using IFS() instead of several nested IF() functions for readability. But that is just personal preference.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Dynamic Sum Range Addresses...(?)
Replies
2
Views
508
Kenny_D
K
U
  • Solved
Rewrite Code as a Sub to work on Active Sheet
Replies
2
Views
54
Skyybot
Skyybot
S
  • Solved
Alternate to pivot table
Replies
5
Views
245
Fluff
Fluff
A
  • Question Question
Data Selection Based on Closest Value [20% Tolerance]
Replies
1
Views
211
StephenCrump
S
S
  • Question Question
Easier formula approach?
Replies
5
Views
246
Cubist
Cubist

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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