Run-time error '1004': Method 'Cells' of object _Global failed

Hello,

I have built a macro in Excel 2000 that I want to run on any active sheet
that I am on in a workbook. However, I get the error in the subject line,
above, for the following code:

While Not LastColumn
If Cells(1, traces + 1).Value = "Volt" Then
traces = traces + 1
Else
LastColumn = True
End If
Wend

I know that typically one specifies the sheet by identifying Cells with
Sheet(1) or whatever, but I don't want this macro to be tied to any specific
sheet, only the one that is presently active.

Does anyone have any ideas?

Thanks in advance.

If your code does not hit exactly the string "Volt" then it will error out as
it will continue right through the end of the columns. Check the value of
traces when the error occures and I bet it will be 256. I would be inclined
to put in a second stop conditon and to make the seach for Volt be a little
more bulletproof, something like this

While Not LastColumn or traces = 255
If ucase(trim(Cells(1, traces + 1).Value)) = "VOLT" Then
traces = traces + 1
Else
LastColumn = True
End If
Wend

--
HTH...

Jim Thomlinson


"Fran D" wrote:

> Hello,
>
> I have built a macro in Excel 2000 that I want to run on any active sheet
> that I am on in a workbook. However, I get the error in the subject line,
> above, for the following code:
>
> While Not LastColumn
> If Cells(1, traces + 1).Value = "Volt" Then
> traces = traces + 1
> Else
> LastColumn = True
> End If
> Wend
>
> I know that typically one specifies the sheet by identifying Cells with
> Sheet(1) or whatever, but I don't want this macro to be tied to any specific
> sheet, only the one that is presently active.
>
> Does anyone have any ideas?
>
> Thanks in advance.

Actually, the value of traces is still at the initialized value of 0 when it
fails, so it didn't even get past the first column. Yes, that column has the
value "Volt" in that position [Cells(1,1)].

Any other ideas?

"Jim Thomlinson" wrote:

> If your code does not hit exactly the string "Volt" then it will error out as
> it will continue right through the end of the columns. Check the value of
> traces when the error occures and I bet it will be 256. I would be inclined
> to put in a second stop conditon and to make the seach for Volt be a little
> more bulletproof, something like this
>
> While Not LastColumn or traces = 255
> If ucase(trim(Cells(1, traces + 1).Value)) = "VOLT" Then
> traces = traces + 1
> Else
> LastColumn = True
> End If
> Wend
>
> --
> HTH...
>
> Jim Thomlinson
>

I still need some help here. Does anyone have any ideas?

"Fran D" wrote:

> Hello,
>
> I have built a macro in Excel 2000 that I want to run on any active sheet
> that I am on in a workbook. However, I get the error in the subject line,
> above, for the following code:
>
> While Not LastColumn
> If Cells(1, traces + 1).Value = "Volt" Then
> traces = traces + 1
> Else
> LastColumn = True
> End If
> Wend
>
> I know that typically one specifies the sheet by identifying Cells with
> Sheet(1) or whatever, but I don't want this macro to be tied to any specific
> sheet, only the one that is presently active.
>
> Does anyone have any ideas?
>
> Thanks in advance.