.Find problem

[Igwiz]

Tearing my hair our here!
I have had this working in various guises but now it is playing up!

client definitely exists in the range ClientData but c is not returning a range:

Set c = Workbooks("Clients&quot.Worksheets("Clients&quot.Range("ClientData&quot.Find(client, LookIn:=xlValues)

I have got another part of a different workbook selected when I run this and c is returning the first value in this selected range.

Any clues?

Thanks,

IG

 

[xlbo]

try this:
with Workbooks("Clients&quot.Worksheets("Clients&quot.Range("ClientData&quot
Set c = .Find(client, LookIn:=xlValues)
end with


Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[Igwiz]

Hi Geoff,

That is what I had in before and it didn't work either which I was I tried changing it. I thought maybe because I have another range (actually a pivottableselect) selected it doens't like it?

 

[Igwiz]

Sorted it by changing how the for each loop referenced the selection.

 

[Bowers74]

Hi Igwiz,

Just a tip:

You can change

Set c = Workbooks("Clients").Worksheets("Clients").Range("ClientData").Find(client, LookIn:=xlValues)

to

Set c = [ClientData].Find(client, LookIn:=xlValues)

You can refer to all of your Named Ranges inside of Brackets "[]", to make your like easier (i.e. LESS TYPING).



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[xlbo]

Mike - you have to be careful though. What that actually does is utilise the default property of the object so
[myRange] actually equates to range("myrange&quot.value
However, because VBA works like it does, you can use the syntax in range referencing. It also works for non named ranges:
[A1]
equates to range("A1&quot.value

so [A1] = 1
will enter a 1 in range A1

Rgds, Geoff
[blue]Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?[/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244