How is this giving me a "Object variable or With block variable not set" error

AMN3161 · ‎11-02-2020

I know this is a very likely unnecessarily long and not optimized code but i am novice and still learning. This is how i know how to write code at this stage so bear with the chunkiness of the code

Ok so this is a long rule so I will post the relevant sections, right in the beginning of the rule i define a bunch of strings that i use throughout the rule

PartNumber1Desc = "Part Number 1 Description"
PartNumber2Desc = "Part Number 2 Description"
PartNumber3Desc = "Part Number 3 Description"
PartNumber4Desc = "Part Number 4 Description"
PartNumber5Desc = "Part Number 5 Description"

PartNumber1Vendor = "Part Number 1 Vendor"
PartNumber2Vendor = "Part Number 2 Vendor"
PartNumber3Vendor = "Part Number 3 Vendor"
PartNumber4Vendor = "Part Number 4 Vendor"
PartNumber5Vendor = "Part Number 5 Vendor"

PartNumber1PH = "Part Number 1 Place Holder"
PartNumber2PH = "Part Number 2 Place Holder"
PartNumber3PH = "Part Number 3 Place Holder"
PartNumber4PH = "Part Number 4 Place Holder"
PartNumber5PH = "Part Number 5 Place Holder"

Now this section works perfectly with no errors

'[ 3-5 are Blank

If Not iProperties.Value("Custom", "Part_Number_1") = "" And Not iProperties.Value("Custom", "Part_Number_2") = "" And iProperties.Value("Custom", "Part_Number_3") = "" And iProperties.Value("Custom", "Part_Number_4") = "" And iProperties.Value("Custom", "Part_Number_5") = "" Then

PartNumber1PH = iProperties.Value("Custom", "Part_Number_1")
PartNumber1PH =PartNumber1PH.Trim().ToUpper()
i = GoExcel.FindRow("P:\_MASTER_PART_LIST\MASTER_PART_LIST.xls", "MASTER_PART_LIST", "Part No.", "=", PartNumber1PH)

End If

If i = -1

	PartNumber1Desc = "Bad Part Number 1"
	PartNumber1Vendor = "Bad Part Number 1"
	PartNumber1PH = "Bad Part Number 1"

Else

	PartNumber1Desc = GoExcel.CurrentRowValue("Description")
	PartNumber1Vendor = GoExcel.CurrentRowValue("Manufacturer")
	
	PartNumber1Desc = PartNumber1Desc.ToUpper()
	PartNumber1Vendor = PartNumber1Vendor.ToUpper()
	
	
End If

If Not iProperties.Value("Custom", "Part_Number_1") = "" And Not iProperties.Value("Custom", "Part_Number_2") = "" And iProperties.Value("Custom", "Part_Number_3") = "" And iProperties.Value("Custom", "Part_Number_4") = "" And iProperties.Value("Custom", "Part_Number_5") = "" Then

PartNumber2PH = iProperties.Value("Custom", "Part_Number_2")
PartNumber2PH =PartNumber2PH.Trim().ToUpper()
i = GoExcel.FindRow("P:\_MASTER_PART_LIST\MASTER_PART_LIST.xls", "MASTER_PART_LIST", "Part No.", "=", PartNumber2PH)

End If

If i = -1

	PartNumber2Desc = "Bad Part Number 2"
	PartNumber2Vendor = "Bad Part Number 2"
	PartNumber2PH = "Bad Part Number 2"

Else

	PartNumber2Desc = GoExcel.CurrentRowValue("Description")
	PartNumber2Vendor = GoExcel.CurrentRowValue("Manufacturer")
	
	PartNumber2Desc = PartNumber2Desc.ToUpper()
	PartNumber2Vendor = PartNumber2Vendor.ToUpper()

End If

If Not iProperties.Value("Custom", "Part_Number_1") = "" And Not iProperties.Value("Custom", "Part_Number_2") = "" And iProperties.Value("Custom", "Part_Number_3") = "" And iProperties.Value("Custom", "Part_Number_4") = "" And iProperties.Value("Custom", "Part_Number_5") = "" Then
 
 iProperties.Value("Project", "Description") = PartNumber1Desc & " / " & PartNumber2Desc
 iProperties.Value("Project", "Vendor") = PartNumber1Vendor & " / " & PartNumber2Vendor
 iProperties.Value("Project", "Part Number") = PartNumber1PH & " / " & PartNumber2PH

End If
']

Then I used that code to make another chunk of code that will run if 3 Custom Iproperties are not blank, the one above runs if 2 are blank

'[ 4-5 are Blank

If Not iProperties.Value("Custom", "Part_Number_1") = "" And Not iProperties.Value("Custom", "Part_Number_2") = "" And Not iProperties.Value("Custom", "Part_Number_3") = "" And iProperties.Value("Custom", "Part_Number_4") = "" And iProperties.Value("Custom", "Part_Number_5") = "" Then

PartNumber1PH = iProperties.Value("Custom", "Part_Number_1")
PartNumber1PH =PartNumber1PH.Trim().ToUpper()
i = GoExcel.FindRow("P:\_MASTER_PART_LIST\MASTER_PART_LIST.xls", "MASTER_PART_LIST", "Part No.", "=", PartNumber1PH)

End If

If i = -1

	PartNumber1Desc = "Bad Part Number 1"
	PartNumber1Vendor = "Bad Part Number 1"
	PartNumber1PH = "Bad Part Number 1"

Else

	PartNumber1Desc = GoExcel.CurrentRowValue("Description")
	PartNumber1Vendor = GoExcel.CurrentRowValue("Manufacturer")
	
	PartNumber1Desc = PartNumber1Desc.ToUpper()
	PartNumber1Vendor = PartNumber1Vendor.ToUpper()
	
	
End If

If Not iProperties.Value("Custom", "Part_Number_1") = "" And Not iProperties.Value("Custom", "Part_Number_2") = "" And Not iProperties.Value("Custom", "Part_Number_3") = "" And iProperties.Value("Custom", "Part_Number_4") = "" And iProperties.Value("Custom", "Part_Number_5") = "" Then

PartNumber2PH = iProperties.Value("Custom", "Part_Number_2")
PartNumber2PH =PartNumber2PH.Trim().ToUpper()
i = GoExcel.FindRow("P:\_MASTER_PART_LIST\MASTER_PART_LIST.xls", "MASTER_PART_LIST", "Part No.", "=", PartNumber2PH)

