VB Look up when the table isnt in the first row
Are you talking about working with BOM data that has been exported to Excel? I'm trying to picture what scenario in which the "table" doesn't start on the first row. If you are dealing with Excel, then yes, you can work around the functional part of your spreadsheet not starting on the first row. You can specify which row (or column) to treat as having the column names (category names) and which row (or column) the working data starts. There are also ways to capture (or specify) which row (or column) is the last one being used by the table (working data).
Wesley Crihfield
(Not an Autodesk Employee)
Tip:
Since you said the table data in the Excel spreadsheet will always start on the same row number, you could change the number 1 in your line:
For r = 1 To 1001to that row number, then it will start searching at that row, instead of at the top of the spreadsheet.
Not a big deal, but it might save a little bit of process time.
Wesley Crihfield
(Not an Autodesk Employee)
I have a couple more variations the code for this task you can try, to see if they process any faster for you.
Both these rules are assuming your data starts on row 10, so you may need to edit that start number.
The first variation uses the built-in GoExcel.FindRow function, but properly specifies where to start looking.
Here's the code for that version:
Dim oXLS As String = "C:\Users\anikel\Desktop\1129752 BOM.xls"
Dim oSheet As String = "BOM"
'<<<< THESE TWO ROWS ARE IMPORTANT >>>>>>>>
'the row that contains the column headers
GoExcel.TitleRow = 9
'the row where the table data starts
GoExcel.FindRowStart = 10
Dim oPN As String = iProperties.Value("Project", "Part Number")
'If found (oRow = row number) ; else if (oRow = -1) it was not found
Dim oRow As Integer = GoExcel.FindRow(oXLS, oSheet, "Manufacturer Part No.", "=", oPN)
If oRow = -1 Then 'it was not found
iProperties.Value("Project", "Stock Number") = "The Part Number is not in the Project BOM"
Else 'it was found
iProperties.Value("Project", "Stock Number") = GoExcel.CellValue("B" & oRow)
End IfThen the second variation uses the InStr() function, along with some strategic plans to exit the loop and the rule as soon as possible. The InStr function will either return a zero (if not found), or if it finds it, it will return a positive Integer representing the target string's position from the left within the source string. Very simple. Since this version accesses Excel to inspect each row, I chose to have it open Excel at the start, then not specify the file and sheet name within each check, then close it at the end, to hopefully speed up the process.
Here's the code for this version:
Dim oXLS As String = "C:\Users\anikel\Desktop\1129752 BOM.xls"
Dim oSheet As String = "BOM"
GoExcel.Open(oXLS, oSheet)
GoExcel.DisplayAlerts = False
Dim oPN As String = iProperties.Value("Project", "Part Number")
Dim oRow, i As Integer
'<<< CHANGE 10 TO YOUR FIRST ROW OF DATA >>>
For oRow = 10 To 1001
If InStr(GoExcel.CellValue("F" & oRow), oPN, CompareMethod.Text) > 0 Then 'found it
iProperties.Value("Project", "Stock Number") = GoExcel.CellValue("B" & oRow)
Exit For
Exit Sub
ElseIf GoExcel.CellValue("F" & oRow) = Nothing Then
'if this cell should never be empty in a 'used' row,
'then it has reached the end of the 'used' rows and can exit
'instead of continuing to 1001 rows
'so:
iProperties.Value("Project", "Stock Number") = "The Part Number is not in the Project BOM"
Exit For
Exit Sub
End If
'if the cell's value was neither a match nor Nothing then,
'it simply goes to the next row
Next
GoExcel.CloseSee if either of these works any faster or smoother for you.
If this solved your problem, or answered your question, please click ACCEPT SOLUTION.
Or, if this helped you, please click 'LIKE' 👍.
Wesley Crihfield
(Not an Autodesk Employee)
