Error Generating BOM --- Error 91: Object variable or With block variable not set

[the_rizzler]

I get this error when I click the "Generate BOM" button in an Excel Workbook. I'm very new to VBA and Excel and not quite sure what to do here.

 

[dreid1011]

It would help if you could share the VBA code attached to the button.

 

[the_rizzler]

It would help if you could share the VBA code attached to the button.

//This is one of the two modules. This module is called "BrowseForFileUI"

'Declaring Variables for the new BOM form
Dim gIsInit As Boolean
Dim gChangeEventHooks As Collection
Dim gBomFileCell As Range
Dim gBOMTemplateSheet As String
Dim gSkipSheets As Variant
Dim gFirstMatRow As Long
Dim gFirstBOMRow As Long
'Declaring Variables for the Req form
Dim gReqFileCell As Range
Dim gReqTemplateSheet As String, gFirstReqRow As Integer

Sub Init()

gIsInit = False 'If this is uncommented: The subroutine "Init" to run everytime it is called (useful for code changes)

'Prevent Init execution after first execution
If gIsInit Then Exit Sub
gIsInit = True

'Define static named cells
Set gBomFileCell = Range("D5")
Set gReqFileCell = Range("D16")

'Defining other settings
gBOMTemplateSheet = "BOM"
gFirstBOMRow = 24 'First row after headers of BOM Template'

'Settings for req form
gReqTemplateSheet = "Materials To Be Requisitioned"
gFirstReqRow = 19

gSkipSheets = Array("Material Summary", "BOM Gen") 'Add sheets to skip to this list
gFirstMatRow = 20 'First row after headers in material sheets of this workbook

'Create EventHooks for worksheet change
Set gChangeEventHooks = New Collection

End Sub

Private Sub BrowseBOMButton_Click()

Init

Dim lFilePath As String
Dim temp As Long
Dim lastdash As Long