End If

If i = -1

	PartNumber2Desc = "Bad Part Number 2"
	PartNumber2Vendor = "Bad Part Number 2"
	PartNumber2PH = "Bad Part Number 2"

Else

	PartNumber2Desc = GoExcel.CurrentRowValue("Description")
	PartNumber2Vendor = GoExcel.CurrentRowValue("Manufacturer")
	
	PartNumber2Desc = PartNumber2Desc.ToUpper()
	PartNumber2Vendor = PartNumber2Vendor.ToUpper()

End If

If Not iProperties.Value("Custom", "Part_Number_1") = "" And Not iProperties.Value("Custom", "Part_Number_2") = "" And Not iProperties.Value("Custom", "Part_Number_3") = "" And iProperties.Value("Custom", "Part_Number_4") = "" And iProperties.Value("Custom", "Part_Number_5") = "" Then

PartNumber3PH = iProperties.Value("Custom", "Part_Number_3")
PartNumber3PH =PartNumber3PH.Trim().ToUpper()
i = GoExcel.FindRow("P:\_MASTER_PART_LIST\MASTER_PART_LIST.xls", "MASTER_PART_LIST", "Part No.", "=", PartNumber3PH)

End If

If i = -1

	PartNumber3Desc = "Bad Part Number 3"
	PartNumber3Vendor = "Bad Part Number 3"
	PartNumber3PH = "Bad Part Number 3"

Else

	PartNumber3Desc = GoExcel.CurrentRowValue("Description")
	PartNumber3Vendor = GoExcel.CurrentRowValue("Manufacturer")
	
	PartNumber3Desc = PartNumber3Desc.ToUpper()
	PartNumber3Vendor = PartNumber3Vendor.ToUpper()
	
End If

If Not iProperties.Value("Custom", "Part_Number_1") = "" And Not iProperties.Value("Custom", "Part_Number_2") = "" And Not iProperties.Value("Custom", "Part_Number_3") = "" And iProperties.Value("Custom", "Part_Number_4") = "" And iProperties.Value("Custom", "Part_Number_5") = "" Then
 
 iProperties.Value("Project", "Description") = PartNumber1Desc & " / " & PartNumber2Desc & " / " & PartNumber3Desc
 iProperties.Value("Project", "Vendor") = PartNumber1Vendor & " / " & PartNumber2Vendor & " / " & PartNumber3Vendor
 iProperties.Value("Project", "Part Number") = PartNumber1PH & " / " & PartNumber2PH & " / " & PartNumber3PH

End If
']

but I am getting a "Object variable or With block variable not set", I don't understand i made it from a working piece of code and it just adds another custom iproperty to the code but mechanically does the same thing as the working rule

Am i missing something really stupid here?

J-Camper · ‎11-02-2020

This is hard to troubleshoot without your existing data set. If you can post more of the code and/or any similar files, it would make troubleshooting easier. DO NOT POST ANY CONFIDENTIAL FILES!!

However, if you sprinkle messageboxs throughout the codes like this:

'your code does tasks

MessageBox.Show("Code executes to here.", "Debug 1")

'Your code does more tasks

MessageBox.Show("Code executes to here.", "Debug 2")

'Your code does more tasks

MessageBox.Show("Code executes to here.", "Debug 3")

You should be able to narrow down the line which is throwing the error.

AMN3161 · ‎11-02-2020

Here is the entire rule, i had it build all of the iproperties it needs so it can be put onto a blank part and work.

Dim oDoc As Document
oDoc = ThisDoc.Document

Dim MyArrayList As New ArrayList 
MyArrayList.Add("Part_Number_1") 
MyArrayList.Add("Part_Number_2") 
MyArrayList.Add("Part_Number_3") 
MyArrayList.Add("Part_Number_4") 
MyArrayList.Add("Part_Number_5") 

oCustomPropertySet = oDoc.PropertySets.Item("Inventor User Defined Properties")

PartNumber1Desc = "Part Number 1 Description"
PartNumber2Desc = "Part Number 2 Description"
PartNumber3Desc = "Part Number 3 Description"
PartNumber4Desc = "Part Number 4 Description"
PartNumber5Desc = "Part Number 5 Description"

PartNumber1Vendor = "Part Number 1 Vendor"
PartNumber2Vendor = "Part Number 2 Vendor"
PartNumber3Vendor = "Part Number 3 Vendor"
PartNumber4Vendor = "Part Number 4 Vendor"
PartNumber5Vendor = "Part Number 5 Vendor"

PartNumber1PH = "Part Number 1 Place Holder"
PartNumber2PH = "Part Number 2 Place Holder"
PartNumber3PH = "Part Number 3 Place Holder"
PartNumber4PH = "Part Number 4 Place Holder"
PartNumber5PH = "Part Number 5 Place Holder"


'[ Adds custom properties
For Each oItem In MyArrayList
	oFlag = False 'set flag to default value
	'look at each property in the collection 
	For Each oCustProp In oCustomPropertySet
		'check property name against the list item
		If oItem = oCustProp.Name Then 
			oFlag = True 'trip the flag
		End If 
	Next
	
	If oFlag = False Then
		'Create new iProperty
		iProperties.Value(oDoc, "Custom", oItem ) = ""
	End If
Next

']

'[ Erases custom properties

For Each oCustProp In oCustomPropertySet
	oFlag = False 'set flag to default value
	For Each oItem In MyArrayList
		'check property name against the list item
		If oCustProp.Name = oItem Then 
			oFlag = True 'trip the flag
		End If 
	Next
	
	If oFlag = False Then
		'delete property
		oCustProp.Delete
	End If
Next

']


'[ 1-5 are Blank

If iProperties.Value("Custom", "Part_Number_1") = "" And iProperties.Value("Custom", "Part_Number_2") = "" And iProperties.Value("Custom", "Part_Number_3") = "" And iProperties.Value("Custom", "Part_Number_4") = "" And iProperties.Value("Custom", "Part_Number_5") = "" Then

Dim partNumber As String = iProperties.Value("Project", "Part Number")
partNumber = partNumber.Trim().ToUpper()
i = GoExcel.FindRow("P:\_MASTER_PART_LIST\MASTER_PART_LIST.xls", "MASTER_PART_LIST", "Part No.", "=", partNumber)

