Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > VBA Run time error 91
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 271
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 271
Default 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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 271
Default 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



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 947
Default 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


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Visual Basic Error Run Time Error, Type Mismatch Meg Partridge Excel Discussion (Misc queries) 12 September 10th 08 06:10 PM
Run Time Error 1004: Application or Object Defined Error BEEJAY Excel Programming 4 October 18th 06 04:19 PM
Conditional Formatting - Run Time Error '13' Type Mismatch Error ksp Excel Programming 0 July 11th 06 07:06 AM
run-time error '1004': Application-defined or object-deifined error [email protected] Excel Programming 5 August 10th 05 09:39 PM
Befuddled with For Next Loop ------ Run - Time Error '13' Type Mismatch Error rdavis7408 Excel Programming 1 August 25th 04 03:54 AM


All times are GMT +1. The time now is 09:54 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement