Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
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) | |||
Need help with Error 'object variable or with block variable not set' | Excel Programming | |||
Run-Time error '91': Object variable of 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 |