Runtime Error 91 Object variable or With block variable not set.
 

Using VBA 6.5, I receive the runtime error when running FormatQtrAnalysis.
I know it has to do something with the way I have set up the class
CSTIAnalysis.
Problem is I do not know what that something is.
Is there any suggestions as to what I am overlooking?
Any suggestions as to where to look at will be appreciated.
(Of course, an answer will Greatly Appreciated!)
From STIOptions Module

Sub FormatQtrAnalysis()
Dim csaQtrAnalysis As CSTIAnalysis
Set csaQtrAnalysis = New CSTIAnalysis
csaQtrAnalysis.Worksheet = ActiveSheet
csaQtrAnalysis.FormatHeader 'When pressing Debug, this line is
highlighted
End Sub

From CSTIAnalysis Class

Private pWorksheet As Worksheet

Public Property Get Worksheet() As Worksheet
Worksheet = pWorksheet
End Property
Public Property Let Worksheet(vNewValue As Worksheet)
Set pWorksheet = vNewValue
End Property

Public Sub FormatHeader()
Dim rHeader As Range
Dim iColCount As Integer
iColCount = pWorksheet.UsedRange.Columns.Count
rHeader = pWorksheet.Range(Cells(1, 1), Cells(1, iColCount))
rHeader.MergeCells = False
With rHeader
.Merge
.EntireRow.AutoFit
.BorderAround Weight:=xlThick, ColorIndex:=1
End With
End Sub

Runtime Error 91 Object variable or With block variable not set.
 

I suspect your problem is this line:
rHeader = pWorksheet.Range(Cells(1, 1), Cells(1, iColCount))

Try this:
Set rHeader = pWorksheet.Range(Cells(1, 1), Cells(1, iColCount))

since rHeader is a Range object.

Hth,
Merjet

Runtime Error 91 Object variable or With block variable not set.
 

or even

Set rHeader = Range(pWorksheet.Cells(1, 1), pWorksheet.Cells(1,
iColCount))


--
HTH

Bob

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

"merjet" wrote in message
...
I suspect your problem is this line:
rHeader = pWorksheet.Range(Cells(1, 1), Cells(1, iColCount))

Try this:
Set rHeader = pWorksheet.Range(Cells(1, 1), Cells(1, iColCount))

since rHeader is a Range object.

Hth,
Merjet

Runtime Error 91 Object variable or With block variable not se
 

That worked, thank you.
2 questions: (If need a separate thread please let me know.)
1) Why is Set used in this situation?
(Unless you know a good book or website to read, then I will take the time
to research instead of leaching.)
2) I tried subbing Me.Worksheet for pWorksheet and it does not work.
(Same as above)

Thank you

"merjet" wrote:

I suspect your problem is this line:
rHeader = pWorksheet.Range(Cells(1, 1), Cells(1, iColCount))

Try this:
Set rHeader = pWorksheet.Range(Cells(1, 1), Cells(1, iColCount))

since rHeader is a Range object.

Hth,
Merjet

Runtime Error 91 Object variable or With block variable not set.
 

I'd qualify all the range objects:

with pworksheet
set rHeader = .Range(.Cells(1, 1), .Cells(1, iColCount))
end with



Tim wrote:

Using VBA 6.5, I receive the runtime error when running FormatQtrAnalysis.
I know it has to do something with the way I have set up the class
CSTIAnalysis.
Problem is I do not know what that something is.
Is there any suggestions as to what I am overlooking?
Any suggestions as to where to look at will be appreciated.
(Of course, an answer will Greatly Appreciated!)
From STIOptions Module

Sub FormatQtrAnalysis()
Dim csaQtrAnalysis As CSTIAnalysis
Set csaQtrAnalysis = New CSTIAnalysis
csaQtrAnalysis.Worksheet = ActiveSheet
csaQtrAnalysis.FormatHeader 'When pressing Debug, this line is
highlighted
End Sub

From CSTIAnalysis Class

Private pWorksheet As Worksheet

Public Property Get Worksheet() As Worksheet
Worksheet = pWorksheet
End Property
Public Property Let Worksheet(vNewValue As Worksheet)
Set pWorksheet = vNewValue
End Property

