Automation Problem revisited - cap inhand - Error 1004 1

[justagrunt]

Hi,
Private module attached to an access form called up by a control. All variables are global and the excel sheet does open and activates the activecell. I have taken a subroutine and dumped it back into the main body of the routine.
The following gives me an error 1004 Application-defined or object defined error at line,

rng11=activecell.offset(c,0)

Which is part of
Case Is = 100
Call Open_Excel

Set rng4 = Sheets(2).Range("e4:e75")
Sheets(2).Range("e4").Activate

z = ""
z = ActiveCell.Value
MsgBox " equals " & z, vbOKOnly 'check it's working


'CHECK MESSAGE BOX FOR DEBUGG
MsgBox "Ok back in main. Ok to here", vbOKOnly ' and start sub routine "Price2-Way

'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


rng11 = ActiveCell.Offset(c, 0)

res = ""
res = rng11.Value

'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

rng21 = ActiveCell.Offset(c, 0)

res2 = ""
res2 = rng21.Value
'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

rng31 = ActiveCell.Offset(c, 0)

res3 = ""
res3 = rng31.Value
'rng31 is the value to be copied to field Forms![tubeselectform]![Price_Prologic)
Forms![tubeselectform]![Price_Prologic] = res3
Rem Price_2way

Case Is = 125
Call Open_Excel

Set rng5 = Sheets(2).Range("f4:f75")
Sheets(2).Range("f4").Activate

Price_2way

Cheers
Any Thoughts
Bill

 

[cLFlaVA]

Hi Bill...

Do me a favor. Change your If statement to this:

[tt]
'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
Msgbox "No value for c"
End
End If
[/tt]

If this doesn't help you, I have a few more ideas.

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[justagrunt]

Hi cLFlaVA,
No joy I am afraid. Still trapping at the line

rng11=activecell.offset(c,0)

Thanks
Bill

 

[cLFlaVA]

And now I found it.

Look at this:

[tt]
Cell E4
-------
According to Rows and Columns:
Column 5
Row 4
[/tt]

When you get to this line:
[tt]rng11 = ActiveCell.Offset(c, 0)[/tt]
And your Forms![tubeselectform]![Grade] is anything other than 1, c will be either -4 or -8.

The Offset function takes the following arguments:
Row offset, Col offset.

See the problem yet?

You're trying to take a cell (4 row, 5 col) and subtract 4 rows or 8 rows from it. The result: a negative number. Not allowed!

Maybe what you want is:

[tt]rng11 = ActiveCell.Offset(0, c)[/tt]

AM I RIGHT YET?!?!?!?!

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[justagrunt]

Arrhhhhhhhhhh,
Yes Buttttttt ok,
Curses blast dyslexia.
Exactly.
Thanks,
Warm regards
Bill

 

[cLFlaVA]

All set? Nice.

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[cLFlaVA]

You know, if Microsoft would just make more than, say, 4 error messages, maybe people wouldn't have such problems finding errors!

Object variable not set is not a fair assessment of what's going on.

Row number cannot be less than 1 or greater than 65,536 would be much more helpful!

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[justagrunt]

Totally Agree with you.
And I believe that there are even error messages which are legacy and don't print which is another cause of distress.
I read the offset help so many times that when it said positive numbers were down or to the right I still had it in my head in reverse.(negative down positive up daaaaa) No matter how many times I looked, dyslexia, and common sense told me opposite, and thats it.
No wonder NASA are using Linux.
Thanks cLFlaVA.
kIND Regards
Bill