End If

If i = -1

	iProperties.Value("Project", "Description") = "The entered Part Number does not exist in the Master Parts List"
	iProperties.Value("Project", "Vendor") = "The entered Part Number does not exist in the Master Parts List"
	
Else

	iProperties.Value("Project", "Description") = GoExcel.CurrentRowValue("Description")
	iProperties.Value("Project", "Vendor") = GoExcel.CurrentRowValue("Manufacturer")
	
	Dim partDescription As String = iProperties.Value("Project", "Description")
	partDescription = partDescription.ToUpper()
	iProperties.Value("Project", "Description")= partDescription

	Dim partVendor As String = iProperties.Value("Project", "Vendor")
	partVendor = partVendor.ToUpper()
	iProperties.Value("Project", "Vendor") = partVendor
	

End If

']

'[ 2-5 are Blank

If Not iProperties.Value("Custom", "Part_Number_1") = "" And iProperties.Value("Custom", "Part_Number_2") = "" And iProperties.Value("Custom", "Part_Number_3") = "" And iProperties.Value("Custom", "Part_Number_4") = "" And iProperties.Value("Custom", "Part_Number_5") = "" Then

PartNumber1PH = iProperties.Value("Custom", "Part_Number_1")
PartNumber1PH = PartNumber1PH.Trim().ToUpper()
i = GoExcel.FindRow("P:\_MASTER_PART_LIST\MASTER_PART_LIST.xls", "MASTER_PART_LIST", "Part No.", "=", PartNumber1PH)

End If

If i = -1

	iProperties.Value("Project", "Description") = "The entered Part Number does not exist in the Master Parts List"
	iProperties.Value("Project", "Vendor") = "The entered Part Number does not exist in the Master Parts List"
	
Else

	iProperties.Value("Project", "Description") = GoExcel.CurrentRowValue("Description")
	iProperties.Value("Project", "Vendor") = GoExcel.CurrentRowValue("Manufacturer")
	
	PartNumber1Desc= iProperties.Value("Project", "Description")
	PartNumber1Desc = PartNumber1Desc.ToUpper()
	iProperties.Value("Project", "Description")= PartNumber1Desc

	PartNumber1Vendor = iProperties.Value("Project", "Vendor")
	PartNumber1Vendor = PartNumber1Vendor.ToUpper()
	iProperties.Value("Project", "Vendor")= PartNumber1Vendor

End If

']

'[ 3-5 are Blank

If Not iProperties.Value("Custom", "Part_Number_1") = "" And Not iProperties.Value("Custom", "Part_Number_2") = "" And iProperties.Value("Custom", "Part_Number_3") = "" And iProperties.Value("Custom", "Part_Number_4") = "" And iProperties.Value("Custom", "Part_Number_5") = "" Then

PartNumber1PH = iProperties.Value("Custom", "Part_Number_1")
PartNumber1PH =PartNumber1PH.Trim().ToUpper()
i = GoExcel.FindRow("P:\_MASTER_PART_LIST\MASTER_PART_LIST.xls", "MASTER_PART_LIST", "Part No.", "=", PartNumber1PH)

End If

If i = -1

	PartNumber1Desc = "Bad Part Number 1"
	PartNumber1Vendor = "Bad Part Number 1"
	PartNumber1PH = "Bad Part Number 1"

Else

	PartNumber1Desc = GoExcel.CurrentRowValue("Description")
	PartNumber1Vendor = GoExcel.CurrentRowValue("Manufacturer")
	
	PartNumber1Desc = PartNumber1Desc.ToUpper()
	PartNumber1Vendor = PartNumber1Vendor.ToUpper()
	
	
End If

If Not iProperties.Value("Custom", "Part_Number_1") = "" And Not iProperties.Value("Custom", "Part_Number_2") = "" And iProperties.Value("Custom", "Part_Number_3") = "" And iProperties.Value("Custom", "Part_Number_4") = "" And iProperties.Value("Custom", "Part_Number_5") = "" Then

PartNumber2PH = iProperties.Value("Custom", "Part_Number_2")
PartNumber2PH =PartNumber2PH.Trim().ToUpper()
i = GoExcel.FindRow("P:\_MASTER_PART_LIST\MASTER_PART_LIST.xls", "MASTER_PART_LIST", "Part No.", "=", PartNumber2PH)

End If

If i = -1

	PartNumber2Desc = "Bad Part Number 2"
	PartNumber2Vendor = "Bad Part Number 2"
	PartNumber2PH = "Bad Part Number 2"

Else

	PartNumber2Desc = GoExcel.CurrentRowValue("Description")
	PartNumber2Vendor = GoExcel.CurrentRowValue("Manufacturer")
	
	PartNumber2Desc = PartNumber2Desc.ToUpper()
	PartNumber2Vendor = PartNumber2Vendor.ToUpper()

End If

If Not iProperties.Value("Custom", "Part_Number_1") = "" And Not iProperties.Value("Custom", "Part_Number_2") = "" And iProperties.Value("Custom", "Part_Number_3") = "" And iProperties.Value("Custom", "Part_Number_4") = "" And iProperties.Value("Custom", "Part_Number_5") = "" Then
 
 iProperties.Value("Project", "Description") = PartNumber1Desc & " / " & PartNumber2Desc
 iProperties.Value("Project", "Vendor") = PartNumber1Vendor & " / " & PartNumber2Vendor
 iProperties.Value("Project", "Part Number") = PartNumber1PH & " / " & PartNumber2PH

End If
']

'[ 4-5 are Blank

If Not iProperties.Value("Custom", "Part_Number_1") = "" And Not iProperties.Value("Custom", "Part_Number_2") = "" And Not iProperties.Value("Custom", "Part_Number_3") = "" And iProperties.Value("Custom", "Part_Number_4") = "" And iProperties.Value("Custom", "Part_Number_5") = "" Then

PartNumber1PH = iProperties.Value("Custom", "Part_Number_1")
PartNumber1PH =PartNumber1PH.Trim().ToUpper()
i = GoExcel.FindRow("P:\_MASTER_PART_LIST\MASTER_PART_LIST.xls", "MASTER_PART_LIST", "Part No.", "=", PartNumber1PH)

End If

If i = -1

	PartNumber1Desc = "Bad Part Number 1"
	PartNumber1Vendor = "Bad Part Number 1"
	PartNumber1PH = "Bad Part Number 1"

