ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   VBA Run time error 91 (https://www.excelbanter.com/excel-programming/391449-vba-run-time-error-91-a.html)

Shawn
VBA Run time error 91
 
Below is my code. I need two things: 1) a suggestion on how to make the code
more efficient and 2) an explaination why I am getting a "Run-time error
€˜91: Object variable or With block variable not set" at the indicated spot.

Public Sub UserForm_Activate()
Set WSCal = Sheets("Cal")
If WSCal.Range("Rx1").Value = "" Then
WSCal.Range("RxRange,RxAnsRange").ClearContents
Set RxTarget = WSCal.Range("Rx1")
ElseIf WSCal.Range("Rx12").Value = "x" Then
Set RxTarget = WSCal.Range("Rx12")
ElseIf WSCal.Range("Rx11").Value = "x" Then
Set RxTarget = WSCal.Range("Rx11")
ElseIf WSCal.Range("Rx10").Value = "x" Then
Set RxTarget = WSCal.Range("10")
ElseIf WSCal.Range("Rx9").Value = "x" Then
Set RxTarget = WSCal.Range("Rx9")
ElseIf WSCal.Range("Rx8").Value = "x" Then
Set RxTarget = WSCal.Range("Rx8")
ElseIf WSCal.Range("Rx7").Value = "x" Then
Set RxTarget = WSCal.Range("Rx7")
ElseIf WSCal.Range("Rx6").Value = "x" Then
Set RxTarget = WSCal.Range("Rx6")
ElseIf WSCal.Range("Rx5").Value = "x" Then
Set RxTarget = WSCal.Range("Rx5")
ElseIf WSCal.Range("Rx4").Value = "x" Then
Set RxTarget = WSCal.Range("Rx4")
ElseIf WSCal.Range("Rx3").Value = "x" Then
Set RxTarget = WSCal.Range("Rx3")
ElseIf WSCal.Range("Rx2").Value = "x" Then
Set RxTarget = WSCal.Range("Rx2")
End If

usfRxOn.frmRxOn.Caption = RxTarget.Offset(0, -1).Value

With usfRxOn
If .frmRxOn.Caption < "Medication 1:" Then
.chbxRxOnNa.Enabled = False
.cmbCont.Enabled = True
.cmbuNextRx.Enabled = True
.cmbRestart.Enabled = True
Else
.chbxRxOnNa.Enabled = True
.cmbCont.Enabled = False
.cmbuNextRx.Enabled = False
.cmbRestart.Enabled = False
End If
.lblRxOn.Caption = WSCal.Range("RxOnQ").Value <<<<error here
End With
Application.ScreenUpdating = False
End Sub

--
Thanks
Shawn

Bob Phillips
VBA Run time error 91
 
Not clear because RxOnQ is not referenced elsewhere, but if it is a range
name that's fine, but if it is a range object it should be

.lblRxOn.Caption = RxOnQ.Value


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Shawn" wrote in message
...
Below is my code. I need two things: 1) a suggestion on how to make the
code
more efficient and 2) an explaination why I am getting a "Run-time error
'91': Object variable or With block variable not set" at the indicated
spot.

Public Sub UserForm_Activate()
Set WSCal = Sheets("Cal")
If WSCal.Range("Rx1").Value = "" Then
WSCal.Range("RxRange,RxAnsRange").ClearContents
Set RxTarget = WSCal.Range("Rx1")
ElseIf WSCal.Range("Rx12").Value = "x" Then
Set RxTarget = WSCal.Range("Rx12")
ElseIf WSCal.Range("Rx11").Value = "x" Then
Set RxTarget = WSCal.Range("Rx11")
ElseIf WSCal.Range("Rx10").Value = "x" Then
Set RxTarget = WSCal.Range("10")
ElseIf WSCal.Range("Rx9").Value = "x" Then
Set RxTarget = WSCal.Range("Rx9")
ElseIf WSCal.Range("Rx8").Value = "x" Then
Set RxTarget = WSCal.Range("Rx8")
ElseIf WSCal.Range("Rx7").Value = "x" Then
Set RxTarget = WSCal.Range("Rx7")
ElseIf WSCal.Range("Rx6").Value = "x" Then
Set RxTarget = WSCal.Range("Rx6")
ElseIf WSCal.Range("Rx5").Value = "x" Then
Set RxTarget = WSCal.Range("Rx5")
ElseIf WSCal.Range("Rx4").Value = "x" Then
Set RxTarget = WSCal.Range("Rx4")
ElseIf WSCal.Range("Rx3").Value = "x" Then
Set RxTarget = WSCal.Range("Rx3")
ElseIf WSCal.Range("Rx2").Value = "x" Then
Set RxTarget = WSCal.Range("Rx2")
End If

usfRxOn.frmRxOn.Caption = RxTarget.Offset(0, -1).Value

