VBA Error in a Function

[maloneb]

Hey Guys,

I'm having an issue with a function that I am using. Basically, the input change the outputs (as would be expected...), and two of the calculated outputs are used in the function, findASMEwallthickness.

This function filters the values in the second sheet (ASME B36. 10m-2004 Pipe Size) in accordance with the appropriate pipesize and uses the next wall thickness up from the calculated minimum wall thickness. In theory.

The function works fine for any pipe diameter over 350mm, yet if the pipe diameter is equal to or less than 350mm, the function gives out wron answers.

A link to the workbook is given below, thanks.

B

http://files.engineering.com/getfil...a-4b27-a727-b15dd912b566&file=PD8010_Test.xls

 

[CharlesChuckieCharles]

NO WAY SHOULD ANYONE CLICK ON THAT

use Excel genie to post your spreadsheet

 

[maloneb]

Oh man, sorry! I didn't know how to do it so used Engineering-Tips to upload it. How do you use excel Geenie?

Sorry!

 

[maloneb]

Here is the VBA code, I don't know how to upload the workbook.

Thanks


Function findASMEwallthickness(pipesize, selectedwt)
Dim ws As Worksheet
Dim FilterRange As Range
Dim i%, m
Set ws = Sheets("ASME B36.10M-2004 Pipe Size")

'Autofilter ASME sheet based on pipesize required
ws.Range(ws.Range("A1:J1"), ws.Range("A1:J1").End(xlDown)).AutoFilter Field:=1, Criteria1:=pipesize

'Set the lookup range to only column I, and visible cells (filtered cells)
Set FilterRange = ws.Range("I2:I" & Rows.Count).SpecialCells(xlCellTypeVisible)

'Locate closest match to require wall thickness in specified range (greater than value)
With FilterRange
i = .Row
m = Application.Match(selectedwt, .Value, 1)
'Error catcher
If Not IsError(m) Then i = .Cells(1, 1).Offset(m).Row
End With

'If pipe is >8", use two sizes above specified WT, else use only only next size up
If selectedwt > 8 Then

findASMEwallthickness = (ws.Cells(i + 1, 9).Value)
'MsgBox (ws.Cells(i + 1, 9).Value)
Else

findASMEwallthickness = (ws.Cells(i, 9).Value)
'MsgBox (ws.Cells(i, 9).Value)

End If

'Remove filter mask
ws.ShowAllData
End Function