Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 91: Object Variable Not Set
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 -- Carlee |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 91: Object Variable Not Set
Hi Carlee,
Try changing: Dim rw As Range to Dim rw As Long --- Regards, Norman "Carlee" wrote in message ... 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 -- Carlee |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 91: Object Variable Not Set
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 In article , Carlee wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 91: Object Variable Not Set
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. "Carlee" wrote: 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 -- Carlee |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Runtime Error '91' Object variable or With block variable not set | Excel Discussion (Misc queries) | |||
Run-Time error '91': Object variable of With block variable not set | Excel Programming | |||
Getting inconsistent Error 91-Object variable or With block variable not set | Excel Programming | |||
Run-time error '91': "Object variable or With block variable not set | Excel Programming | |||
Cells.Find error Object variable or With block variable not set | Excel Programming |