With usfRxOn
If .frmRxOn.Caption < "Medication 1:" Then
.chbxRxOnNa.Enabled = False
.cmbCont.Enabled = True
.cmbuNextRx.Enabled = True
.cmbRestart.Enabled = True
Else
.chbxRxOnNa.Enabled = True
.cmbCont.Enabled = False
.cmbuNextRx.Enabled = False
.cmbRestart.Enabled = False
End If
.lblRxOn.Caption = WSCal.Range("RxOnQ").Value <<<<error here
End With
Application.ScreenUpdating = False
End Sub

--
Thanks
Shawn



Shawn
VBA Run time error 91
 
I noted the error in the wrong place. It should have been on the line of
code starting with "usfRxOn.frmRxOn.Caption..." right after the ElseIf
section.
--
Thanks
Shawn


"Bob Phillips" wrote:

Not clear because RxOnQ is not referenced elsewhere, but if it is a range
name that's fine, but if it is a range object it should be

.lblRxOn.Caption = RxOnQ.Value


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Shawn" wrote in message
...
Below is my code. I need two things: 1) a suggestion on how to make the
code
more efficient and 2) an explaination why I am getting a "Run-time error
'91': Object variable or With block variable not set" at the indicated
spot.

Public Sub UserForm_Activate()
Set WSCal = Sheets("Cal")
If WSCal.Range("Rx1").Value = "" Then
WSCal.Range("RxRange,RxAnsRange").ClearContents
Set RxTarget = WSCal.Range("Rx1")
ElseIf WSCal.Range("Rx12").Value = "x" Then
Set RxTarget = WSCal.Range("Rx12")
ElseIf WSCal.Range("Rx11").Value = "x" Then
Set RxTarget = WSCal.Range("Rx11")
ElseIf WSCal.Range("Rx10").Value = "x" Then
Set RxTarget = WSCal.Range("10")
ElseIf WSCal.Range("Rx9").Value = "x" Then
Set RxTarget = WSCal.Range("Rx9")
ElseIf WSCal.Range("Rx8").Value = "x" Then
Set RxTarget = WSCal.Range("Rx8")
ElseIf WSCal.Range("Rx7").Value = "x" Then
Set RxTarget = WSCal.Range("Rx7")
ElseIf WSCal.Range("Rx6").Value = "x" Then
Set RxTarget = WSCal.Range("Rx6")
ElseIf WSCal.Range("Rx5").Value = "x" Then
Set RxTarget = WSCal.Range("Rx5")
ElseIf WSCal.Range("Rx4").Value = "x" Then
Set RxTarget = WSCal.Range("Rx4")
ElseIf WSCal.Range("Rx3").Value = "x" Then
Set RxTarget = WSCal.Range("Rx3")
ElseIf WSCal.Range("Rx2").Value = "x" Then
Set RxTarget = WSCal.Range("Rx2")
End If

usfRxOn.frmRxOn.Caption = RxTarget.Offset(0, -1).Value

With usfRxOn
If .frmRxOn.Caption < "Medication 1:" Then
.chbxRxOnNa.Enabled = False
.cmbCont.Enabled = True
.cmbuNextRx.Enabled = True
.cmbRestart.Enabled = True
Else
.chbxRxOnNa.Enabled = True
.cmbCont.Enabled = False
.cmbuNextRx.Enabled = False
.cmbRestart.Enabled = False
End If
.lblRxOn.Caption = WSCal.Range("RxOnQ").Value <<<<error here
End With
Application.ScreenUpdating = False
End Sub

--
Thanks
Shawn




Shawn
VBA Run time error 91
 
I figured this out.
--
Thanks
Shawn


"Shawn" wrote:

I noted the error in the wrong place. It should have been on the line of
code starting with "usfRxOn.frmRxOn.Caption..." right after the ElseIf
section.
--
Thanks
Shawn


"Bob Phillips" wrote:

Not clear because RxOnQ is not referenced elsewhere, but if it is a range
name that's fine, but if it is a range object it should be

.lblRxOn.Caption = RxOnQ.Value


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Shawn" wrote in message
...
Below is my code. I need two things: 1) a suggestion on how to make the
code
more efficient and 2) an explaination why I am getting a "Run-time error
'91': Object variable or With block variable not set" at the indicated
spot.

