VBA Code for "Find" in Excel 1

[Kstater30]

I am trying to write a code to "find" the value in a cell, for this example we will say cell A2, in a table on the same worksheet. So if the table is in range A5:F50 and I want to "Find" the value in cell A2 in that range and then leave the cursor on that cell once it's found how can I do that? I have tried using the VBA help but it does a find and replace, i just want to find and go to that cell. Any suggestions are greatly appreciated.

 

[SkipVought]

Hi,

dim r as range
set r = range("A5:F50").find("Stringtofind")
if not r is nothing then r.select

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[SkipVought]

ahhh, I see you have a value in A2

dim r as range
set r = range("A5:F50").find(A2)
if not r is nothing then r.select

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[Kstater30]

Skip, I appreciate your help. When I copy and paste the code and run it the cursor goes to the table but doesn't select the cell from A2, it instead goes to the first row and second column in the table regardless of what I put into A2 to find. Any advice? Even though I have changed the value in A2 it always goes to this location.

 

[PHV]

this worked for me:
Dim r As Range
Set r = Range("A5:F50").Find([A2])
If Not r Is Nothing Then r.Select


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Kstater30]

Guys, thank you for your help. PHV, I input that code and it seems to have worked. I'll put it in with the others I have done with Macros and see if I can't get the whole shabang to work. If not "I'll be back". Really do appreciate everything guys.

Thanks