Else

	PartNumber1Desc = GoExcel.CurrentRowValue("Description")
	PartNumber1Vendor = GoExcel.CurrentRowValue("Manufacturer")
	
	PartNumber1Desc = PartNumber1Desc.ToUpper()
	PartNumber1Vendor = PartNumber1Vendor.ToUpper()
	
	
End If

If Not iProperties.Value("Custom", "Part_Number_1") = "" And Not iProperties.Value("Custom", "Part_Number_2") = "" And Not iProperties.Value("Custom", "Part_Number_3") = "" And iProperties.Value("Custom", "Part_Number_4") = "" And iProperties.Value("Custom", "Part_Number_5") = "" Then

PartNumber2PH = iProperties.Value("Custom", "Part_Number_2")
PartNumber2PH =PartNumber2PH.Trim().ToUpper()
i = GoExcel.FindRow("P:\_MASTER_PART_LIST\MASTER_PART_LIST.xls", "MASTER_PART_LIST", "Part No.", "=", PartNumber2PH)

End If

If i = -1

	PartNumber2Desc = "Bad Part Number 2"
	PartNumber2Vendor = "Bad Part Number 2"
	PartNumber2PH = "Bad Part Number 2"

Else

	PartNumber2Desc = GoExcel.CurrentRowValue("Description")
	PartNumber2Vendor = GoExcel.CurrentRowValue("Manufacturer")
	
	PartNumber2Desc = PartNumber2Desc.ToUpper()
	PartNumber2Vendor = PartNumber2Vendor.ToUpper()

End If

If Not iProperties.Value("Custom", "Part_Number_1") = "" And Not iProperties.Value("Custom", "Part_Number_2") = "" And Not iProperties.Value("Custom", "Part_Number_3") = "" And iProperties.Value("Custom", "Part_Number_4") = "" And iProperties.Value("Custom", "Part_Number_5") = "" Then

PartNumber3PH = iProperties.Value("Custom", "Part_Number_3")
PartNumber3PH =PartNumber3PH.Trim().ToUpper()
i = GoExcel.FindRow("P:\_MASTER_PART_LIST\MASTER_PART_LIST.xls", "MASTER_PART_LIST", "Part No.", "=", PartNumber3PH)

End If

If i = -1

	PartNumber3Desc = "Bad Part Number 3"
	PartNumber3Vendor = "Bad Part Number 3"
	PartNumber3PH = "Bad Part Number 3"

Else

	PartNumber3Desc = GoExcel.CurrentRowValue("Description")
	PartNumber3Vendor = GoExcel.CurrentRowValue("Manufacturer")
	
	PartNumber3Desc = PartNumber3Desc.ToUpper()
	PartNumber3Vendor = PartNumber3Vendor.ToUpper()
	
End If

If Not iProperties.Value("Custom", "Part_Number_1") = "" And Not iProperties.Value("Custom", "Part_Number_2") = "" And Not iProperties.Value("Custom", "Part_Number_3") = "" And iProperties.Value("Custom", "Part_Number_4") = "" And iProperties.Value("Custom", "Part_Number_5") = "" Then
 
 iProperties.Value("Project", "Description") = PartNumber1Desc & " / " & PartNumber2Desc & " / " & PartNumber3Desc
 iProperties.Value("Project", "Vendor") = PartNumber1Vendor & " / " & PartNumber2Vendor & " / " & PartNumber3Vendor
 iProperties.Value("Project", "Part Number") = PartNumber1PH & " / " & PartNumber2PH & " / " & PartNumber3PH

End If
']

AMN3161 · ‎11-02-2020

but let me try that message box idea real fast

AMN3161 · ‎11-02-2020

Ok from what i can tell is that the portion that i last added works in isolation, so i starting cutting portions out of my code to see where the interference is and this is what i narrowed it down to

'[ 4-5 are Blank

If Not iProperties.Value("Custom", "Part_Number_1") = "" And Not iProperties.Value("Custom", "Part_Number_2") = "" And Not iProperties.Value("Custom", "Part_Number_3") = "" And iProperties.Value("Custom", "Part_Number_4") = "" And iProperties.Value("Custom", "Part_Number_5") = "" Then

PartNumber1PH = iProperties.Value("Custom", "Part_Number_1")
PartNumber1PH =PartNumber1PH.Trim().ToUpper()
i = GoExcel.FindRow("P:\_MASTER_PART_LIST\MASTER_PART_LIST.xls", "MASTER_PART_LIST", "Part No.", "=", PartNumber1PH)

End If

If i = -1

	PartNumber1Desc = "Bad Part Number 1"
	PartNumber1Vendor = "Bad Part Number 1"
	PartNumber1PH = "Bad Part Number 1"

Else

	PartNumber1Desc = GoExcel.CurrentRowValue("Description")
	PartNumber1Vendor = GoExcel.CurrentRowValue("Manufacturer")
	
	PartNumber1Desc = PartNumber1Desc.ToUpper()
	PartNumber1Vendor = PartNumber1Vendor.ToUpper()
	
	
End If

If Not iProperties.Value("Custom", "Part_Number_1") = "" And Not iProperties.Value("Custom", "Part_Number_2") = "" And Not iProperties.Value("Custom", "Part_Number_3") = "" And iProperties.Value("Custom", "Part_Number_4") = "" And iProperties.Value("Custom", "Part_Number_5") = "" Then

PartNumber2PH = iProperties.Value("Custom", "Part_Number_2")
PartNumber2PH =PartNumber2PH.Trim().ToUpper()
i = GoExcel.FindRow("P:\_MASTER_PART_LIST\MASTER_PART_LIST.xls", "MASTER_PART_LIST", "Part No.", "=", PartNumber2PH)

End If

If i = -1

	PartNumber2Desc = "Bad Part Number 2"
	PartNumber2Vendor = "Bad Part Number 2"
	PartNumber2PH = "Bad Part Number 2"

Else

	PartNumber2Desc = GoExcel.CurrentRowValue("Description")
	PartNumber2Vendor = GoExcel.CurrentRowValue("Manufacturer")
	
	PartNumber2Desc = PartNumber2Desc.ToUpper()
	PartNumber2Vendor = PartNumber2Vendor.ToUpper()

End If

