VBA Error 91: Object variable or With block variable not set

[KML123]

Hi there

I am very new to VBA and have the following error and cannot identify exactly where the problem lies: Please could someone help?
Thank you very much
Krista



Option Explicit
Option Base 1

'-------------------------------------------------------------------------------------
' Macro to create report for individual builder using autofilter
'-------------------------------------------------------------------------------------
Public Sub CreateReportBuilder()

Dim strBuilder As String
Dim varAllData As Variant
Dim varFindData As Variant
Dim lngLastRow As Long
Dim lngLoop As Long
Dim lngRecordCount As Long
Dim intFind As Integer
Dim intFindCount As Integer
Dim intField As Integer
Dim intFieldCount As Integer
Dim intHeaderRow As Integer
Dim intPasteFirstRow As Integer
Dim intPasteLastCol As Integer
Dim intLastUsedRow As Integer
Dim intPasteBuilderCol As Integer
Dim intBuilderCol As Integer

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error GoTo ErrorHandler

With wsReportBuilder
intHeaderRow = .Range("ClaimHeader").Row
intPasteFirstRow = intHeaderRow + 1
intPasteLastCol = .Rows(intHeaderRow).Find(What:="Additional Notes", LookIn:=xlValues, LookAt:=xlWhole).Column
intLastUsedRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row - 1
If intLastUsedRow < intPasteFirstRow Then intLastUsedRow = intPasteFirstRow
.Range(.Cells(intPasteFirstRow, 1), .Cells(intLastUsedRow, intPasteLastCol)).ClearContents

End With


strBuilder = wsReportBuilder.cboBuilder.Value

With wsDatabase
lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row - 1
varAllData = .Range(.Cells(2, 1), .Cells(lngLastRow, intPasteLastCol))
intBuilderCol = .Rows(1).Find(What:="Builder", LookIn:=xlValues, LookAt:=xlWhole).Column

'Loop through all data to find selected builder to determine array size for redim
lngRecordCount = UBound(varAllData, 1)
intFieldCount = UBound(varAllData, 2)
For lngLoop = 1 To lngRecordCount
If varAllData(lngLoop, intBuilderCol) = strBuilder Or varAllData(lngLoop, intBuilderCol + 1) = strBuilder Then
intFindCount = intFindCount + 1
End If
Next lngLoop

'Check if no records found
If intFindCount = 0 Then
' MsgBox prompt:="No records found for " & strBuilder, Title:="No data"
With wsReportBuilder
intPasteBuilderCol = .Rows(intHeaderRow).Find(What:="Builder", LookIn:=xlValues, LookAt:=xlWhole).Column
.Cells(intPasteFirstRow, 1) = "No data for " & strBuilder
.Cells(intPasteFirstRow, intPasteBuilderCol) = strBuilder
.Calculate
End With

GoTo ExitReport
End If

ReDim varFindData(intFindCount, intFieldCount)
For lngLoop = 1 To lngRecordCount
If varAllData(lngLoop, intBuilderCol) = strBuilder Or varAllData(lngLoop, intBuilderCol + 1) = strBuilder Then
intFind = intFind + 1
For intField = 1 To intFieldCount
varFindData(intFind, intField) = varAllData(lngLoop, intField)
Next intField
End If

Next lngLoop

End With

With wsReportBuilder
.Range(.Cells(intPasteFirstRow, 1), .Cells(intFindCount + intPasteFirstRow - 1, intFieldCount)) = varFindData
.Calculate
End With


ExitReport:
' Application.Calculation = xlCalculationAutomatic
Exit Sub

ErrorHandler:
MsgBox prompt:="VBA Error " & Err.Number & vbCrLf & Err.Description, _
Buttons:=vbOKOnly + vbCritical, Title:="VBA Error"
Application.Calculation = xlCalculationAutomatic

End Sub

[alansidman]

When you compile the code, which line(s) of code are highlighted.

Look at the link in my signature on debugging code. Once you have analyzed your code through debugging, post back with your findings for help.

[KML123]

Thank you.

I have de-bugged and the error occurs on this line of code...

With wsReportBuilder
intHeaderRow = .Range("ClaimHeader").Row
intPasteFirstRow = intHeaderRow + 1
intPasteLastCol = .Rows(intHeaderRow).Find(What:="Additional Notes", LookIn:=xlValues, LookAt:=xlWhole).Column
intLastUsedRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row - 1
If intLastUsedRow < intPasteFirstRow Then intLastUsedRow = intPasteFirstRow
.Range(.Cells(intPasteFirstRow, 1), .Cells(intLastUsedRow, intPasteLastCol)).ClearContents

End With

[alansidman]

for starters, in the code you presented, you have a variable wsReportBuilder that is not defined. Need to do that first. I tried to compile your code and that was the first error I got.

Alan