I received type mismatch(13) error when I tried to run the sub. It seems to be about the parameter j I pass to function DivisionCase. I don't understand why, please enlighten me. Second thought: Do I need to pass j byref?

    Function Last_Row(column)
    'Find the last Row with data in a column
        With ActiveSheet
            Last_Row = .Cells(.Rows.Count, column).End(xlUp).Row
        End With
    End Function

    Sub verticalProfileCalc()
    Dim currentParaBVC As Variant: currentParaBVC = Null
    Dim j As Long
    Dim value1 As Variant
    Dim value2 As Variant
    
    For j = 2 To Last_Row(11)
    
    value1 = CDec(Cells(j, 12).Value)
    value2 = CDec(Cells(j, 13).Value)
    Cells(j, 16).Value = CDec(DivisionCase(value1, value2, j, currentParaBVC))
    Next j
    End Sub

    Function DivisionCase(ByVal downchain As Variant, ByVal upchain As Variant, ByVal currentRow As Long, ByRef currentParaBVC As Variant) As Variant
    
    Dim downchainSection As Integer: downchainSection = Cells(currentRow, 14).Value
    Dim upchainSection As Integer: upchainSection = Cells(currentRow, 15).Value
    Dim verticalProfile As Variant: verticalProfile = CDec(Cells(currentRow, 16).Value)
    Dim LineType As String: LineType = Application.VLookup(downchainSection, "CurveSchedule", 4, False)
    Dim Grade As Variant
    Dim ParabolicLength As Variant: ParabolicLength = CDec(Application.VLookup(downchainSection, "CurveSchedule", 3, False) - Application.VLookup(downchainSection, "CurveSchedule", 2, False))
        If currentRow = 2 Then
        verticalProfile = CDec(Range("InitialPoint").Value)
        'temporarily assignd initvalue to cell P2
        ElseIf downchainSection = upchainSection Then
            
                If LineType = "Straight" Then
                    'do something
                    
                ElseIf LineType = "Parabolic" Then
                   'do something
                End If
                
            ElseIf ((upchainSection - downchainSection) = 1) Then
                
                If LineType = "Straight" Then
                    'do something                          
                ElseIf LineType = "Parabolic" Then
                    'do something
                End If
                
            End If
        DivisionCase = CDec(verticalProfile)
    End Function
    

CodePudding user response：

One possible solution

I gave your code a shot with some made up data on my end, and here's what I see. This line:

Dim LineType As String: LineType = Application.VLookup(downchainSection, "CurveSchedule", 4, False)

It fails on a Type 13 Mismatch because it should be:

Dim LineType As String: LineType = Application.WorksheetFunction.VLookup(downchainSection, "CurveSchedule", 4, False)

You have the object reference wrong; you need the WorksheetFunction. That's probably not your only problem, though...

Other possible issues to check

Just fixing the above didn't work, though. I don't know what "CurveSchedule" is referring to -- presumably a named range or a list object table?. Either way, VBA is reading that as a string as written. If it's a named range, I got it to work with ActiveSheet.Range("CurveSchedule"). So the line that works for me looks like this:

Dim LineType As String: LineType = Application.WorksheetFunction.VLookup(downchainSection, Range("CurveSchedule"), 4, False)

Also, because I'm making up my own data, I had an issue with the var downchainSection because I didn't have any value in that cell when it was assigned. Presumably, you do, so probably not an issue for you.

I don't know if that's your issue or not, but if this doesn't help you, I strongly recommend stepping through with F8 (on Windows, anyway) and tell us exactly what line of code it's failing on. Once I find the line that the code is failing, I start changing the line to simpler things (because I tend to write complex thinking I'm better than I am) until I find the line working, then work my way back to complex seeing what works.