Public Sub FormatHeader()
Dim rHeader As Range
Dim iColCount As Integer
iColCount = pWorksheet.UsedRange.Columns.Count
rHeader = pWorksheet.Range(Cells(1, 1), Cells(1, iColCount))
rHeader.MergeCells = False
With rHeader
.Merge
.EntireRow.AutoFit
.BorderAround Weight:=xlThick, ColorIndex:=1
End With
End Sub

--

Dave Peterson

Runtime Error 91 Object variable or With block variable not set.
 

I don't think you have to qualify the Range, because the qualified Cells is
linking it to the worksheet, the Range is the Range object not the Range
property.

--
HTH

Bob

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

"Dave Peterson" wrote in message
...
I'd qualify all the range objects:

with pworksheet
set rHeader = .Range(.Cells(1, 1), .Cells(1, iColCount))
end with



Tim wrote:

Using VBA 6.5, I receive the runtime error when running
FormatQtrAnalysis.
I know it has to do something with the way I have set up the class
CSTIAnalysis.
Problem is I do not know what that something is.
Is there any suggestions as to what I am overlooking?
Any suggestions as to where to look at will be appreciated.
(Of course, an answer will Greatly Appreciated!)
From STIOptions Module

Sub FormatQtrAnalysis()
Dim csaQtrAnalysis As CSTIAnalysis
Set csaQtrAnalysis = New CSTIAnalysis
csaQtrAnalysis.Worksheet = ActiveSheet
csaQtrAnalysis.FormatHeader 'When pressing Debug, this line is
highlighted
End Sub

From CSTIAnalysis Class

Private pWorksheet As Worksheet

Public Property Get Worksheet() As Worksheet
Worksheet = pWorksheet
End Property
Public Property Let Worksheet(vNewValue As Worksheet)
Set pWorksheet = vNewValue
End Property

Public Sub FormatHeader()
Dim rHeader As Range
Dim iColCount As Integer
iColCount = pWorksheet.UsedRange.Columns.Count
rHeader = pWorksheet.Range(Cells(1, 1), Cells(1, iColCount))
rHeader.MergeCells = False
With rHeader
.Merge
.EntireRow.AutoFit
.BorderAround Weight:=xlThick, ColorIndex:=1
End With
End Sub

--

Dave Peterson

Runtime Error 91 Object variable or With block variable not se
 

You are using Set because it is an object variable you are dealing with.
With normal variable you load them with Let, but VBA defaults that so you
can get away without using it, but you must use Set with objects.

--
HTH

Bob

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

"Tim" wrote in message
...
That worked, thank you.
2 questions: (If need a separate thread please let me know.)
1) Why is Set used in this situation?
(Unless you know a good book or website to read, then I will take the time
to research instead of leaching.)
2) I tried subbing Me.Worksheet for pWorksheet and it does not work.
(Same as above)

Thank you

"merjet" wrote:

I suspect your problem is this line:
rHeader = pWorksheet.Range(Cells(1, 1), Cells(1, iColCount))

Try this:
Set rHeader = pWorksheet.Range(Cells(1, 1), Cells(1, iColCount))

since rHeader is a Range object.

Hth,
Merjet

Runtime Error 91 Object variable or With block variable not se
 

Thanks, that makes sense.

"Bob Phillips" wrote:

You are using Set because it is an object variable you are dealing with.
With normal variable you load them with Let, but VBA defaults that so you
can get away without using it, but you must use Set with objects.

--
HTH

Bob

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

"Tim" wrote in message
...
That worked, thank you.
2 questions: (If need a separate thread please let me know.)
1) Why is Set used in this situation?
(Unless you know a good book or website to read, then I will take the time
to research instead of leaching.)
2) I tried subbing Me.Worksheet for pWorksheet and it does not work.
(Same as above)

Thank you

"merjet" wrote:

I suspect your problem is this line:
rHeader = pWorksheet.Range(Cells(1, 1), Cells(1, iColCount))

Try this:
Set rHeader = pWorksheet.Range(Cells(1, 1), Cells(1, iColCount))

since rHeader is a Range object.

