Thread: #NAME? Error

Hi

I am trying to get my spreadsheet to automatically calculate the pipes i am still required to order in of a specific diameter/class by taking the pipes i have on site then subtracting from them the pipes of a specific diameter/class to complete a section of pipeline then move onto the next section of pipeline and calculate it again however it returns #NAME? error

Please help

Many thanks

Paul

'Initiating function
Function Pipes_Required(Pipe_Class As String, Pipes_On_Site As Integer) As Integer
    
    'define counter
    Dim i As Integer
    
    'while cell isnt blank
    While Worksheets("Pavement_Drainage_Data").Cells(i, 7) <> ""
    
        'If Pipe classes are the same
        If Pipe_Class = Worksheets("Pavement_Drainage_Data").Cells(i, 7) Then
            
            'minus pipes from delivered pipes
             Pipes_Required = Pipes_Required - Pipes_On_Site
        
        'End if
        End If
    
    'Step counter
    i = i + 1
    
    'End while loop
    Wend
    
'Return function
End Function

Hi here is the workbook I am trying to run the function on

Is it the function that's not initializing, how come what have I done wrong

Cheers Paul

Hi sorry for the vagueness I will try again. so now i have split it up int two functions one to sum the pipes required and then one to sum the pipes on site

'Initiating function
Function Pipes_Required(Pipe_Class As String) As Integer
    
    'define counter
    Dim i As Integer
    
    'while cell isnt blank
    While Worksheets("Pavement_Drainage_Data").Cells(i, "F") <> ""
    
        'If Pipe classes are the same
        If Pipe_Class = Worksheets("Pavement_Drainage_Data").Cells(i, "F") Then
            
            'minus pipes from delivered pipes
             Pipes_Required = Pipes_Required - Worksheets("Pavement_Drainage_Data").Cells(i, "H")
        
        'End if
        End If
    
    'Step counter
    i = i + 1
    
    'End while loop
    Wend
    
'Return function
End Function

is designed to step through the Pavement_Drainage_Data worksheet and sum up the number of pipes that share the same diameter and class as designated in Pipe_Class.

' Initialise Function
Function Pipes_On_Site(Pipe_Class As String) As Double
    
    'Initilise i
    Dim i As Integer
    
    'set i
    i = 2
    
    'While rows are not blank
    While Workbooks("Drainage").Worksheets("General_Data").Cells(i, 6) <> ""
        
        'if pipe diameters and classes are equal
        If Workbooks("Drainage").Worksheets("General_Data").Cells(i, 11) = Pipe_Class And Workbooks("Drainage").Worksheets("General_Data").Cells(i, 7) = "z5000" Then
            
            'then add pipes to pipes on site
            Pipes_On_Site = Pipes_On_Site + Cells(i, 6)
        End If
        
        'step i
        i = i + 1
    
    Wend

End Function

is designed to step through a separate workbook with delivery data on it Workbooks("Drainage").Worksheets("General_Data") and sum up all the deliveries with the same diameter and class as designated in Pipe_Class and return them to this sheet. the idea is as this delivery spreadsheet is updated the pipes required data will stay the same and by subtracting the pipes delivered from the pipes required i will know how many are still to come in or that i have to order.

Many thanks

Paul

Hi
I think i did what you said however the error is still present please see attached screen dump to see if i have any errors

As far as I can see that function must always return a negative value. You have a line of code to subtract pipes delivered from the required number, but you don't have any code which adds anything to the number of pipes required.

You also don't set a starting values for fPipes_Required.

Personally when coding functions I don't like using the function name as a working variable within the function. I'll create a new variable, initialise it, use it in the function and then assign its value to the function before I exit.

Other than that the function looks OK. Have you tried setting a break point and stepping through the code?

Hi so I solved the name error, turns out my spreadsheet was blocking macros so enabled that and that solved the first problem. Now I am getting a Value error for the second function Pipes_On_Site code I think maybe its because I am using integer where as the numbers are too 2 decimal points, what initialization should I use.

'Initialise Function
Function fPipes_On_Site(Pipe_Class As String) As Integer

    
    'Initilise i
    Dim i As Integer: i = 2
      
    'While rows are not blank
    While Workbooks("Drainage").Worksheets("General_Data").Cells(i, "F") <> ""
        
        'if pipe diameters and classes are equal
        If Pipe_Class = Workbooks("Drainage").Worksheets("General_Data").Cells(i, "K") Then
        
            'then add pipes to pipes on site
            fPipes_On_Site = fPipes_On_Site + Workbooks("Drainage").Worksheets("General_Data").Cells(i, "F")
        
        'End if
        End If
        
    'Step counter
    i = i + 1
    
    Wend

End Function