If Not iProperties.Value("Custom", "Part_Number_1") = "" And Not iProperties.Value("Custom", "Part_Number_2") = "" And Not iProperties.Value("Custom", "Part_Number_3") = "" And iProperties.Value("Custom", "Part_Number_4") = "" And iProperties.Value("Custom", "Part_Number_5") = "" Then

PartNumber3PH = iProperties.Value("Custom", "Part_Number_3")
PartNumber3PH =PartNumber3PH.Trim().ToUpper()
i = GoExcel.FindRow("P:\_MASTER_PART_LIST\MASTER_PART_LIST.xls", "MASTER_PART_LIST", "Part No.", "=", PartNumber3PH)

End If

If i = -1

	PartNumber3Desc = "Bad Part Number 3"
	PartNumber3Vendor = "Bad Part Number 3"
	PartNumber3PH = "Bad Part Number 3"

Else

	PartNumber3Desc = GoExcel.CurrentRowValue("Description")
	PartNumber3Vendor = GoExcel.CurrentRowValue("Manufacturer")
	
	PartNumber3Desc = PartNumber3Desc.ToUpper()
	PartNumber3Vendor = PartNumber3Vendor.ToUpper()
	
End If

If Not iProperties.Value("Custom", "Part_Number_1") = "" And Not iProperties.Value("Custom", "Part_Number_2") = "" And Not iProperties.Value("Custom", "Part_Number_3") = "" And iProperties.Value("Custom", "Part_Number_4") = "" And iProperties.Value("Custom", "Part_Number_5") = "" Then
 
 iProperties.Value("Project", "Description") = PartNumber1Desc & " / " & PartNumber2Desc & " / " & PartNumber3Desc
 iProperties.Value("Project", "Vendor") = PartNumber1Vendor & " / " & PartNumber2Vendor & " / " & PartNumber3Vendor
 iProperties.Value("Project", "Part Number") = PartNumber1PH & " / " & PartNumber2PH & " / " & PartNumber3PH

End If
']

This is my new portion above and it works in isolation, if I remove this (below) out of my code existing code, then the portion (above) works

'[ 3-5 are Blank

If Not iProperties.Value("Custom", "Part_Number_1") = "" And Not iProperties.Value("Custom", "Part_Number_2") = "" And iProperties.Value("Custom", "Part_Number_3") = "" And iProperties.Value("Custom", "Part_Number_4") = "" And iProperties.Value("Custom", "Part_Number_5") = "" Then

PartNumber1PH = iProperties.Value("Custom", "Part_Number_1")
PartNumber1PH =PartNumber1PH.Trim().ToUpper()
i = GoExcel.FindRow("P:\_MASTER_PART_LIST\MASTER_PART_LIST.xls", "MASTER_PART_LIST", "Part No.", "=", PartNumber1PH)

End If

If i = -1

	PartNumber1Desc = "Bad Part Number 1"
	PartNumber1Vendor = "Bad Part Number 1"
	PartNumber1PH = "Bad Part Number 1"

Else

	PartNumber1Desc = GoExcel.CurrentRowValue("Description")
	PartNumber1Vendor = GoExcel.CurrentRowValue("Manufacturer")
	
	PartNumber1Desc = PartNumber1Desc.ToUpper()
	PartNumber1Vendor = PartNumber1Vendor.ToUpper()
	
	
End If

If Not iProperties.Value("Custom", "Part_Number_1") = "" And Not iProperties.Value("Custom", "Part_Number_2") = "" And iProperties.Value("Custom", "Part_Number_3") = "" And iProperties.Value("Custom", "Part_Number_4") = "" And iProperties.Value("Custom", "Part_Number_5") = "" Then

PartNumber2PH = iProperties.Value("Custom", "Part_Number_2")
PartNumber2PH =PartNumber2PH.Trim().ToUpper()
i = GoExcel.FindRow("P:\_MASTER_PART_LIST\MASTER_PART_LIST.xls", "MASTER_PART_LIST", "Part No.", "=", PartNumber2PH)

End If

If i = -1

	PartNumber2Desc = "Bad Part Number 2"
	PartNumber2Vendor = "Bad Part Number 2"
	PartNumber2PH = "Bad Part Number 2"

Else

	PartNumber2Desc = GoExcel.CurrentRowValue("Description")
	PartNumber2Vendor = GoExcel.CurrentRowValue("Manufacturer")
	
	PartNumber2Desc = PartNumber2Desc.ToUpper()
	PartNumber2Vendor = PartNumber2Vendor.ToUpper()

End If

If Not iProperties.Value("Custom", "Part_Number_1") = "" And Not iProperties.Value("Custom", "Part_Number_2") = "" And iProperties.Value("Custom", "Part_Number_3") = "" And iProperties.Value("Custom", "Part_Number_4") = "" And iProperties.Value("Custom", "Part_Number_5") = "" Then
 
 iProperties.Value("Project", "Description") = PartNumber1Desc & " / " & PartNumber2Desc
 iProperties.Value("Project", "Vendor") = PartNumber1Vendor & " / " & PartNumber2Vendor
 iProperties.Value("Project", "Part Number") = PartNumber1PH & " / " & PartNumber2PH

End If
']

So basically my "4-5 are Blank" portion will work in isolation and will also work only if "3-5 are Blank" is removed from the rule. There is something between those two portions that are not happy with each other. But "3-5 are Blank" will work fine when "4-5 are Blank" is in there, just not vice versa

AMN3161 · ‎11-03-2020

Ok so if I switch order of "4-5 are Blank" and "3-5 are Blank" in the rule, then "4-5 are Blank" works and "3-5 are Blank doesn't now

I don't get what can be causing this problem

J-Camper · ‎11-03-2020

I think it might work better if you tie the (1-5 are blank), (2-5 are blank), etc. into a single If, Else If Statement. I made that change, so try this:

Dim oDoc As Document
oDoc = ThisDoc.Document

Dim MyArrayList As New ArrayList 
MyArrayList.Add("Part_Number_1") 
MyArrayList.Add("Part_Number_2") 
MyArrayList.Add("Part_Number_3") 
MyArrayList.Add("Part_Number_4") 
MyArrayList.Add("Part_Number_5") 

oCustomPropertySet = oDoc.PropertySets.Item("Inventor User Defined Properties")

