Calling a subroutine gives object variable not set

B

Bill

Hi,
The code is in a module. Using access 2000. Module is
called up from a command button on the form (called
calculate). Calls up the Fuction "tube Select" of which
Sub Price_2way() ' pricing subroutine common

is called up.

I took out the common subroutine in a function which is
called up in a "Case Select" .
At the start I open an Excel worksheet,

With xlapp
.Visible = False
Set xlbook = .Workbooks.Open("C:\My
Documents\2WAYPRICES.xls")
Set xlsheet = xlbook.Worksheets(2)
xlbook.Worksheets(2).Activate
End With

The Case select is based on a control value on an Acces
form - like,

Select Case Forms![tubeselectform]!Frame110 ' tube size
Me![tube size].

' each number in Case IS - represents a
diameter of pipe in millimetres.

Case Is = 50
Set rng1 = xlsheet.Range("b4:b75")
xlsheet.Range("b4").Activate

Call Price_2way

Case Is = 65
Set rng2 = xlsheet.Range("c4:c75")
xlsheet.Range("c4").Activate

Call Price_2way

Price two way is common so I extracted it out to give,

Sub Price_2way() ' pricing subroutine common

Dim rng11 As Range
Dim rng21 As Range
Dim rng31 As Range
Dim rng41 As Range
Dim c As Integer
Dim res As Variant
Dim res2 As Variant
Dim res3 As Variant

'look at the material option
If (Forms![tubeselectform]![Grade] = 1)
Then 'non sanitary mild steel
c = -3
ElseIf (Forms![tubeselectform]!
[Grade] = 2) Then ' non sanitary 304
c = -4
ElseIf (Forms![tubeselectform]!
[Grade] = 3) Then '316 stst option.
c = -8
Else
'do nothing
End If
Set rng11 = ActiveCell.Offset(c,
0).Value
res = ""
res = rng11
'rng11 is the value to be copied to
field Forms![tubeselectform]![Price_Tube_Size)
Forms![tubeselectform]!
[Price_Tube_Size] = res

'now look at the Cover option
If (Forms![tubeselectform]![Cover] = 2)
Then 'cover is 304 ss
c = -30
ElseIf (Forms![tubeselectform]!
[Cover] = 3) Then ' cover is 316 ss
c = -31
Else
c = -1
End If
Set rng21 = xlsheet.ActiveCell.Offset
(c, 0).Value
res2 = ""
res2 = rng21
'rng21 is the value to be copied to
field Forms![tubeselectform]![Price_Cover_Option]and will
be numeric.
Forms![tubeselectform]!
[Price_Cover_Option] = res2

'now look at the Controller option
If (Forms![tubeselectform]![DC] = True)
Then
c = -29
ElseIf (Forms![tubeselectform]![DC] =
False) Then
c = -28
Else
'do nothing
End If
Set rng31 = xlsheet.ActiveCell.Offset
(c, 0).Value
res3 = ""
res3 = rng31
'rng31 is the value to be copied to
field Forms![tubeselectform]![Price_Prologic)
Forms![tubeselectform]!
[Price_Prologic] = res3
End Sub

When it runs I get an error at the line,

Set rng11 = ActiveCell.Offset(c, 0).Value

saying "Object variable or With Block Variable not set.

In the "case Is" I activate the active cell. In the sub
routine I offset to get a value - or do I?????.

Where have I gone wrong????.
Thanks In advance
Bill
 
A

Alex Ivanov

Access generally have no idea what ActiveCell is. Try to give it a fully
qualified name, ie
Set rng11 = xlsheet.ActiveCell.Offset(c, 0).Value

I would recommend to add Option Explicit as a first line in you code module
and declare all your variables.
It will make debugging much easier.

--
Please reply to NG only. The email address is not monitored.

Alex.

Bill said:
Hi,
The code is in a module. Using access 2000. Module is
called up from a command button on the form (called
calculate). Calls up the Fuction "tube Select" of which
Sub Price_2way() ' pricing subroutine common

is called up.

I took out the common subroutine in a function which is
called up in a "Case Select" .
At the start I open an Excel worksheet,

With xlapp
.Visible = False
Set xlbook = .Workbooks.Open("C:\My
Documents\2WAYPRICES.xls")
Set xlsheet = xlbook.Worksheets(2)
xlbook.Worksheets(2).Activate
End With

The Case select is based on a control value on an Acces
form - like,

Select Case Forms![tubeselectform]!Frame110 ' tube size
Me![tube size].

' each number in Case IS - represents a
diameter of pipe in millimetres.

Case Is = 50
Set rng1 = xlsheet.Range("b4:b75")
xlsheet.Range("b4").Activate

Call Price_2way

Case Is = 65
Set rng2 = xlsheet.Range("c4:c75")
xlsheet.Range("c4").Activate

Call Price_2way

Price two way is common so I extracted it out to give,

Sub Price_2way() ' pricing subroutine common

Dim rng11 As Range
Dim rng21 As Range
Dim rng31 As Range
Dim rng41 As Range
Dim c As Integer
Dim res As Variant
Dim res2 As Variant
Dim res3 As Variant

'look at the material option
If (Forms![tubeselectform]![Grade] = 1)
Then 'non sanitary mild steel
c = -3
ElseIf (Forms![tubeselectform]!
[Grade] = 2) Then ' non sanitary 304
c = -4
ElseIf (Forms![tubeselectform]!
[Grade] = 3) Then '316 stst option.
c = -8
Else
'do nothing
End If
Set rng11 = ActiveCell.Offset(c,
0).Value
res = ""
res = rng11
'rng11 is the value to be copied to
field Forms![tubeselectform]![Price_Tube_Size)
Forms![tubeselectform]!
[Price_Tube_Size] = res

'now look at the Cover option
If (Forms![tubeselectform]![Cover] = 2)
Then 'cover is 304 ss
c = -30
ElseIf (Forms![tubeselectform]!
[Cover] = 3) Then ' cover is 316 ss
c = -31
Else
c = -1
End If
Set rng21 = xlsheet.ActiveCell.Offset
(c, 0).Value
res2 = ""
res2 = rng21
'rng21 is the value to be copied to
field Forms![tubeselectform]![Price_Cover_Option]and will
be numeric.
Forms![tubeselectform]!
[Price_Cover_Option] = res2

'now look at the Controller option
If (Forms![tubeselectform]![DC] = True)
Then
c = -29
ElseIf (Forms![tubeselectform]![DC] =
False) Then
c = -28
Else
'do nothing
End If
Set rng31 = xlsheet.ActiveCell.Offset
(c, 0).Value
res3 = ""
res3 = rng31
'rng31 is the value to be copied to
field Forms![tubeselectform]![Price_Prologic)
Forms![tubeselectform]!
[Price_Prologic] = res3
End Sub

When it runs I get an error at the line,

Set rng11 = ActiveCell.Offset(c, 0).Value

saying "Object variable or With Block Variable not set.

In the "case Is" I activate the active cell. In the sub
routine I offset to get a value - or do I?????.

Where have I gone wrong????.
Thanks In advance
Bill
Click to expand...
 
B

Bill

Thanks Alex,
The Module is linked to a form.
I have used option explicit.
Sorry but I do not understand how to give the ActiveCell
a fully qualified name for the subroutine.
The active cell is called up in the "Case Is" but I do
not understand how to pass this to the subroutine?
If I run the debugg I do not get the error, only when the
code runs does - an error at the line,
Please expand on a fully qualified name.
Kind Regards
Bill

-----Original Message-----
Access generally have no idea what ActiveCell is. Try to give it a fully
qualified name, ie
Set rng11 = xlsheet.ActiveCell.Offset(c, 0).Value

I would recommend to add Option Explicit as a first line in you code module
and declare all your variables.
It will make debugging much easier.

--
Please reply to NG only. The email address is not monitored.

Alex.

Hi,
The code is in a module. Using access 2000. Module is
called up from a command button on the form (called
calculate). Calls up the Fuction "tube Select" of which
Sub Price_2way() ' pricing subroutine common

is called up.

I took out the common subroutine in a function which is
called up in a "Case Select" .
At the start I open an Excel worksheet,

With xlapp
.Visible = False
Set xlbook = .Workbooks.Open("C:\My
Documents\2WAYPRICES.xls")
Set xlsheet = xlbook.Worksheets(2)
xlbook.Worksheets(2).Activate
End With

The Case select is based on a control value on an Acces
form - like,

Select Case Forms![tubeselectform]!Frame110 ' tube size
Me![tube size].

' each number in Case IS - represents a
diameter of pipe in millimetres.

Case Is = 50
Set rng1 = xlsheet.Range("b4:b75")
xlsheet.Range("b4").Activate

Call Price_2way

Case Is = 65
Set rng2 = xlsheet.Range("c4:c75")
xlsheet.Range("c4").Activate

Call Price_2way

Price two way is common so I extracted it out to give,

Sub Price_2way() ' pricing subroutine common

Dim rng11 As Range
Dim rng21 As Range
Dim rng31 As Range
Dim rng41 As Range
Dim c As Integer
Dim res As Variant
Dim res2 As Variant
Dim res3 As Variant

'look at the material option
If (Forms![tubeselectform]![Grade] = 1)
Then 'non sanitary mild steel
c = -3
ElseIf (Forms![tubeselectform]!
[Grade] = 2) Then ' non sanitary 304
c = -4
ElseIf (Forms![tubeselectform]!
[Grade] = 3) Then '316 stst option.
c = -8
Else
'do nothing
End If
Set rng11 = ActiveCell.Offset(c,
0).Value
res = ""
res = rng11
'rng11 is the value to be copied to
field Forms![tubeselectform]![Price_Tube_Size)
Forms![tubeselectform]!
[Price_Tube_Size] = res

'now look at the Cover option
If (Forms![tubeselectform]![Cover] = 2)
Then 'cover is 304 ss
c = -30
ElseIf (Forms![tubeselectform]!
[Cover] = 3) Then ' cover is 316 ss
c = -31
Else
c = -1
End If
Set rng21 = xlsheet.ActiveCell.Offset
(c, 0).Value
res2 = ""
res2 = rng21
'rng21 is the value to be copied to
field Forms![tubeselectform]![Price_Cover_Option]and will
be numeric.
Forms![tubeselectform]!
[Price_Cover_Option] = res2

'now look at the Controller option
If (Forms![tubeselectform]![DC] = True)
Then
c = -29
ElseIf (Forms![tubeselectform]! [DC] =
False) Then
c = -28
Else
'do nothing
End If
Set rng31 = xlsheet.ActiveCell.Offset
(c, 0).Value
res3 = ""
res3 = rng31
'rng31 is the value to be copied to
field Forms![tubeselectform]![Price_Prologic)
Forms![tubeselectform]!
[Price_Prologic] = res3
End Sub
In the "case Is" I activate the active cell. In the sub
routine I offset to get a value - or do I?????.

Where have I gone wrong????.
Thanks In advance
Bill
Click to expand...


.
Click to expand...
 
A

Alex Ivanov

Bill,
I have already given you an example. As I see from your code snippet, you
have already done it right in other cases, but here you forgot to prefix it
with xlsheet.

Side note, not related to the problem:
Set xlsheet = xlbook.Worksheets(2)
xlbook.Worksheets(2).Activate ' <- you should use xlsheet.Activate
instead

HTH

Alex.


Bill said:
Thanks Alex,
The Module is linked to a form.
I have used option explicit.
Sorry but I do not understand how to give the ActiveCell
a fully qualified name for the subroutine.
The active cell is called up in the "Case Is" but I do
not understand how to pass this to the subroutine?
If I run the debugg I do not get the error, only when the
code runs does - an error at the line,
Please expand on a fully qualified name.
Kind Regards
Bill

-----Original Message-----
Access generally have no idea what ActiveCell is. Try to give it a fully
qualified name, ie
Set rng11 = xlsheet.ActiveCell.Offset(c, 0).Value

I would recommend to add Option Explicit as a first line in you code module
and declare all your variables.
It will make debugging much easier.

--
Please reply to NG only. The email address is not monitored.

Alex.

Hi,
The code is in a module. Using access 2000. Module is
called up from a command button on the form (called
calculate). Calls up the Fuction "tube Select" of which
Sub Price_2way() ' pricing subroutine common

is called up.

I took out the common subroutine in a function which is
called up in a "Case Select" .
At the start I open an Excel worksheet,

With xlapp
.Visible = False
Set xlbook = .Workbooks.Open("C:\My
Documents\2WAYPRICES.xls")
Set xlsheet = xlbook.Worksheets(2)
xlbook.Worksheets(2).Activate
End With

The Case select is based on a control value on an Acces
form - like,

Select Case Forms![tubeselectform]!Frame110 ' tube size
Me![tube size].

' each number in Case IS - represents a
diameter of pipe in millimetres.

Case Is = 50
Set rng1 = xlsheet.Range("b4:b75")
xlsheet.Range("b4").Activate

Call Price_2way

Case Is = 65
Set rng2 = xlsheet.Range("c4:c75")
xlsheet.Range("c4").Activate

Call Price_2way

Price two way is common so I extracted it out to give,

Sub Price_2way() ' pricing subroutine common

Dim rng11 As Range
Dim rng21 As Range
Dim rng31 As Range
Dim rng41 As Range
Dim c As Integer
Dim res As Variant
Dim res2 As Variant
Dim res3 As Variant

'look at the material option
If (Forms![tubeselectform]![Grade] = 1)
Then 'non sanitary mild steel
c = -3
ElseIf (Forms![tubeselectform]!
[Grade] = 2) Then ' non sanitary 304
c = -4
ElseIf (Forms![tubeselectform]!
[Grade] = 3) Then '316 stst option.
c = -8
Else
'do nothing
End If
Set rng11 = ActiveCell.Offset(c,
0).Value
res = ""
res = rng11
'rng11 is the value to be copied to
field Forms![tubeselectform]![Price_Tube_Size)
Forms![tubeselectform]!
[Price_Tube_Size] = res

'now look at the Cover option
If (Forms![tubeselectform]![Cover] = 2)
Then 'cover is 304 ss
c = -30
ElseIf (Forms![tubeselectform]!
[Cover] = 3) Then ' cover is 316 ss
c = -31
Else
c = -1
End If
Set rng21 = xlsheet.ActiveCell.Offset
(c, 0).Value
res2 = ""
res2 = rng21
'rng21 is the value to be copied to
field Forms![tubeselectform]![Price_Cover_Option]and will
be numeric.
Forms![tubeselectform]!
[Price_Cover_Option] = res2

'now look at the Controller option
If (Forms![tubeselectform]![DC] = True)
Then
c = -29
ElseIf (Forms![tubeselectform]! [DC] =
False) Then
c = -28
Else
'do nothing
End If
Set rng31 = xlsheet.ActiveCell.Offset
(c, 0).Value
res3 = ""
res3 = rng31
'rng31 is the value to be copied to
field Forms![tubeselectform]![Price_Prologic)
Forms![tubeselectform]!
[Price_Prologic] = res3
End Sub


In the "case Is" I activate the active cell. In the sub
routine I offset to get a value - or do I?????.

Where have I gone wrong????.
Thanks In advance
Bill
Click to expand...


.
Click to expand...
Click to expand...
 
B

Bill

Thanks Ivan,
Further to my predicament even with the refernece as
suggested I still get the same error or if I shift the
subroutine to the main body, errors suggesting that the
object etc doesn't support this action.
Having said all that could I possibly be asking the code
to do something I shouldn't. what I mean is i'm running
the code from access to open an excel spread sheet at a
particular worksheet. i'm defining the range and
dependent on the input from a control on the form find a
particular cell value on the spreadsheet (offset form
active cell) and make this value the result or input of a
control on the form. I'm adding numbers from the
spreadsheet on the form so to speak.maybe I should copy
the cell value to clip board and then paste to the
control, the way I was thinking was to let the value on
the sheet = something in code then saying that the
control in the access form = this value.
anyway some ramblings - thanks for the input.
Warm regards
bill.
-----Original Message-----
Bill,
I have already given you an example. As I see from your code snippet, you
have already done it right in other cases, but here you forgot to prefix it
with xlsheet.

Side note, not related to the problem:
Set xlsheet = xlbook.Worksheets(2)
xlbook.Worksheets(2).Activate ' <- you should use xlsheet.Activate
instead

HTH

Alex.


Thanks Alex,
The Module is linked to a form.
I have used option explicit.
Sorry but I do not understand how to give the ActiveCell
a fully qualified name for the subroutine.
The active cell is called up in the "Case Is" but I do
not understand how to pass this to the subroutine?
If I run the debugg I do not get the error, only when the
code runs does - an error at the line,
Set rng11 = ActiveCell.Offset(c, 0).Value

saying "Object variable or With Block Variable not
Click to expand...
set.

Please expand on a fully qualified name.
Kind Regards
Bill

-----Original Message-----
Access generally have no idea what ActiveCell is. Try
Click to expand...
to
give it a fully
qualified name, ie
Set rng11 = xlsheet.ActiveCell.Offset(c, 0).Value

I would recommend to add Option Explicit as a first
Click to expand...
line
in you code module
and declare all your variables.
It will make debugging much easier.

--
Please reply to NG only. The email address is not monitored.

Alex.

Hi,
The code is in a module. Using access 2000. Module is
called up from a command button on the form (called
calculate). Calls up the Fuction "tube Select" of which
Sub Price_2way() ' pricing subroutine common

is called up.

I took out the common subroutine in a function which is
called up in a "Case Select" .
At the start I open an Excel worksheet,

With xlapp
.Visible = False
Set xlbook = .Workbooks.Open("C:\My
Documents\2WAYPRICES.xls")
Set xlsheet = xlbook.Worksheets(2)
xlbook.Worksheets(2).Activate
End With

The Case select is based on a control value on an Acces
form - like,

Select Case Forms![tubeselectform]!Frame110 ' tube size
Me![tube size].

' each number in Case IS - represents a
diameter of pipe in millimetres.

Case Is = 50
Set rng1 = xlsheet.Range("b4:b75")
xlsheet.Range("b4").Activate

Call Price_2way

Case Is = 65
Set rng2 = xlsheet.Range("c4:c75")
xlsheet.Range("c4").Activate

Call Price_2way

Price two way is common so I extracted it out to give,

Sub Price_2way() ' pricing subroutine common

Dim rng11 As Range
Dim rng21 As Range
Dim rng31 As Range
Dim rng41 As Range
Dim c As Integer
Dim res As Variant
Dim res2 As Variant
Dim res3 As Variant

'look at the material option
If (Forms![tubeselectform]![Grade] = 1)
Then 'non sanitary mild steel
c = -3
ElseIf (Forms![tubeselectform]!
[Grade] = 2) Then ' non sanitary 304
c = -4
ElseIf (Forms![tubeselectform]!
[Grade] = 3) Then '316 stst option.
c = -8
Else
'do nothing
End If
Set rng11 = ActiveCell.Offset(c,
0).Value
res = ""
res = rng11
'rng11 is the value to be copied to
field Forms![tubeselectform]![Price_Tube_Size)
Forms![tubeselectform]!
[Price_Tube_Size] = res

'now look at the Cover option
If (Forms![tubeselectform]![Cover] = 2)
Then 'cover is 304 ss
c = -30
ElseIf (Forms![tubeselectform]!
[Cover] = 3) Then ' cover is 316 ss
c = -31
Else
c = -1
End If
Set rng21 = xlsheet.ActiveCell.Offset
(c, 0).Value
res2 = ""
res2 = rng21
'rng21 is the value to be copied to
field Forms![tubeselectform]![Price_Cover_Option]and will
be numeric.
Forms![tubeselectform]!
[Price_Cover_Option] = res2

'now look at the Controller option
If (Forms![tubeselectform]![DC] = True)
Then
c = -29
ElseIf (Forms![tubeselectform]! [DC] =
False) Then
c = -28
Else
'do nothing
End If
Set rng31 = xlsheet.ActiveCell.Offset
(c, 0).Value
res3 = ""
res3 = rng31
'rng31 is the value to be copied to
field Forms![tubeselectform]![Price_Prologic)
Forms![tubeselectform]!
[Price_Prologic] = res3
End Sub


In the "case Is" I activate the active cell. In the sub
routine I offset to get a value - or do I?????.

Where have I gone wrong????.
Thanks In advance
Bill


.
Click to expand...
Click to expand...


.
Click to expand...
 
A

Alex Ivanov

I believe your xlsheet goes out of scope when you call the Price_2way.
either move the declaration of xlsheet to outside of any routine
to give it a module-level scope or pass the xlsheet to the Price_2way as a
parameter.
Of course, in the later case you will need to change the declaration
of the procedure to accept the parameter:

Sub Price_2way(xlsheet as excel.worksheet) ' pricing subroutine common

HTH
--
Please reply to NG only. The email address is not monitored.

Alex.

Bill said:
Thanks Ivan,
Further to my predicament even with the refernece as
suggested I still get the same error or if I shift the
subroutine to the main body, errors suggesting that the
object etc doesn't support this action.
Having said all that could I possibly be asking the code
to do something I shouldn't. what I mean is i'm running
the code from access to open an excel spread sheet at a
particular worksheet. i'm defining the range and
dependent on the input from a control on the form find a
particular cell value on the spreadsheet (offset form
active cell) and make this value the result or input of a
control on the form. I'm adding numbers from the
spreadsheet on the form so to speak.maybe I should copy
the cell value to clip board and then paste to the
control, the way I was thinking was to let the value on
the sheet = something in code then saying that the
control in the access form = this value.
anyway some ramblings - thanks for the input.
Warm regards
bill.
-----Original Message-----
Bill,
I have already given you an example. As I see from your code snippet, you
have already done it right in other cases, but here you forgot to prefix it
with xlsheet.

Side note, not related to the problem:
Set xlsheet = xlbook.Worksheets(2)
xlbook.Worksheets(2).Activate ' <- you should use xlsheet.Activate
instead

HTH

Alex.


Thanks Alex,
The Module is linked to a form.
I have used option explicit.
Sorry but I do not understand how to give the ActiveCell
a fully qualified name for the subroutine.
The active cell is called up in the "Case Is" but I do
not understand how to pass this to the subroutine?
If I run the debugg I do not get the error, only when the
code runs does - an error at the line,

Set rng11 = ActiveCell.Offset(c, 0).Value

saying "Object variable or With Block Variable not set.

Please expand on a fully qualified name.
Kind Regards
Bill


-----Original Message-----
Access generally have no idea what ActiveCell is. Try to
give it a fully
qualified name, ie
Set rng11 = xlsheet.ActiveCell.Offset(c, 0).Value

I would recommend to add Option Explicit as a first line
in you code module
and declare all your variables.
It will make debugging much easier.

--
Please reply to NG only. The email address is not
monitored.

Alex.

message
Hi,
The code is in a module. Using access 2000. Module is
called up from a command button on the form (called
calculate). Calls up the Fuction "tube Select" of which
Sub Price_2way() ' pricing subroutine common

is called up.

I took out the common subroutine in a function which is
called up in a "Case Select" .
At the start I open an Excel worksheet,

With xlapp
.Visible = False
Set xlbook = .Workbooks.Open("C:\My
Documents\2WAYPRICES.xls")
Set xlsheet = xlbook.Worksheets(2)
xlbook.Worksheets(2).Activate
End With

The Case select is based on a control value on an Acces
form - like,

Select Case Forms![tubeselectform]!Frame110 ' tube size
Me![tube size].

' each number in Case IS - represents a
diameter of pipe in millimetres.

Case Is = 50
Set rng1 = xlsheet.Range("b4:b75")
xlsheet.Range("b4").Activate

Call Price_2way

Case Is = 65
Set rng2 = xlsheet.Range("c4:c75")
xlsheet.Range("c4").Activate

Call Price_2way

Price two way is common so I extracted it out to give,

Sub Price_2way() ' pricing subroutine common

Dim rng11 As Range
Dim rng21 As Range
Dim rng31 As Range
Dim rng41 As Range
Dim c As Integer
Dim res As Variant
Dim res2 As Variant
Dim res3 As Variant

'look at the material option
If (Forms![tubeselectform]![Grade] = 1)
Then 'non sanitary mild steel
c = -3
ElseIf (Forms![tubeselectform]!
[Grade] = 2) Then ' non sanitary 304
c = -4
ElseIf (Forms![tubeselectform]!
[Grade] = 3) Then '316 stst option.
c = -8
Else
'do nothing
End If
Set rng11 = ActiveCell.Offset(c,
0).Value
res = ""
res = rng11
'rng11 is the value to be copied to
field Forms![tubeselectform]![Price_Tube_Size)
Forms![tubeselectform]!
[Price_Tube_Size] = res

'now look at the Cover option
If (Forms![tubeselectform]![Cover] = 2)
Then 'cover is 304 ss
c = -30
ElseIf (Forms![tubeselectform]!
[Cover] = 3) Then ' cover is 316 ss
c = -31
Else
c = -1
End If
Set rng21 =
xlsheet.ActiveCell.Offset
(c, 0).Value
res2 = ""
res2 = rng21
'rng21 is the value to be copied to
field Forms![tubeselectform]![Price_Cover_Option]and
will
be numeric.
Forms![tubeselectform]!
[Price_Cover_Option] = res2

'now look at the Controller option
If (Forms![tubeselectform]![DC] = True)
Then
c = -29
ElseIf (Forms![tubeselectform]!
[DC] =
False) Then
c = -28
Else
'do nothing
End If
Set rng31 =
xlsheet.ActiveCell.Offset
(c, 0).Value
res3 = ""
res3 = rng31
'rng31 is the value to be copied to
field Forms![tubeselectform]![Price_Prologic)
Forms![tubeselectform]!
[Price_Prologic] = res3
End Sub


In the "case Is" I activate the active cell. In the sub
routine I offset to get a value - or do I?????.

Where have I gone wrong????.
Thanks In advance
Bill


.
Click to expand...


.
Click to expand...
Click to expand...
 
B

Bill

Thanks Alex,
Yes - I will work on that. The sheet going out of scope -
yes - Thanks.
Sorry for the delay in replying - a side issue took over.
Much indepted to your help.
Warm Regards
Bill
-----Original Message-----
I believe your xlsheet goes out of scope when you call the Price_2way.
either move the declaration of xlsheet to outside of any routine
to give it a module-level scope or pass the xlsheet to the Price_2way as a
parameter.
Of course, in the later case you will need to change the declaration
of the procedure to accept the parameter:

Sub Price_2way(xlsheet as excel.worksheet) ' pricing subroutine common

HTH
--
Please reply to NG only. The email address is not monitored.

Alex.

Thanks Ivan,
Further to my predicament even with the refernece as
suggested I still get the same error or if I shift the
subroutine to the main body, errors suggesting that the
object etc doesn't support this action.
Having said all that could I possibly be asking the code
to do something I shouldn't. what I mean is i'm running
the code from access to open an excel spread sheet at a
particular worksheet. i'm defining the range and
dependent on the input from a control on the form find a
particular cell value on the spreadsheet (offset form
active cell) and make this value the result or input of a
control on the form. I'm adding numbers from the
spreadsheet on the form so to speak.maybe I should copy
the cell value to clip board and then paste to the
control, the way I was thinking was to let the value on
the sheet = something in code then saying that the
control in the access form = this value.
anyway some ramblings - thanks for the input.
Warm regards
bill.
-----Original Message-----
Bill,
I have already given you an example. As I see from
Click to expand...
your
code snippet, you
have already done it right in other cases, but here
Click to expand...
you
forgot to prefix it
with xlsheet.

Side note, not related to the problem:
Set xlsheet = xlbook.Worksheets(2)
xlbook.Worksheets(2).Activate ' <- you should
Click to expand...
use
xlsheet.Activate
instead

HTH

Alex.


Thanks Alex,
The Module is linked to a form.
I have used option explicit.
Sorry but I do not understand how to give the ActiveCell
a fully qualified name for the subroutine.
The active cell is called up in the "Case Is" but I do
not understand how to pass this to the subroutine?
If I run the debugg I do not get the error, only
Click to expand...
when
the
code runs does - an error at the line,

Set rng11 = ActiveCell.Offset(c, 0).Value

saying "Object variable or With Block Variable
Click to expand...
not
set.
Please expand on a fully qualified name.
Kind Regards
Bill


-----Original Message-----
Access generally have no idea what ActiveCell is.
Click to expand...
Try
to
give it a fully
qualified name, ie
Set rng11 = xlsheet.ActiveCell.Offset(c, 0).Value

I would recommend to add Option Explicit as a first line
in you code module
and declare all your variables.
It will make debugging much easier.

--
Please reply to NG only. The email address is not
monitored.

Alex.

message
Hi,
The code is in a module. Using access 2000.
Click to expand...
Module
is
called up from a command button on the form (called
calculate). Calls up the Fuction "tube Select" of which
Sub Price_2way() ' pricing subroutine common

is called up.

I took out the common subroutine in a function which is
called up in a "Case Select" .
At the start I open an Excel worksheet,

With xlapp
.Visible = False
Set xlbook = .Workbooks.Open("C:\My
Documents\2WAYPRICES.xls")
Set xlsheet = xlbook.Worksheets(2)
xlbook.Worksheets(2).Activate
End With

The Case select is based on a control value on an Acces
form - like,

Select Case Forms![tubeselectform]!Frame110 '
Click to expand...
tube
size
Me![tube size].

' each number in Case IS - represents a
diameter of pipe in millimetres.

Case Is = 50
Set rng1 = xlsheet.Range ("b4:b75")
xlsheet.Range("b4").Activate

Call Price_2way

Case Is = 65
Set rng2 = xlsheet.Range ("c4:c75")
xlsheet.Range("c4").Activate

Call Price_2way

Price two way is common so I extracted it out to give,

Sub Price_2way() ' pricing subroutine common

Dim rng11 As Range
Dim rng21 As Range
Dim rng31 As Range
Dim rng41 As Range
Dim c As Integer
Dim res As Variant
Dim res2 As Variant
Dim res3 As Variant

'look at the material option
If (Forms![tubeselectform]!
Click to expand...
[Grade]
= 1)
Then 'non sanitary mild steel
c = -3
ElseIf (Forms! [tubeselectform]!
[Grade] = 2) Then ' non sanitary 304
c = -4
ElseIf (Forms! [tubeselectform]!
[Grade] = 3) Then '316 stst option.
c = -8
Else
'do nothing
End If
Set rng11 = ActiveCell.Offset (c,
0).Value
res = ""
res = rng11
'rng11 is the value to be
Click to expand...
copied
to
field Forms![tubeselectform]![Price_Tube_Size)
Forms![tubeselectform]!
[Price_Tube_Size] = res

'now look at the Cover option
If (Forms![tubeselectform]!
Click to expand...
[Cover]
= 2)
Then 'cover is 304 ss
c = -30
ElseIf (Forms! [tubeselectform]!
[Cover] = 3) Then ' cover is 316 ss
c = -31
Else
c = -1
End If
Set rng21 =
xlsheet.ActiveCell.Offset
(c, 0).Value
res2 = ""
res2 = rng21
'rng21 is the value to be copied to
field Forms![tubeselectform]![Price_Cover_Option] and
will
be numeric.
Forms![tubeselectform]!
[Price_Cover_Option] = res2

'now look at the Controller option
If (Forms![tubeselectform]![DC] = True)
Then
c = -29
ElseIf (Forms! [tubeselectform]!
[DC] =
False) Then
c = -28
Else
'do nothing
End If
Set rng31 =
xlsheet.ActiveCell.Offset
(c, 0).Value
res3 = ""
res3 = rng31
'rng31 is the value to be copied to
field Forms![tubeselectform]![Price_Prologic)
Forms![tubeselectform]!
[Price_Prologic] = res3
End Sub


In the "case Is" I activate the active cell. In
Click to expand...
the
sub
routine I offset to get a value - or do I?????.

Where have I gone wrong????.
Thanks In advance
Bill


.



.
Click to expand...
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

From Access Automate Excel to transfer cell values to form controls - possible? 6
object reference not set to an instance of an object error 2
Object Variable or With Block Variable Not Set 3

Top