If FileExists(gBomFileCell.Text) Then
    'Open Browse dialog in same directory as currently selected file
   
    'Begin by locating the last "\"
    temp = 1
    Do
        lastdash = temp
        temp = InStr(lastdash + 1, gBomFileCell.Text, "\", vbTextCompare)
    Loop While temp <> 0
   
    'Call the file dialog with only the directory as the inital location
    lFilePath = BrowseFolder("Please Select the BOM Template File", Left(gBomFileCell.Text, lastdash))
Else
    lFilePath = BrowseFolder("Please Select the BOM Template File")
End If

If lFilePath <> "" Then gBomFileCell.value = lFilePath 'Do nothing if user clicked cancel
End Sub
Private Sub GenBOMButton_Click()

On Error GoTo ErrHandle

Init

'Check to make sure file exists
If Not FileExists(gBomFileCell.Text) Then
    MsgBox ("Error: Unable to locate BOM template. Please fix the file path you provided.")
    Exit Sub
End If

'This is the code to open up the BOM Template for Editing'
'Declaring variables'
Dim lMatSheet As Variant

Dim app As Excel.Application
Dim wb As Excel.Workbook
Dim BOMTemplate As Excel.Worksheet

Set app = CreateObject("Excel.Application")
app.DisplayAlerts = False

Set wb = app.Workbooks.Open(gBomFileCell.Text, , True) 'Open the workbook as read only so that its harder to save over the template

Set BOMTemplate = wb.Sheets(gBOMTemplateSheet)
BOMTemplate.Activate

'Map out all the columns necessary
Dim lBOMNum As Long, lBOMCat As Long, lBOMMan As Long, lBOMManuNo As Long, lBOMDesc As Long, lBOMSub As Long, lBOMSupp As Long, lBOMMatType As Long, lBOMUnit As Long, lBOMBaseQty As Long
Dim lBOMContQty As Long, lBOMTotQty As Long, lBOMNeedDate As Long, lBOMLocaton As Variant, lBOMRev As Long, lBOMNotes As Long, lBOMUnitPrice As Long, lBOMTotalPrice As Long, lBOMTask As Long, lBOMStat As Long
With BOMTemplate.Rows(gFirstBOMRow - 1)
    lBOMNum = .Find("Line " & vbLf & "Number").Column
    lBOMCat = .Find("Item Number").Column
    lBOMMan = .Find("Manufacturer").Column
    lBOMManuNo = .Find("Manufacturer Part Num").Column
    lBOMDesc = .Find("Item Description").Column
    lBOMSub = .Find("Substitutes Permitted (Y/N)").Column 'Column populated with "N" for all non-ET number items (discussed with AA)
    lBOMSupp = .Find("Potential Supplier").Column 'Potential Supplier Column populated with "Vendor" info from Material Summary (discussed with AA)
   ' lBOMMatType = .Find("Material Type").Column  // removed to match updated MM Template GB-20210106
    lBOMUnit = .Find("Unit of " & vbLf & "Measurement").Column
    lBOMBaseQty = .Find("Base " & vbLf & "Quantity").Column
    lBOMContQty = .Find("Contingency " & vbLf & "Quantity").Column
    lBOMTotQty = .Find("Total " & vbLf & "Quantity").Column
    lBOMNeedDate = .Find("Need-By Date").Column
    lBOMLocation = .Find("Deliver To (Destination)").Column 'Column populated with Nisku as the Destination (discussed with AA)
    lBOMRev = .Find("Revision").Column
    lBOMNotes = .Find("Notes").Column
    lBOMUnitPrice = .Find("Price").Column
    lBOMTotalPrice = .Find("Extended Cost").Column
    lBOMTask = .Find("CBS Code (Task)").Column
    lBOMStat = .Find("Item Status").Column
End With

'Initialize
lCurBomRow = gFirstBOMRow

'Clear BOM Template
If gFirstBOMRow - 1 <> BOMTemplate.Range("A" & BOMTemplate.Rows.Count).End(xlUp).Row Then BOMTemplate.Range(gFirstBOMRow & ":" & BOMTemplate.Range("A" & BOMTemplate.Rows.Count).End(xlUp).Row).Delete

Set lMatSheet = Worksheets("Material Summary")

'Map All Material Summary Sheet Columns
Dim lMatCat As Long, lMatMan As Long, lMatPart As Long, lMatTask As Long, lMatUoM As Long, lMatDesc As Long, lMatQty As Long, lMatCost As Long, lMatTCost As Long
Dim lMatRef As Long, lMatType As Long, lMatSub As Long, lMatSupp As Long, lMatStat As Long

With lMatSheet.Rows(gFirstMatRow - 1)
    lMatCat = .Find("Oracle" & vbLf & "Cat No.").Column
    lMatMan = .Find("Manufacturer").Column
    lMatPart = .Find("Part" & vbLf & "No.").Column
    lMatTask = .Find("Task" & vbLf & "Code").Column
    lMatUoM = .Find("Unit of" & vbLf & "Measure").Column
    lMatDesc = .Find("Description").Column
    lMatQty = .Find("Quantity").Column 'This is Base Qty; previously coded as 'Qty', so left as is
    lMatCost = .Find("Unit" & vbLf & "Cost").Column
    lMatTCost = .Find("Total Cost").Column
    lMatType = .Find("Type").Column
    lMatRef = .Find("REF" & vbLf & "Required").Column
    lMatSub = .Find("SUBSTITUTES PERMITTED (Y/N)").Column
    lMatSupp = .Find("Vendor").Column
    lMatStat = .Find("Item Status").Column
End With
   
       
    For i = gFirstMatRow To lMatSheet.Range("A" & lMatSheet.Rows.Count).End(xlUp).Row - 1
   
       
            With BOMTemplate.Range("A" & lCurBomRow & ":" & BOMTemplate.Cells(lCurBomRow, lBOMNotes).Address)
                .Borders.LineStyle = xlContinuous
                .Cells.WrapText = True
                .Cells.HorizontalAlignment = xlCenter
                .Cells.VerticalAlignment = xlVAlignCenter
                .Cells(1, lBOMDesc).HorizontalAlignment = xlLeft
            End With

            With BOMTemplate
            'Insert
                .Cells(lCurBomRow, lBOMNum).value = lCurBomRow - gFirstBOMRow + 1
                .Cells(lCurBomRow, lBOMCat).value = lMatSheet.Cells(i, lMatCat).Text
                .Cells(lCurBomRow, lBOMMan).value = lMatSheet.Cells(i, lMatMan).Text
                .Cells(lCurBomRow, lBOMManuNo).value = lMatSheet.Cells(i, lMatPart).Text
                .Cells(lCurBomRow, lBOMDesc).value = lMatSheet.Cells(i, lMatDesc).Text
                '.Cells(lCurBomRow, lBOMMatType).value = lMatSheet.Cells(i, lMatType).Text  // removed to match updated MM Template  GB-20210106
                .Cells(lCurBomRow, lBOMUnit).value = lMatSheet.Cells(i, lMatUoM).Text
                .Cells(lCurBomRow, lBOMBaseQty).value = lMatSheet.Cells(i, lMatQty).Text
                .Cells(lCurBomRow, lBOMTotQty).FormulaR1C1 = "=sum(R[0]C[-2]:R[0]C[-1])"
                .Cells(lCurBomRow, lBOMRev).value = 0
                .Cells(lCurBomRow, lBOMUnitPrice).value = lMatSheet.Cells(i, lMatCost).Text
                .Cells(lCurBomRow, lBOMTotalPrice).FormulaR1C1 = "=R[0]C[-6]*R[0]C[-1]"  'modified to put formula into BOM sheet    GB-20210106
                .Cells(lCurBomRow, lBOMTask).value = lMatSheet.Cells(i, lMatTask).Text
                .Cells(lCurBomRow, lBOMSub).value = lMatSheet.Cells(i, lMatSub).Text
                .Cells(lCurBomRow, lBOMSupp).value = lMatSheet.Cells(i, lMatSupp).Text
                .Cells(lCurBomRow, lBOMNeedDate).value = lMatSheet.Cells(12, 4).Text
                .Cells(lCurBomRow, lBOMLocation).value = lMatSheet.Cells(13, 4).Text
                .Cells(lCurBomRow, lBOMContQty).value = lMatSheet.Cells(14, 4).Text
                .Cells(lCurBomRow, lBOMStat).value = lMatSheet.Cells(i, lMatStat).Text
            End With
           
            lCurBomRow = lCurBomRow + 1
           
    Next
   
'Code to populate the generic fields in the template
With BOMTemplate
    .Cells(3, 4).value = "Automation & SCADA (ASE) BOM"
    .Cells(10, 2).value = 0
    .Cells(10, 3).value = "INITIAL BOM - DBM STAGE"
    .Cells(10, 8).value = lMatSheet.Cells(5, 4).Text
    .Cells(4, 4).value = lMatSheet.Cells(6, 4).Text
    .Cells(6, 8).value = lMatSheet.Cells(7, 4).Text & " - " & lMatSheet.Cells(8, 4).Text & " - " & lMatSheet.Cells(9, 4).Text & " - ASE - BOM - REV.0"
    .Cells(11, 14).value = lMatSheet.Cells(10, 4).Text
    .Cells(11, 11).value = lMatSheet.Cells(11, 4).Text
   
End With

Skip:
app.Visible = True

Exit Sub
ErrHandle:
junk = MsgBox("Error " & Err.Number & ": " & Err.Description, vbCritical, "Error Generating BOM")

On Error Resume Next
wb.Close False
Set app = Nothing
End Sub
'Comment this subroutine if the Req form is no longer needed

Private Sub BrowseReqButton_Click()
Init

Dim lFilePath As String
Dim temp As Long
Dim lastdash As Long

If FileExists(gReqFileCell.Text) Then
    'Open Browse dialog in same directory as currently selected file
   
    'Begin by locating the last "\"
    temp = 1
    Do
        lastdash = temp
        temp = InStr(lastdash + 1, gReqFileCell.Text, "\", vbTextCompare)
    Loop While temp <> 0
   
    'Call the file dialog with only the directory as the inital location
    lFilePath = BrowseFolder("Please Select the BOM Template File", Left(gReqFileCell.Text, lastdash))
Else
    lFilePath = BrowseFolder("Please Select the BOM Template File")
End If

If lFilePath <> "" Then gReqFileCell.value = lFilePath 'Do nothing if user clicked cancel
End Sub
Private Sub GenReqButton_Click()

'On Error GoTo ErrHandle

Init

'Check to make sure file exists
If Not FileExists(gReqFileCell.Text) Then
    MsgBox ("Error, Unable to locate Req template. Please fix the file path you provided.")
    Exit Sub
End If

Dim lMatSheet As Variant

'Open up the Template for Editing
Dim app As Excel.Application
Dim wb As Excel.Workbook
Dim ReqTemplate As Excel.Worksheet

Set app = CreateObject("Excel.Application")
app.DisplayAlerts = False
'app.Visible = True 'For debugging, slows process significantly

Set wb = app.Workbooks.Open(gReqFileCell.Text, , True) 'Open the workbook as readonly so that its harder to save over the template
Set ReqTemplate = wb.Sheets(gReqTemplateSheet)
ReqTemplate.Activate

'Map out all the columns necessary
Dim lReqNum As Long, lReqCat As Long, lReqVend As Long, lReqDesc As Long, lReqManuNo As Long, lReqTotQty As Long, lReqUnit As Long, lReqUnitPrice As Long, lReqTotalPrice As Long, lReqMatType As Long, lReqTask As Long, lReqNotes As Long
With ReqTemplate.Rows(gFirstReqRow - 1)
    lReqNum = .Find("BOM Item " & vbLf & "Number").Column
    lReqCat = .Find("E*").Column
    lReqVend = .Find("Vendor").Column
    lReqDesc = .Find("Item Description").Column
    lReqManuNo = .Find("Manufacturer Number").Column
    lReqTotQty = .Find("Quantity To Be Requisitioned").Column
    lReqUnit = .Find("Unit Of " & vbLf & "Measurement").Column
    lReqUnitPrice = .Find("Unit Price").Column
    lReqTotalPrice = .Find("Total Price").Column
    lReqMatType = .Find("Material Type").Column
    lReqTask = .Find("Task").Column
    lReqNotes = .Find("Notes").Column
End With

'Initialize
lCurReqRow = gFirstReqRow

'Clear Req Template
If gFirstReqRow - 1 <> ReqTemplate.Range("A" & ReqTemplate.Rows.Count).End(xlUp).Row Then ReqTemplate.Range(gFirstReqRow & ":" & ReqTemplate.Range("A" & ReqTemplate.Rows.Count).End(xlUp).Row).Delete

Set lMatSheet = Worksheets("Material Summary")
   
'Map All Material Sheet Columns
Dim lMatCat As Long, lMatVend As Long, lMatMan As Long, lMatPart As Long, lMatTask As Long, lMatDesc As Long, lMatQty As Long, lMatUoM As Long, lMatCost As Long, lMatRef As Long, lMatType As Long

With lMatSheet.Rows(gFirstMatRow - 1)
    lMatCat = .Find("Oracle" & vbLf & "Cat No.").Column
    lMatVend = .Find("Vendor").Column
    lMatMan = .Find("Manufacturer").Column
    lMatPart = .Find("Manufacturer Part" & vbLf & "No.").Column
    lMatTask = .Find("Task" & vbLf & "Code").Column
    lMatDesc = .Find("Description").Column
    lMatQty = .Find("Quantity").Column
    lMatUoM = .Find("Unit of" & vbLf & "Measure").Column
    lMatCost = .Find("Unit" & vbLf & "Cost").Column
    lMatType = .Find("Type").Column
    lMatRef = .Find("REF" & vbLf & "Required").Column
End With
   
    For i = gFirstMatRow To lMatSheet.Range("A" & lMatSheet.Rows.Count).End(xlUp).Row - 1
   
           With ReqTemplate.Range("A" & lCurReqRow & ":" & ReqTemplate.Cells(lCurReqRow, lReqNotes).Address)
                .Borders.LineStyle = xlContinuous
                .Cells.WrapText = True
                .Cells.HorizontalAlignment = xlCenter
                .Cells.VerticalAlignment = xlVAlignCenter
                .Cells(1, lReqDesc).HorizontalAlignment = xlLeft
            End With
           
            With ReqTemplate
            'Insert
                .Cells(lCurReqRow, lReqNum).value = lCurReqRow - gFirstReqRow + 1
                .Cells(lCurReqRow, lReqCat).value = lMatSheet.Cells(i, lMatCat).Text
                .Cells(lCurReqRow, lReqVend).value = lMatSheet.Cells(i, lMatVend).Text
                .Cells(lCurReqRow, lReqManuNo).value = lMatSheet.Cells(i, lMatPart).Text
                .Cells(lCurReqRow, lReqMatType).value = lMatSheet.Cells(i, lMatType).Text
                .Cells(lCurReqRow, lReqDesc).value = lMatSheet.Cells(i, lMatMan).Text & " - " & lMatSheet.Cells(i, lMatDesc).Text
                .Cells(lCurReqRow, lReqTotQty).value = lMatSheet.Cells(i, lMatQty).Text
                .Cells(lCurReqRow, lReqUnit).value = lMatSheet.Cells(i, lMatUoM).Text
                .Cells(lCurReqRow, lReqUnitPrice).value = lMatSheet.Cells(i, lMatCost).Text
                .Cells(lCurReqRow, lReqTotalPrice).FormulaR1C1 = "=R[0]C[-3]*R[0]C[-1]"
                .Cells(lCurReqRow, lReqTask).value = lMatSheet.Cells(i, lMatTask).Text
            End With
           
            lCurReqRow = lCurReqRow + 1
           
    Next
  
'Code to populate the generic fields in the template
With ReqTemplate
    .Cells(9, 2).value = lMatSheet.Cells(7, 4).Text
    .Cells(10, 2).value = lMatSheet.Cells(8, 4).Text
    .Cells(11, 2).value = lMatSheet.Cells(6, 4).Text
    .Cells(12, 2).value = lMatSheet.Cells(12, 4).Text
    .Cells(11, 12).value = lMatSheet.Cells(11, 4).Text
    .Cells(10, 11).value = lMatSheet.Cells(11, 4).Text
    .Cells(10, 12).value = lMatSheet.Cells(10, 4).Text
    .Cells(13, 2).value = lMatSheet.Cells(7, 4).Text & " - " & lMatSheet.Cells(8, 4).Text & " - " & lMatSheet.Cells(9, 4).Text & " - ASE - BOM - REV.0"
   
End With
Skip:
app.Visible = True

Exit Sub
ErrHandle:
junk = MsgBox("Error " & Err.Number & ": " & Err.Description, vbCritical, "Error Generating Req")
On Error Resume Next
wb.Close False
Set app = Nothing
End Sub



Sub Worksheet_Change(ByVal Target As Range)

Init

Dim lHookRange As Range
For Each lEventHook In gChangeEventHooks

If Not Intersect(lEventHook.HookRange, Target) Is Nothing Then CallByName Me, lEventHook.Handler, VbMethod

Next lEventHook

End Sub


//This is the second of the two modules. This one is called "FileFunctions"
Function FileExists(FilePath As String) As Boolean
    FileExists = Not Dir(FilePath) = ""
End Function

Function filelist(lFileSearch As String) As String()
    Dim lDirResult As String
    Dim lFileList() As String

    ' Function finds all normal attribute files in a folder
    lDirResult = Dir(lFileSearch) ' Retrieve the first file name

    If lDirResult = "" Then GoTo ListEmpty ' Folder has no files
    ReDim lFileList(0) As String
    lFileList(0) = lDirResult

    Do
        lDirResult = Dir()
        If lDirResult = "" Then Exit Do ' End of files
        ReDim Preserve lFileList(UBound(lFileList) + 1) As String
        lFileList(UBound(lFileList)) = lDirResult
    Loop

    filelist = lFileList
ListEmpty:
End Function

Function MyDocuments() As String
    Dim objFSO As Object
    Dim objShell As Object
    Dim objFolder As Object
    Dim objFolderItem As Object
   
    Const MY_DOCUMENTS = &H5&
   
    ' Initialize objects
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objShell = CreateObject("Shell.Application")
   
    Set objFolder = objShell.Namespace(MY_DOCUMENTS)
    Set objFolderItem = objFolder.Self
    MyDocuments = objFolderItem.Path
   
    ' Clean up
    Set objFSO = Nothing
    Set objShell = Nothing
    Set objFolder = Nothing
    Set objFolderItem = Nothing
End Function

Sub DeleteFile(ByVal FileToDelete As String)
    If FileExists(FileToDelete) Then
        SetAttr FileToDelete, vbNormal
        Kill FileToDelete
    End If
End Sub

Sub EmptyDir(lDirPath As String)
    Dim lDirList As Variant
    Dim i As Long

    lDirList = filelist(lDirPath)
    If Len(Join(lDirList)) <> 0 Then ' For when dir is already empty
        For i = 0 To UBound(lDirList)
            DeleteFile (lDirPath & lDirList(i))
        Next
    End If
End Sub

 

[dreid1011]

Private Sub BrowseBOMButton_Click()

Private Sub BrowseReqButton_Click()

Private Sub GenReqButton_Click()

Okay, I see these three subs for buttons. Is the button named "GenReqButton"?

Also, when the error appears, if you click on Debug, what line of code is highlighted?

 

[the_rizzler]

Okay, I see these three subs for buttons. Is the button named "GenReqButton"?

Also, when the error appears, if you click on Debug, what line of code is highlighted?

I apologize, the first piece of code I included was for Sheet3(BOM) which is a Microsoft Excel Object. Which may or may not be a problem, again I'm not sure as I didn't write this code.
The second piece of code I inlcuded was a module.
The code for the "BrowseForFileUI" (which is also a module) is as follows:
Function BrowseFolder(Title As String, Optional InitialFilename As String) As String
Dim V As Variant
With Application.FileDialog(msoFileDialogFilePicker)
.Title = Title
.InitialFilename = InitialFilename
.Filters.Add "Excel Workbooks", "*.xls; *.xlsx; *.xlt; *.xltx; *.xlsb; *.xlsm", 1
.Show
On Error Resume Next
Err.Clear
V = .SelectedItems(1)
If Err.Number <> 0 Then
V = vbNullString
End If
End With
BrowseFolder = CStr(V)
End Function

To answer your first question, the button named "GenReqButton" generates a Requisition document, something we no longer need or require, so it should not be used. However, we do use the Generate BOM button, which in this case would be the "GenBOMButton_Click()" function.

To answer your second question, after a few seconds of pressing the Generate BOM button, I get an error pop-up box, and not the usual excel error box. The error box I get only has two actions that can be performed: either cross it (top right) and an OK button (bottom right).

I hope this makes at least a little bit of sense.

 

[dreid1011]

Okay, I missed the "GenBOMButton_Click()" the first time. Without seeing which line is throwing the error, I may not be of much help. Are you able to comment out the error handling section of that code and then click the button?

'On Error GoTo ErrHandle

'ErrHandle:
'junk = MsgBox("Error " & Err.Number & ": " & Err.Description, vbCritical, "Error Generating BOM")

'On Error Resume Next

 

[the_rizzler]

Okay, I missed the "GenBOMButton_Click()" the first time. Without seeing which line is throwing the error, I may not be of much help. Are you able to comment out the error handling section of that code and then click the button?

'On Error GoTo ErrHandle

'ErrHandle:
'junk = MsgBox("Error " & Err.Number & ": " & Err.Description, vbCritical, "Error Generating BOM")

'On Error Resume Next

Hi I commented that portion of the code out. Upon running the code again i.e pressing the button, I now get the error box with the title "Microsoft Visual Basic"
and now the dialog box says
"Run-time error '91':
Object variable or With block variable not set"
and I get the options:
continue (this one cannot be pressed as it is greyed out
End
Debug
Help
Upon pressing Debug, it takes me to the VBA code editor and highlights this line of code:
lMatPart = .Find("Part" & vbLf & "No.").Column

 

[dreid1011]

Okay, comment out that line now and try it again. Let's see if the error is only on that line, or if it is present on others as well.

 

[the_rizzler]

I did comment out that line and then ran the program again. It then gave yet another run-time error. So I debugged again. I commented out the highlighted line. Ran the code again.
This time, the code does do it job. It opens a new workbook, and shows the generated BOM with all the parts, etc.
However, it is missing a very important entry.
In the "Manufacturer Part Num" column, nothing populates. I think this is because we commented that line out.

 

[dreid1011]

I did comment out that line and then ran the program again. It then gave yet another run-time error. So I debugged again. I commented out the highlighted line. Ran the code again.
This time, the code does do it job. It opens a new workbook, and shows the generated BOM with all the parts, etc.
However, it is missing a very important entry.
In the "Manufacturer Part Num" column, nothing populates. I think this is because we commented that line out.

Yes, that is very likely why. So, on those two lines you commented out is the issue. My initial thought is those lines are not finding what they are looking for. My next suggestion would be to double check the sheet and row those lines are looking in and make sure the values they are trying to find actually exist and exist as specifically as they are in the lines of code.