PartNumber1Desc = "Part Number 1 Description"
PartNumber2Desc = "Part Number 2 Description"
PartNumber3Desc = "Part Number 3 Description"
PartNumber4Desc = "Part Number 4 Description"
PartNumber5Desc = "Part Number 5 Description"

PartNumber1Vendor = "Part Number 1 Vendor"
PartNumber2Vendor = "Part Number 2 Vendor"
PartNumber3Vendor = "Part Number 3 Vendor"
PartNumber4Vendor = "Part Number 4 Vendor"
PartNumber5Vendor = "Part Number 5 Vendor"

PartNumber1PH = "Part Number 1 Place Holder"
PartNumber2PH = "Part Number 2 Place Holder"
PartNumber3PH = "Part Number 3 Place Holder"
PartNumber4PH = "Part Number 4 Place Holder"
PartNumber5PH = "Part Number 5 Place Holder"


'[ Adds custom properties
For Each oItem In MyArrayList
	oFlag = False 'set flag to default value
	'look at each property in the collection 
	For Each oCustProp In oCustomPropertySet
		'check property name against the list item
		If oItem = oCustProp.Name Then 
			oFlag = True 'trip the flag
		End If 
	Next
	
	If oFlag = False Then
		'Create new iProperty
		iProperties.Value(oDoc, "Custom", oItem ) = ""
	End If
Next

']

'[ Erases custom properties

For Each oCustProp In oCustomPropertySet
	oFlag = False 'set flag to default value
	For Each oItem In MyArrayList
		'check property name against the list item
		If oCustProp.Name = oItem Then 
			oFlag = True 'trip the flag
		End If 
	Next
	
	If oFlag = False Then
		'delete property
		oCustProp.Delete
	End If
Next

']


'[ 1-5 are Blank

If iProperties.Value("Custom", "Part_Number_1") = "" And iProperties.Value("Custom", "Part_Number_2") = "" And iProperties.Value("Custom", "Part_Number_3") = "" And iProperties.Value("Custom", "Part_Number_4") = "" And iProperties.Value("Custom", "Part_Number_5") = "" Then

Dim partNumber As String = iProperties.Value("Project", "Part Number")
partNumber = partNumber.Trim().ToUpper()
i = GoExcel.FindRow("P:\_MASTER_PART_LIST\MASTER_PART_LIST.xls", "MASTER_PART_LIST", "Part No.", "=", partNumber)

'End If

If i = -1

	iProperties.Value("Project", "Description") = "The entered Part Number does not exist in the Master Parts List"
	iProperties.Value("Project", "Vendor") = "The entered Part Number does not exist in the Master Parts List"
	
Else

	iProperties.Value("Project", "Description") = GoExcel.CurrentRowValue("Description")
	iProperties.Value("Project", "Vendor") = GoExcel.CurrentRowValue("Manufacturer")
	
	Dim partDescription As String = iProperties.Value("Project", "Description")
	partDescription = partDescription.ToUpper()
	iProperties.Value("Project", "Description")= partDescription

	Dim partVendor As String = iProperties.Value("Project", "Vendor")
	partVendor = partVendor.ToUpper()
	iProperties.Value("Project", "Vendor") = partVendor
	

End If


']

'[ 2-5 are Blank

Else If Not iProperties.Value("Custom", "Part_Number_1") = "" And iProperties.Value("Custom", "Part_Number_2") = "" And iProperties.Value("Custom", "Part_Number_3") = "" And iProperties.Value("Custom", "Part_Number_4") = "" And iProperties.Value("Custom", "Part_Number_5") = "" Then

PartNumber1PH = iProperties.Value("Custom", "Part_Number_1")
PartNumber1PH = PartNumber1PH.Trim().ToUpper()
i = GoExcel.FindRow("P:\_MASTER_PART_LIST\MASTER_PART_LIST.xls", "MASTER_PART_LIST", "Part No.", "=", PartNumber1PH)

'End If

If i = -1

	iProperties.Value("Project", "Description") = "The entered Part Number does not exist in the Master Parts List"
	iProperties.Value("Project", "Vendor") = "The entered Part Number does not exist in the Master Parts List"
	
Else

	iProperties.Value("Project", "Description") = GoExcel.CurrentRowValue("Description")
	iProperties.Value("Project", "Vendor") = GoExcel.CurrentRowValue("Manufacturer")
	
	PartNumber1Desc= iProperties.Value("Project", "Description")
	PartNumber1Desc = PartNumber1Desc.ToUpper()
	iProperties.Value("Project", "Description")= PartNumber1Desc

	PartNumber1Vendor = iProperties.Value("Project", "Vendor")
	PartNumber1Vendor = PartNumber1Vendor.ToUpper()
	iProperties.Value("Project", "Vendor")= PartNumber1Vendor

End If

']

'[ 3-5 are Blank

Else If Not iProperties.Value("Custom", "Part_Number_1") = "" And Not iProperties.Value("Custom", "Part_Number_2") = "" And iProperties.Value("Custom", "Part_Number_3") = "" And iProperties.Value("Custom", "Part_Number_4") = "" And iProperties.Value("Custom", "Part_Number_5") = "" Then

PartNumber1PH = iProperties.Value("Custom", "Part_Number_1")
PartNumber1PH =PartNumber1PH.Trim().ToUpper()
i = GoExcel.FindRow("P:\_MASTER_PART_LIST\MASTER_PART_LIST.xls", "MASTER_PART_LIST", "Part No.", "=", PartNumber1PH)

'End If

If i = -1

	PartNumber1Desc = "Bad Part Number 1"
	PartNumber1Vendor = "Bad Part Number 1"
	PartNumber1PH = "Bad Part Number 1"

Else

	PartNumber1Desc = GoExcel.CurrentRowValue("Description")
	PartNumber1Vendor = GoExcel.CurrentRowValue("Manufacturer")
	
	PartNumber1Desc = PartNumber1Desc.ToUpper()
	PartNumber1Vendor = PartNumber1Vendor.ToUpper()
	
	
End If

'If Not iProperties.Value("Custom", "Part_Number_1") = "" And Not iProperties.Value("Custom", "Part_Number_2") = "" And iProperties.Value("Custom", "Part_Number_3") = "" And iProperties.Value("Custom", "Part_Number_4") = "" And iProperties.Value("Custom", "Part_Number_5") = "" Then