Hth,
Merjet

Runtime Error 91 Object variable or With block variable not set.
 

It depends on where the code is. If the code is behind a worksheet (not
pworksheet), then not qualifying it would cause trouble.


Bob Phillips wrote:

I don't think you have to qualify the Range, because the qualified Cells is
linking it to the worksheet, the Range is the Range object not the Range
property.

--
HTH

Bob

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

"Dave Peterson" wrote in message
...
I'd qualify all the range objects:

with pworksheet
set rHeader = .Range(.Cells(1, 1), .Cells(1, iColCount))
end with



Tim wrote:

Using VBA 6.5, I receive the runtime error when running
FormatQtrAnalysis.
I know it has to do something with the way I have set up the class
CSTIAnalysis.
Problem is I do not know what that something is.
Is there any suggestions as to what I am overlooking?
Any suggestions as to where to look at will be appreciated.
(Of course, an answer will Greatly Appreciated!)
From STIOptions Module

Sub FormatQtrAnalysis()
Dim csaQtrAnalysis As CSTIAnalysis
Set csaQtrAnalysis = New CSTIAnalysis
csaQtrAnalysis.Worksheet = ActiveSheet
csaQtrAnalysis.FormatHeader 'When pressing Debug, this line is
highlighted
End Sub

From CSTIAnalysis Class

Private pWorksheet As Worksheet

Public Property Get Worksheet() As Worksheet
Worksheet = pWorksheet
End Property
Public Property Let Worksheet(vNewValue As Worksheet)
Set pWorksheet = vNewValue
End Property

Public Sub FormatHeader()
Dim rHeader As Range
Dim iColCount As Integer
iColCount = pWorksheet.UsedRange.Columns.Count
rHeader = pWorksheet.Range(Cells(1, 1), Cells(1, iColCount))
rHeader.MergeCells = False
With rHeader
.Merge
.EntireRow.AutoFit
.BorderAround Weight:=xlThick, ColorIndex:=1
End With
End Sub

--

Dave Peterson

--

Dave Peterson

Runtime Error 91 Object variable or With block variable not set.
 

I didn't recall that for this questioner, the code was in a class module.

I'd still qualify my ranges, though.

Dave Peterson wrote:

It depends on where the code is. If the code is behind a worksheet (not
pworksheet), then not qualifying it would cause trouble.

Bob Phillips wrote:

I don't think you have to qualify the Range, because the qualified Cells is
linking it to the worksheet, the Range is the Range object not the Range
property.

--
HTH

Bob

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

"Dave Peterson" wrote in message
...
I'd qualify all the range objects:

with pworksheet
set rHeader = .Range(.Cells(1, 1), .Cells(1, iColCount))
end with



Tim wrote:

Using VBA 6.5, I receive the runtime error when running
FormatQtrAnalysis.
I know it has to do something with the way I have set up the class
CSTIAnalysis.
Problem is I do not know what that something is.
Is there any suggestions as to what I am overlooking?
Any suggestions as to where to look at will be appreciated.
(Of course, an answer will Greatly Appreciated!)
From STIOptions Module

Sub FormatQtrAnalysis()
Dim csaQtrAnalysis As CSTIAnalysis
Set csaQtrAnalysis = New CSTIAnalysis
csaQtrAnalysis.Worksheet = ActiveSheet
csaQtrAnalysis.FormatHeader 'When pressing Debug, this line is
highlighted
End Sub

From CSTIAnalysis Class

Private pWorksheet As Worksheet

Public Property Get Worksheet() As Worksheet
Worksheet = pWorksheet
End Property
Public Property Let Worksheet(vNewValue As Worksheet)
Set pWorksheet = vNewValue
End Property

Public Sub FormatHeader()
Dim rHeader As Range
Dim iColCount As Integer
iColCount = pWorksheet.UsedRange.Columns.Count
rHeader = pWorksheet.Range(Cells(1, 1), Cells(1, iColCount))
rHeader.MergeCells = False
With rHeader
.Merge
.EntireRow.AutoFit
.BorderAround Weight:=xlThick, ColorIndex:=1
End With
End Sub

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson