Another For each headache I get

[Vacuum Sealed]

Hi All

Still haven't got over the For Each hurdle yet, a lot of learning still
to be had.

Sub ConvertPental()

Dim myStep12 As Range
Dim c As Object, myPalls As Range

Set sWS = Sheets("Import")
Set myStep12 = sWS.Range("H3:H25")

For Each c In myStep12
If c.Offset(0, -3).Value > 0 Then
With c
.Value = .Offset(0, -3).Value + .Offset(0, -2).Value * 44
End With
End If
Next c
End Sub

This throws up:

Run-Time Error '424' Object required

As always

Any assistance is appreciated.

TIA
Mick.

 

[Paul Robinson]

Hi
Works fine for me. You could just change to
Dim c as Range

but Dim c As Object did not cause a problem for me either. Check that
you really do have a sheet called "Import" and not some other spelling
or space character present.
Which line is highlighted in the code when the error occurs?
regards
Paul

 

[Gord Dibben]

Works for me Mick.

I do agree with Paul that c could be Dimmed as Range rather than
Object but no problems as is.

If you don't have a sheet named Import you would get a runtime error 9
"subscript out of range" so that would not appear to be the problem.

I tested only in 2003............maybe later versions don't like the
Dim c as Object



Gord

 

[Vacuum Sealed]

Works for me Mick.

I do agree with Paul that c could be Dimmed as Range rather than
Object but no problems as is.

If you don't have a sheet named Import you would get a runtime error 9
"subscript out of range" so that would not appear to be the problem.

I tested only in 2003............maybe later versions don't like the
Dim c as Object



Gord

Paul & Gord

Thank you, I am using 2003 at work, it was very frustrating. The above
snippet is where the code halted, regardless if I dimmed it as either.

I may be missing an object library or similar hence why it is throwing
up the error maybe...

With regards to "c", I was using "Cell" and thought that may be a
contributor, so I shortened it to "c", I will use another letter and see
what eventuates.

I will double check the spelling for trailing blanks.

Thanks again.

 

[isabelle]

hi Mick,

Sub ConvertPental()
Dim sWS As Worksheet
Dim myStep12 As Range
Dim c As Range
' Dim myPalls As Range 'not use

Set sWS = Sheets("Import")
Set myStep12 = sWS.Range("H3:H25")

For Each c In myStep12
If c.Offset(0, -3).Value > 0 Then
With c
.Value = .Offset(0, -3).Value + .Offset(0, -2).Value * 44
End With
End If
Next c
End Sub

--
isabelle



Le 2012-03-07 16:46, Vacuum Sealed a écrit :

 

[Paul Robinson]

Hi
If this is a code snippet then the problem lies elsewhere in the code
we can't see.
regards
Paul

 

[Vacuum Sealed]

Hi
If this is a code snippet then the problem lies elsewhere in the code
we can't see.
regards
Paul

Paul

I tend to agree with you, although the code runs through fine up to this
point, that said!

Just out of curiosity, what is the maximum number of dims you can set
for routine, I'm thinking as I had so many within this routine it
reached it's limit and just happened to cease on this section of the code.

With that in mind, I decided to call the code from a separate module and
it works fine. So that's one less bruise on my forehead.

Thank you to all for your patience and help.

Cheers
Mick.