PartNumber2PH = iProperties.Value("Custom", "Part_Number_2")
PartNumber2PH =PartNumber2PH.Trim().ToUpper()
i = GoExcel.FindRow("P:\_MASTER_PART_LIST\MASTER_PART_LIST.xls", "MASTER_PART_LIST", "Part No.", "=", PartNumber2PH)

'End If

If i = -1

	PartNumber2Desc = "Bad Part Number 2"
	PartNumber2Vendor = "Bad Part Number 2"
	PartNumber2PH = "Bad Part Number 2"

Else

	PartNumber2Desc = GoExcel.CurrentRowValue("Description")
	PartNumber2Vendor = GoExcel.CurrentRowValue("Manufacturer")
	
	PartNumber2Desc = PartNumber2Desc.ToUpper()
	PartNumber2Vendor = PartNumber2Vendor.ToUpper()

End If

'If Not iProperties.Value("Custom", "Part_Number_1") = "" And Not iProperties.Value("Custom", "Part_Number_2") = "" And iProperties.Value("Custom", "Part_Number_3") = "" And iProperties.Value("Custom", "Part_Number_4") = "" And iProperties.Value("Custom", "Part_Number_5") = "" Then
 
 iProperties.Value("Project", "Description") = PartNumber1Desc & " / " & PartNumber2Desc
 iProperties.Value("Project", "Vendor") = PartNumber1Vendor & " / " & PartNumber2Vendor
 iProperties.Value("Project", "Part Number") = PartNumber1PH & " / " & PartNumber2PH

'End If
']

'[ 4-5 are Blank

Else If Not iProperties.Value("Custom", "Part_Number_1") = "" And Not iProperties.Value("Custom", "Part_Number_2") = "" And Not iProperties.Value("Custom", "Part_Number_3") = "" And iProperties.Value("Custom", "Part_Number_4") = "" And iProperties.Value("Custom", "Part_Number_5") = "" Then

PartNumber1PH = iProperties.Value("Custom", "Part_Number_1")
PartNumber1PH =PartNumber1PH.Trim().ToUpper()
i = GoExcel.FindRow("P:\_MASTER_PART_LIST\MASTER_PART_LIST.xls", "MASTER_PART_LIST", "Part No.", "=", PartNumber1PH)

'End If

If i = -1

	PartNumber1Desc = "Bad Part Number 1"
	PartNumber1Vendor = "Bad Part Number 1"
	PartNumber1PH = "Bad Part Number 1"

Else

	PartNumber1Desc = GoExcel.CurrentRowValue("Description")
	PartNumber1Vendor = GoExcel.CurrentRowValue("Manufacturer")
	
	PartNumber1Desc = PartNumber1Desc.ToUpper()
	PartNumber1Vendor = PartNumber1Vendor.ToUpper()
	
	
End If

'If Not iProperties.Value("Custom", "Part_Number_1") = "" And Not iProperties.Value("Custom", "Part_Number_2") = "" And Not iProperties.Value("Custom", "Part_Number_3") = "" And iProperties.Value("Custom", "Part_Number_4") = "" And iProperties.Value("Custom", "Part_Number_5") = "" Then

PartNumber2PH = iProperties.Value("Custom", "Part_Number_2")
PartNumber2PH =PartNumber2PH.Trim().ToUpper()
i = GoExcel.FindRow("P:\_MASTER_PART_LIST\MASTER_PART_LIST.xls", "MASTER_PART_LIST", "Part No.", "=", PartNumber2PH)

'End If

If i = -1

	PartNumber2Desc = "Bad Part Number 2"
	PartNumber2Vendor = "Bad Part Number 2"
	PartNumber2PH = "Bad Part Number 2"

Else

	PartNumber2Desc = GoExcel.CurrentRowValue("Description")
	PartNumber2Vendor = GoExcel.CurrentRowValue("Manufacturer")
	
	PartNumber2Desc = PartNumber2Desc.ToUpper()
	PartNumber2Vendor = PartNumber2Vendor.ToUpper()

End If

'If Not iProperties.Value("Custom", "Part_Number_1") = "" And Not iProperties.Value("Custom", "Part_Number_2") = "" And Not iProperties.Value("Custom", "Part_Number_3") = "" And iProperties.Value("Custom", "Part_Number_4") = "" And iProperties.Value("Custom", "Part_Number_5") = "" Then

PartNumber3PH = iProperties.Value("Custom", "Part_Number_3")
PartNumber3PH =PartNumber3PH.Trim().ToUpper()
i = GoExcel.FindRow("P:\_MASTER_PART_LIST\MASTER_PART_LIST.xls", "MASTER_PART_LIST", "Part No.", "=", PartNumber3PH)

'End If

If i = -1

	PartNumber3Desc = "Bad Part Number 3"
	PartNumber3Vendor = "Bad Part Number 3"
	PartNumber3PH = "Bad Part Number 3"

Else

	PartNumber3Desc = GoExcel.CurrentRowValue("Description")
	PartNumber3Vendor = GoExcel.CurrentRowValue("Manufacturer")
	
	PartNumber3Desc = PartNumber3Desc.ToUpper()
	PartNumber3Vendor = PartNumber3Vendor.ToUpper()
	
End If

'If Not iProperties.Value("Custom", "Part_Number_1") = "" And Not iProperties.Value("Custom", "Part_Number_2") = "" And Not iProperties.Value("Custom", "Part_Number_3") = "" And iProperties.Value("Custom", "Part_Number_4") = "" And iProperties.Value("Custom", "Part_Number_5") = "" Then
 
 iProperties.Value("Project", "Description") = PartNumber1Desc & " / " & PartNumber2Desc & " / " & PartNumber3Desc
 iProperties.Value("Project", "Vendor") = PartNumber1Vendor & " / " & PartNumber2Vendor & " / " & PartNumber3Vendor
 iProperties.Value("Project", "Part Number") = PartNumber1PH & " / " & PartNumber2PH & " / " & PartNumber3PH

End If
']

I know you mentioned the code is chunky because you are new to coding, but if you would like I could condense this into something more streamlined. I would be sure to leave plenty of comments which might make more sense once you see it, but I don't want to waste my time if you want to stick with what you are comfortable with.

AMN3161 · ‎11-03-2020

No please, i would love to know how to make it more streamline. I have much more to add to this and i would love a method to make this more condensed.

