VBA Run time error 91

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

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

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)

 

[Guest]

I figured this out.
--
Thanks
Shawn

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.

 

[Dana DeLouis]

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