Public Sub UserForm_Activate()
Set WSCal = Sheets("Cal")
If WSCal.Range("Rx1").Value = "" Then
WSCal.Range("RxRange,RxAnsRange").ClearContents
Set RxTarget = WSCal.Range("Rx1")
ElseIf WSCal.Range("Rx12").Value = "x" Then
Set RxTarget = WSCal.Range("Rx12")
ElseIf WSCal.Range("Rx11").Value = "x" Then
Set RxTarget = WSCal.Range("Rx11")
ElseIf WSCal.Range("Rx10").Value = "x" Then
Set RxTarget = WSCal.Range("10")
ElseIf WSCal.Range("Rx9").Value = "x" Then
Set RxTarget = WSCal.Range("Rx9")
ElseIf WSCal.Range("Rx8").Value = "x" Then
Set RxTarget = WSCal.Range("Rx8")
ElseIf WSCal.Range("Rx7").Value = "x" Then
Set RxTarget = WSCal.Range("Rx7")
ElseIf WSCal.Range("Rx6").Value = "x" Then
Set RxTarget = WSCal.Range("Rx6")
ElseIf WSCal.Range("Rx5").Value = "x" Then
Set RxTarget = WSCal.Range("Rx5")
ElseIf WSCal.Range("Rx4").Value = "x" Then
Set RxTarget = WSCal.Range("Rx4")
ElseIf WSCal.Range("Rx3").Value = "x" Then
Set RxTarget = WSCal.Range("Rx3")
ElseIf WSCal.Range("Rx2").Value = "x" Then
Set RxTarget = WSCal.Range("Rx2")
End If

usfRxOn.frmRxOn.Caption = RxTarget.Offset(0, -1).Value

With usfRxOn
If .frmRxOn.Caption < "Medication 1:" Then
.chbxRxOnNa.Enabled = False
.cmbCont.Enabled = True
.cmbuNextRx.Enabled = True
.cmbRestart.Enabled = True
Else
.chbxRxOnNa.Enabled = True
.cmbCont.Enabled = False
.cmbuNextRx.Enabled = False
.cmbRestart.Enabled = False
End If
.lblRxOn.Caption = WSCal.Range("RxOnQ").Value <<<<error here
End With
Application.ScreenUpdating = False
End Sub

--
Thanks
Shawn




Dana DeLouis
VBA Run time error 91
 
1) a suggestion on how to make the code more efficient..

This is not really complete, but are there any ideas here that could help?
What I'm thinking is that there is a less chance of a typo with something
like this:

For example, I think it's hard to spot this error, where I think you meant
Range("Rx10")
Set RxTarget = WSCal.Range("10")

Sub Demo()
Dim Cell As Range
Const k As String = "x"

For Each Cell In Range("RX2:RX12")
If Cell.Value = k Then
Set RxTarget = Cell
Exit For
End If
Next Cell
End Sub

--
HTH :)
Dana DeLouis
Windows XP & Excel 2007


"Shawn" wrote in message
...
Below is my code. I need two things: 1) a suggestion on how to make the
code
more efficient and 2) an explaination why I am getting a "Run-time error
'91': Object variable or With block variable not set" at the indicated
spot.

Public Sub UserForm_Activate()
Set WSCal = Sheets("Cal")
If WSCal.Range("Rx1").Value = "" Then
WSCal.Range("RxRange,RxAnsRange").ClearContents
Set RxTarget = WSCal.Range("Rx1")
ElseIf WSCal.Range("Rx12").Value = "x" Then
Set RxTarget = WSCal.Range("Rx12")
ElseIf WSCal.Range("Rx11").Value = "x" Then
Set RxTarget = WSCal.Range("Rx11")
ElseIf WSCal.Range("Rx10").Value = "x" Then
Set RxTarget = WSCal.Range("10")
ElseIf WSCal.Range("Rx9").Value = "x" Then
Set RxTarget = WSCal.Range("Rx9")
ElseIf WSCal.Range("Rx8").Value = "x" Then
Set RxTarget = WSCal.Range("Rx8")
ElseIf WSCal.Range("Rx7").Value = "x" Then
Set RxTarget = WSCal.Range("Rx7")
ElseIf WSCal.Range("Rx6").Value = "x" Then
Set RxTarget = WSCal.Range("Rx6")
ElseIf WSCal.Range("Rx5").Value = "x" Then
Set RxTarget = WSCal.Range("Rx5")
ElseIf WSCal.Range("Rx4").Value = "x" Then
Set RxTarget = WSCal.Range("Rx4")
ElseIf WSCal.Range("Rx3").Value = "x" Then
Set RxTarget = WSCal.Range("Rx3")
ElseIf WSCal.Range("Rx2").Value = "x" Then
Set RxTarget = WSCal.Range("Rx2")
End If

usfRxOn.frmRxOn.Caption = RxTarget.Offset(0, -1).Value

With usfRxOn
If .frmRxOn.Caption < "Medication 1:" Then
.chbxRxOnNa.Enabled = False
.cmbCont.Enabled = True
.cmbuNextRx.Enabled = True
.cmbRestart.Enabled = True
Else
.chbxRxOnNa.Enabled = True
.cmbCont.Enabled = False
.cmbuNextRx.Enabled = False
.cmbRestart.Enabled = False
End If
.lblRxOn.Caption = WSCal.Range("RxOnQ").Value <<<<error here
End With
Application.ScreenUpdating = False
End Sub

--
Thanks
Shawn




All times are GMT +1. The time now is 07:38 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com