Announcements
Attention for Customers without Multi-Factor Authentication or Single Sign-On - OTP Verification rolls out April 2025. Read all about it here.
KWarrenCA
in reply to: WCrihfield

I was able to rework this code to get it to work the way I wanted it to by looking for stocked items first if no stocked items were found it would search again for non stocked items.

 

''Sheet Metal Stock Number
'Checks to see if the file is sheet metal. If not it will return.
If oSubType = "Sheet Metal" Then

'Pulls flat pattern extents
	Dim oLength As Double
	Dim oWidth As Double

	ex_Length = SheetMetal.FlatExtentsLength
	ex_Width = SheetMetal.FlatExtentsWidth

'Checks flat pattern to see what length is longer and defines the longer side to be length
	If ex_Length > ex_Width Then
		oLength = ex_Length
		oWidth = ex_Width
	Else
		oWidth = ex_Length
		oLength = ex_Width
	End If



'Define Excel File to Lookup
	ExcelFile = "C:\CA Workspace\CAD Standards\CA Library A\iLogic Automation\CA - Sheetmetal Part Number Selection - Table.xlsx"
'Ignores any excel alerts
	GoExcel.DisplayAlerts = False

'Message box warning for non stocked items
	NonStockedNote = "This Item is not Stocked. Check with Purchasing on Availablity!"
	
	Dim SheetMetalStyle As String
	SheetMetalStyle = SheetMetal.GetActiveStyle
	SheetMaterial = iProperties.Material
	
'Checks the sheet if it is larger than 72 X 144 notifies user sheet is custom order
If (oWidth >72) AndAlso (oLength >144) Then
	i = MessageBox.Show("Sheet is too large for Stocked material. Check with Purchasing if we can order custom sheet sizes", "Too Large of Sheet", MessageBoxButtons.OK, MessageBoxIcon.Hand, MessageBoxDefaultButton.Button1)
	SheetPartnumber = "Check With Purchasing if we can order a sheet this size."
End If


''Setting Width depending on sheet size
If (oWidth <= 48) AndAlso (oWidth >0) Then
	oSetWidth = 48
Else If (oWidth <= 60) AndAlso (oWidth > 48.000001)
	oSetWidth = 60
Else If (oWidth <=72) AndAlso (oWidth > 60.000001) Then
	oSetWidth = 72
End If

''Setting Length depending on sheet size
If (oLength <= 120) AndAlso (oLength > 0) Then
	oSetLength = 120
Else If (oLength <= 144) AndAlso (oLength > 120.000001)Then
	oSetLength = 144
End If

'Used to verify it works on sheet size
'i = MessageBox.Show(oSetWidth & OSetLength, "Sheet Size", MessageBoxButtons.OK, MessageBoxIcon.None, MessageBoxDefaultButton.Button1)


Dim rowNum As Integer = GoExcel.FindRow(ExcelFile, "Stocked", "Sheet Metal Rule", "=", SheetMetalStyle, "Material", "=", iProperties.Material, "Sheet Width (Short Side)", ">=", oSetWidth, "Sheet Length (Long Side)", ">=", oSetLength, "Stocked", "=", "Yes")

'Checks if no option is available then displays message box to user
If (rowNum >0) Then
			SheetPartnumber = GoExcel.CurrentRowValue("Part Number")
			CostCode = GoExcel.CurrentRowValue("Cost Code")
 			question = MessageBox.Show("The Stock Number will be replaced with the "& SheetPartnumber &" are you sure you want to procceed?", "Warning Stock Number Change", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1)
				If question = vbNo Then
					Return
					Else If question = vbYes Then
				iProperties.Value("Project", "Stock Number") = SheetPartnumber
				iProperties.Value("Project", "Cost Center") = CostCode
				Return
			End If
		
	Else If (rowNum <0) Then
	End If
	
Dim rowNum2 As Integer = GoExcel.FindRow(ExcelFile, "Stocked", "Sheet Metal Rule", "=", SheetMetalStyle, "Material", "=", iProperties.Material, "Sheet Width (Short Side)", ">=", oSetWidth, "Sheet Length (Long Side)", ">=", oSetLength, "Stocked", "=", "No")

If (rowNum2 >0)Then
			SheetPartnumber = GoExcel.CurrentRowValue("Part Number")
			CostCode = GoExcel.CurrentRowValue("Cost Code")
			question = MessageBox.Show(NonStockedNote & "The Stock Number will be replaced with the "& SheetPartnumber &" are you sure you want to procceed?", "Non Stocked Sheet", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1)
				If question = vbNo Then
					Return
					Else If question = vbYes Then
				iProperties.Value("Project", "Stock Number") = SheetPartnumber
				iProperties.Value("Project", "Cost Center") = CostCode
				Return
				End If
		Else If (rowNum2 <0) Then
			i = MessageBox.Show("No Sheet Size Available. Check Purchasing for Part Number.", "No Sheet Size Available", MessageBoxButtons.OK, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1)
		End If


Else 
	Return
End If