Error 91: Object Variable Not Set

[Carlee]

Sorry about the previous post, i hit Enter and it submitted it...doh

I am getting an error 91: Object Variable or With Block Not Set on the
following code, but not sure how to correct it. It seems to be hanging up at:

rw = SH.Range("A:A").Find(Me.cboMonth.Value, LookIn:=xlValues).Row

I've tried a variety of things, but to no avail. Any ideas? Full code is
below.

Private Sub cmdSubmit_Click()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim sStr As String
Dim msg As String
Dim rw As Range

Set WB = ThisWorkbook
Set SH = WB.Sheets("HQ Input Log")
Set Rng = SH.Columns("A:A")
sStr = Me.cboMonth.Value

WB.Activate
SH.Activate

rw = SH.Range("A:A").Find(Me.cboMonth.Value, LookIn:=xlValues).Row

If Not Me.cboMonth.Value Is Nothing Then
If MsgBox("The HQ Input Log already contains a reading entry for the
month of " & sStr & ". Do you want to replace the existing entry with your
new one?", vbYesNo) = vbYes Then

SH.Cells(rw, 1) = Me.cboMonth.Value
SH.Cells(rw, 2) = Me.txtPreparedBy.Value
SH.Cells(rw, 3) = Me.txtNoLdsAtSmelter.Value
SH.Cells(rw, 4) = Me.txtWeight.Value
SH.Cells(rw, 5) = Me.txtMoisture.Value
SH.Cells(rw, 6) = Me.txtDryWeight.Value
SH.Cells(rw, 7) = Me.txtCuWeight.Value
SH.Cells(rw, 8) = Me.txtCuPercent.Value

MsgBox "Your submission has replaced the previous reading for the
month of " & sStr
Else
MsgBox "The entry process has been canceled. No entry has been
added to the HQ Input Log"
End If
Else
Range("A" & Cells(Rows.Count, 1).End(xlUp).Row + 1).Activate
ActiveCell.Offset(0, 0) = Me.cboMonth
ActiveCell.Offset(0, 1) = Me.txtPreparedBy
ActiveCell.Offset(0, 2) = Me.txtNoLdsAtSmelter.Value
ActiveCell.Offset(0, 3) = Me.txtWeight.Value
ActiveCell.Offset(0, 4) = Me.txtMoisture.Value
ActiveCell.Offset(0, 5) = Me.txtDryWeight.Value
ActiveCell.Offset(0, 6) = Me.txtCuWeight.Value
ActiveCell.Offset(0, 7) = Me.txtCuPercent.Value
End If
Unload Me
MsgBox "Your submission has been successfully added to the HQ Log."
End Sub

 

[Norman Jones]

Hi Carlee,

Try changing:

Dim rw As Range
to

Dim rw As Long

 

[JE McGimpsey]

You're trying to access the row of a range that doesn't exist (i.e,
Me.cboMonth.Value wasn't found in SH.Range("A:A")).

Perhaps add/rearrange something like:

Dim rFound As Range
DIm nResult As Long

Set rFound = SH.Range("A:A").Find( _
What:=Me.cboMonth.Value, _
LookIn:=xlValues)
If rFound Is Nothing Then
With SH
Set rFound = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
End With
Else
nResult = MsgBox(Prompt:="The HQ Input Log...", Buttons:=vbYesNo)
If nResult = vbNo Then
MsgBox "The entry process has been cancelled..."
Exit Sub
End If
End If
With rFound.Resize(1, 8)
.Item1 = Me.cboMonth.Value
.Item2 = Me.txtPreparedBy.Value
'...
.Item8 = Me.txtCuPercent.Value
End With
If nResult = vbYes Then
MsgBox "Your submission has replaced..."
Else
MsgBox "Your submission has successfully..."
End If

 

[JLGWhiz]

I'll give it one more try Carlee:

Private Sub cmdSubmit_Click()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim sStr As String
Dim msg As String
Dim rw As Long

Set WB = ThisWorkbook
Set SH = WB.Sheets("HQ Input Log")
Set Rng = SH.Columns("A:A")
sStr = Me.cboMonth.Value

WB.Activate
SH.Activate
With WB.Sheets("HQ Input Log")
rw = .Find(sStr, LookIn:=xlValues).Row
If Not rw Is Nothing Then
If MsgBox("The HQ Input Log already contains a reading entry for the
month of " & sStr & ". Do you want to replace the existing entry with your
new one?", vbYesNo) = vbYes Then
.Cells(rw, 1) = sStr
.Cells(rw, 2) = Me.txtPreparedBy.Value
.Cells(rw, 3) = Me.txtNoLdsAtSmelter.Value
.Cells(rw, 4) = Me.txtWeight.Value
.Cells(rw, 5) = Me.txtMoisture.Value
.Cells(rw, 6) = Me.txtDryWeight.Value
.Cells(rw, 7) = Me.txtCuWeight.Value
.Cells(rw, 8) = Me.txtCuPercent.Value
MsgBox "Your submission has replaced the previous reading for the
month of " & sStr
Else
MsgBox "The entry process has been canceled. No entry has been
added to the HQ Input Log"
End If
Else
Range("A" & Cells(Rows.Count, 1).End(xlUp).Row + 1).Activate
ActiveCell.Offset(0, 0) = Me.cboMonth
ActiveCell.Offset(0, 1) = Me.txtPreparedBy
ActiveCell.Offset(0, 2) = Me.txtNoLdsAtSmelter.Value
ActiveCell.Offset(0, 3) = Me.txtWeight.Value
ActiveCell.Offset(0, 4) = Me.txtMoisture.Value
ActiveCell.Offset(0, 5) = Me.txtDryWeight.Value
ActiveCell.Offset(0, 6) = Me.txtCuWeight.Value
ActiveCell.Offset(0, 7) = Me.txtCuPercent.Value
End If
End With
MsgBox "Your submission has been successfully added to the HQ Log."
Unload Me
End Sub

It should work, but I am not so sure about the Me references, since in one
place it refers to the worksheet and at the end you use it to unload the
control. I also moved your MsgBox at the end to execute before the unload
command. Let me know if it works.