How do i fix this macro?

[Hinojosa via OfficeKB.com]

This is the macro I'm using now:

Sub copy_it()
Dim r As Range,r1 As Range, r2 As Range
Workbooks ("TMSDL.XLS").Activate
s = InputBox("Enter Search Value:")
For Each r in ActiveSheet.UsedRange
If r.Value = s Then
Set r1 = Range(r, r.Offset(0,8))
Set r2 = Workbooks("TMSPhaomReport.xls").Worksheet("TMSDL").Range("A1")

r1.Copy r2
Exit sub
End If
Next

I still learning how to use this but the problem is when I run this Macro it
doesn't do anything. I'm guessing because r doesn't equal s but not really
sure. Can anyone help me out?

 

[Dave Peterson]

Unless you do something special, this line is case sensitive:

If r.Value = s Then

So ASDF won't match asdf or AsDf or ASDf or ASdF or...

maybe it would be as simple as:

if lcase(r.value) = lcase(s) then

 

[Hinojosa via OfficeKB.com]

Yes sir i have tried with all caps and all lowercase but still it just
doesn't do anything.

Unless you do something special, this line is case sensitive:

If r.Value = s Then

So ASDF won't match asdf or AsDf or ASDf or ASdF or...

maybe it would be as simple as:

if lcase(r.value) = lcase(s) then

This is the macro I'm using now:

[quoted text clipped - 18 lines]

 

[Dave Peterson]

Maybe there's a difference in what you type and what's in the cell???

(Leading/trailing/extra embedded spaces???)

Yes sir i have tried with all caps and all lowercase but still it just
doesn't do anything.

Unless you do something special, this line is case sensitive:

If r.Value = s Then

So ASDF won't match asdf or AsDf or ASDf or ASdF or...

maybe it would be as simple as:

if lcase(r.value) = lcase(s) then

This is the macro I'm using now:

[quoted text clipped - 18 lines]