Parts List Find and Replace - if then cell equals

[andrew_canfield]

Hello.

This will find & replace but I'd also like to alter the next column cell value if a criteria is met:

how can oCell.Value be edited? 

If Column Description = "M4 FORM A WASHER"

then 

Material Column = " BS EN ISO 7089:2000"

Dim oPartslist As PartsList
oPartslist = oSheet.PartsLists(1)

            Dim ic As Integer
            For ic = 1 To oSheet.PartsLists.Count
                 oPartsList = oSheet.PartsLists.Item(ic)
            Next

            Dim i As Long
            For i = 1 To oPartsList.PartsListRows.Count
                Dim oRow As PartsListRow
                 oRow = oPartsList.PartsListRows.Item(i)

                ' Iterate through each column in the row.
                Dim j As Long
                For j = 1 To oPartsList.PartsListColumns.Count
                    ' Get the current cell.
                    Dim oCell As PartsListCell
                     oCell = oRow.Item(j)

'                    ' Check that the column isn't the quantity column.
'                    If oPartslist.PartsListColumns.Item(j).Title = sColumnTitle Then

'						xx = oRow
'						yy = j
'						MessageBox.Show(xx, yy)


                        If oCell.Value = "M4 FORM A WASHER" Then
                            oCell.Value = "M4 FORM A WASHER C.S BZP"
                            oCell.Static = True
                        End If
'                    End If
                Next
            Next

Regards

Andrew

[A.Acheson · Accepted solution]

Within the filter checking the cell for matching criteria declare a new cell and index over by one column keeping on the same row. 

Dim oCell1 As PartsListCell
                     oCell1 = oRow.Item(j+1)

[andrew_canfield]

kinda fixed:

Dim oDoc As Inventor.DrawingDocument
oDoc = ThisDoc.Document

Dim oSheet As Inventor.Sheet
oSheet = oDoc.Sheets(1) ' first sheet
'oSheet = oDoc.Sheets("A0:1") ' sheet by name

' say there is a Partslist on the sheet.
Dim oPartslist As PartsList
oPartslist = oSheet.PartsLists(1)

            Dim ic As Integer
            For ic = 1 To oSheet.PartsLists.Count
                 oPartslist = oSheet.PartsLists.Item(ic)
            Next

            Dim i As Long
            For i = 1 To oPartslist.PartsListRows.Count
                Dim oRow As PartsListRow
                 oRow = oPartslist.PartsListRows.Item(i)

                ' Iterate through each column in the row.
                Dim j As Long
                For j = 1 To oPartslist.PartsListColumns.Count
                    ' Get the current cell.
                    Dim oCell As PartsListCell				
                     oCell = oRow.Item(j)
                        If oCell.Value = "M4 FORM A WASHER C.S bzp" Then
                            'oCell.Value = "M4 FORM A WASHER C.S BZP"			
						
                            oCell.Static = True
						               
                    ' Get the current cell.
					j=5                 				
                     oCell = oRow.Item(j)
                     oCell.Value = "BS EN ISO 7089:2000" 
                            'oCell.Value = "M4 FORM A WASHER C.S BZP"			
						
                            oCell.Static = True
					
                        End If	
                       
'                    
                Next
            Next

[andrew_canfield]

What's needed to edit the content of a cell if there's a a partial match?

i.e. cell = "M4 FORM A WASHER CARBON STEEL BZP" & replace "CARBON STEEL BZP" with nothing.

( so cells containing M5, M6,etc will also be edited & the specification will overwrite the material column - & the specification also references the material)

Regards

Andrew

[andrew_canfield]

Thanks Again

Is it possible to have just part of the text replaced within a cell?

eg use replace "CARBON STEEL BZP" with ""

so

M4 FORM A WASHER CARBON STEEL BZP

becomes

M4 FORM A WASHER 

(so one line of code can convert M4, M5, M6 etc)

Regards

Andrew

[A.Acheson]

Sure, if you get a hold of the string object there are loads of built in functions to work with the string. 

Enter "Replace" and then hover over the area and a description of how to use it will pop up in the tool tip.  

Dim oDesc_old As String
Dim oDesc_new As String

oDesc_old = "M4 FORM A WASHER CARBON STEEL BZP"
oDesc_new = oDesc_old.Replace("CARBON STEEL BZP", "") 
MessageBox.Show(oDesc_new, "Title")

[andrew_canfield]

Hello,

Seeing problems when trying to combine the two -  can the cell contents be seen as a string (my guess, & it is a total guess). Added comments after the ' IN CAPITALS  LINE 34- hope they help.

Dim oDoc As Inventor.DrawingDocument
oDoc = ThisDoc.Document

Dim oSheet As Inventor.Sheet
oSheet = oDoc.Sheets(1) ' first sheet
'oSheet = oDoc.Sheets("A0:1") ' sheet by name

' say there is a Partslist on the sheet.
Dim oPartslist As PartsList
oPartslist = oSheet.PartsLists(1)

            Dim ic As Integer
            For ic = 1 To oSheet.PartsLists.Count
                 oPartslist = oSheet.PartsLists.Item(ic)
            Next

            Dim i As Long
            For i = 1 To oPartslist.PartsListRows.Count
                Dim oRow As PartsListRow
                 oRow = oPartslist.PartsListRows.Item(i)

                ' Iterate through each column in the row.
                Dim j As Long
                For j = 1 To oPartslist.PartsListColumns.Count
                    ' Get the current cell.
                    Dim oCell As PartsListCell				
                     oCell = oRow.Item(j)
					 
					 
						Dim oDesc_old As String
						Dim oDesc_new As String

						oDesc_old = oCell ' THIS CREATES AN ERROR - GUESS oCell ISN'T A STRING?
						oDesc_new = oDesc_old.Replace("CARBON STEEL BZP", "") 
						MessageBox.Show(oDesc_old, "oCell") ' THE MESSAGE BOX WILL NOT DISPLAY oCell - BECAUSE IT'S NOT A STRING?
					 
					 	oCell = oDesc_new
						
'                        If oCell.Value = "M4 FORM A WASHER C.S bzp" Then ' GUESS THE IF WILL NOT BE NEEDED AS EDITS SHOULD UPDATE
'                            'oCell.Value = "M4 FORM A WASHER C.S BZP"			
						
                            oCell.Static = True						
                        'End If	                      
'                    
                Next
            Next

 Regards

Andrew

[A.Acheson · Accepted solution]

oCell on it's own wouldn't be a string but  oCell.Value would be.