Hi there,

I'm working on a macro to find items within my database. I
found the code below in the help files, but can't get it
working in my worksheet.

I have a feeling that this has something to do with the
variable (c) not being defined - at least that's what the
pop up in VB tells me. How do I define this?

Also I want to change the code a little to lookin another
worksheet. Is this do-able with this code or is there a
simpler way??

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <
firstAddress
End If
End With

Cheers,

Sue

Hello Sue
I have a feeling that this has something to do with the
variable (c) not being defined - at least that's what the
pop up in VB tells me. How do I define this?
Either add this line :
Dim c
Or:
in the VB Editor go to Tools, Options, Editor tab and untick the second
option

Also I want to change the code a little to lookin another
worksheet. Is this do-able with this code or is there a
simpler way??

Amend this line With Worksheets(1).Range("a1:a500")
with the relevant worksheet name and range eg:
With Worksheets("YourSheetName").Range("B1:B500")

HTH
Cordially
Pascal


"Sue M" a écrit dans le message de
...
Hi there,

I'm working on a macro to find items within my database. I
found the code below in the help files, but can't get it
working in my worksheet.

I have a feeling that this has something to do with the
variable (c) not being defined - at least that's what the
pop up in VB tells me. How do I define this?

Also I want to change the code a little to lookin another
worksheet. Is this do-able with this code or is there a
simpler way??

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <
firstAddress
End If
End With

Cheers,

Sue

Hi,
the worksheet in your code is 1 and the range is A1:A500
it is possible to change as you want ....

With Worksheets(1).Range("a1:a500")

With Worksheets("test").Range("a1:X5700")


--
....Patrick
Quoi que vous fassiez, faites le bien .
Mail: http://cerbermail.com/?KPW0tTCjFw
Connectez vous sur ce forum par :
news://msnews.microsoft.com/microsoft.public.fr.excel


"Sue M" a écrit dans le message de
...
Hi there,

I'm working on a macro to find items within my database. I
found the code below in the help files, but can't get it
working in my worksheet.

I have a feeling that this has something to do with the
variable (c) not being defined - at least that's what the
pop up in VB tells me. How do I define this?

Also I want to change the code a little to lookin another
worksheet. Is this do-able with this code or is there a
simpler way??

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <
firstAddress
End If
End With

Cheers,

Sue

Thanks I have it going now.

both c and first adress needed the Dim thing.

Cheers,

Sue
-----Original Message-----
Hello Sue
I have a feeling that this has something to do with the
variable (c) not being defined - at least that's what
the
pop up in VB tells me. How do I define this?
Either add this line :
Dim c
Or:
in the VB Editor go to Tools, Options, Editor tab and
untick the second
option

Also I want to change the code a little to lookin
another
worksheet. Is this do-able with this code or is there a
simpler way??

Amend this line With Worksheets(1).Range("a1:a500")
with the relevant worksheet name and range eg:
With Worksheets("YourSheetName").Range("B1:B500")

HTH
Cordially
Pascal


"Sue M" a écrit
dans le message de
...
Hi there,

I'm working on a macro to find items within my
database. I
found the code below in the help files, but can't get it
working in my worksheet.

I have a feeling that this has something to do with the
variable (c) not being defined - at least that's what
the
pop up in VB tells me. How do I define this?

Also I want to change the code a little to lookin
another
worksheet. Is this do-able with this code or is there a
simpler way??

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <
firstAddress
End If
End With

Cheers,

Sue


.