Its a breath of fresh air when I learned you can do this '[ '] to make collapsible segments of code

J-Camper · ‎11-03-2020

Okay, I will spend some time later today on it and get back to you.

J-Camper · ‎11-03-2020

I've been tinkering and this is what i have:

Sub Main 'Where main code lives
	Dim oDoc As Document = ThisDoc.Document
		
	Dim MyArrayList As New List(Of String) 
	MyArrayList.AddRange({"Part_Number_1", "Part_Number_2", "Part_Number_3", "Part_Number_4", "Part_Number_5"}) '{Brackets Create Range. Seperated by commas} 
	
	oCustomPropertySet = oDoc.PropertySets.Item("Inventor User Defined Properties")
	
	'[Adds custom properties
	For Each oItem In MyArrayList
		oFlag = False 'set flag to default value
		'look at each property in the collection 
		For Each oCustProp In oCustomPropertySet
			'check property name against the list item
			If oItem = oCustProp.Name Then 
				oFlag = True 'trip the flag
			End If 
		Next
		
		If oFlag = False Then
			'Create new iProperty
			iProperties.Value(oDoc, "Custom", oItem) = ""
		End If
	Next
	']

	'[Erases custom properties
	'I don't really recomend deleting every Custom iProperty in case you want to use more later down the line
	For Each oCustProp In oCustomPropertySet
		oFlag = False 'set flag to default value
		For Each oItem In MyArrayList
			'check property name against the list item
			If oCustProp.Name = oItem Then 
				oFlag = True 'trip the flag
			End If 
		Next
		
		If oFlag = False Then
			'delete property
			oCustProp.Delete
		End If
	Next
	']
	
	'[Determine which Part Numbers Are filled in:
	Dim ActiveItems As New List(Of Boolean) 'First item is i = 0 and last item is i = List.Count - 1
	ActiveItems.AddRange({True, True, True, True, True})
	Dim ActiveCount As Integer = 0
	For j As Integer = 0 To MyArrayList.Count - 1
		If oCustomPropertySet.Item(MyArrayList.Item(j)).Value = ""
			ActiveItems.Item(j) = False
		Else
			ActiveCount += 1
		End If
	Next
	']
	
	'[Set Part Number, Description, Vendor
	Dim partNumber As String = ""
	'I've always had a hard time getting "GoExcel.FindRow" to work in the past, but if it is working then I won't change it.  I just wasn't able to test the GoExcel commands
	If ActiveCount = 0 'All are empty
		partNumber = iProperties.Value("Project", "Part Number").ToString.Trim().ToUpper()
		i = GoExcel.FindRow("P:\_MASTER_PART_LIST\MASTER_PART_LIST.xls", "MASTER_PART_LIST", "Part No.", "=", partNumber)
		If i = -1
			iProperties.Value("Project", "Description") = "The entered Part Number does not exist in the Master Parts List"
			iProperties.Value("Project", "Vendor") = "The entered Part Number does not exist in the Master Parts List"
		Else
			iProperties.Value("Project", "Description") = GoExcel.CurrentRowValue("Description").ToString.ToUpper()
			iProperties.Value("Project", "Vendor") = GoExcel.CurrentRowValue("Manufacturer").ToString.ToUpper()
		End If
	
	Else If ActiveCount = 1 'Only 1 is not empty
		partNumber = iProperties.Value("Custom", MyArrayList.Item(ActiveItems.IndexOf(True))).ToString.Trim().ToUpper()
		i = GoExcel.FindRow("P:\_MASTER_PART_LIST\MASTER_PART_LIST.xls", "MASTER_PART_LIST", "Part No.", "=", partNumber)
		If i = -1
			iProperties.Value("Project", "Description") = "The entered Part Number does not exist in the Master Parts List"
			iProperties.Value("Project", "Vendor") = "The entered Part Number does not exist in the Master Parts List"
		Else
			iProperties.Value("Project", "Description") = GoExcel.CurrentRowValue("Description").ToString.ToUpper()
			iProperties.Value("Project", "Vendor") = GoExcel.CurrentRowValue("Manufacturer").ToString.ToUpper()
		End If
	
	Else 'More than 1 is not empty
		'Setup Combo Strings
		Dim BuildPartNumber As String = ""
		Dim BuildDescription As String = ""
		Dim BuildVendor As String = ""
		'Build Per String
		For k As Integer = 0 To ActiveItems.Count - 1
			If ActiveItems.Item(k) = True
				partNumber = iProperties.Value("Custom", MyArrayList.Item(k)).ToString.Trim().ToUpper()
				i = GoExcel.FindRow("P:\_MASTER_PART_LIST\MASTER_PART_LIST.xls", "MASTER_PART_LIST", "Part No.", "=", partNumber)
				If i = -1
					BuildPartNumber = AddToString(BuildPartNumber, "Bad Part Number " & (k+1).ToString)'Calling a custom function
					BuildDescription = AddToString(BuildDescription, "Bad Part Number " & (k+1).ToString)'Calling a custom function
					BuildVendor = AddToString(BuildVendor, "Bad Part Number " & (k+1).ToString)'Calling a custom function				
				Else
					BuildPartNumber = AddToString(BuildPartNumber, partNumber)'Calling a custom function
					BuildDescription = AddToString(BuildDescription, GoExcel.CurrentRowValue("Description").ToString.ToUpper())'Calling a custom function
					BuildVendor = AddToString(BuildVendor, GoExcel.CurrentRowValue("Manufacturer").ToString.ToUpper())'Calling a custom function
				End If
			End If
		Next
		'Set Properties
		iProperties.Value("Project", "Description") = BuildDescription
		iProperties.Value("Project", "Vendor") = BuildVendor
		iProperties.Value("Project", "Part Number") = BuildPartNumber
	End If
	']
	
End Sub 'Where main code lives

Function AddToString(Source As String, Addition As String) As String
	If Source = "" 'First Time Running
		Result = Addition
	Else
		Result = Source & " / " & Addition
	End If
	Return Result
End Function

I never had any luck getting "GoExcel.FindRow" to find my column names in the past, I'm not sure why... Anyways I didn't want to change that if it is working for you. So The only thing I couldn't test was grabbing the data from the excel file.

Let me know if you have any questions, or it is not working as intended.

Community

How is this giving me a &quot;Object variable or With block variable not set&quot; error