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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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

L
  • Question
Formula & Sub to format phone numbers working by itself but not in conjunction with other code.
Replies
4
Views
205
Scott Huish
Scott Huish
P
  • Question
ListBox Click to textbox & delete row
Replies
0
Views
357
pure vito
P
gtd526
  • Question
vba Sorting Columns w/ 2 Criteria's
Replies
1
Views
162
gtd526
gtd526
E
  • Solved
Get a 2D spilled formula to fulfill a distribution table matching the ranges of both dimensions
Replies
3
Views
94
Fluff
Fluff
G
  • Question
Formula to Retrieve uniqueID row Above
Replies
1
Views
365
Trebor76
T

Forum statistics

Threads
1,215,720
Messages
6,126,436
Members
449,314
Latest member
MrSabo83

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