# VBA Find Variant



## B-Man (Dec 22, 2022)

Im not sure what im doing wrong
I want to extract "Name" from a cell then search for that name. but i can only seem to get it to search for literally "name" not my defined name....



```
Dim Name As Variant
             For x = Len(Range("C4")) To 1 Step -1
                If Mid(Range("C4"), x, 1) Like "[A-Za-z]" Then Name = Left(Range("C4"), x): Exit For
            Next x
   
               
 Set findcell = wsDest.Range("F:F").Find(What:=Name, LookIn:=xlValues, SearchDirection:=xlPrevious)
```


----------



## StephenCrump (Dec 22, 2022)

B-Man said:


> I want to extract "Name" from a cell then search for that name. but i can only seem to get it to search for literally "name" not my defined name....


So, what's in cell C4?


----------



## HongRu (Dec 22, 2022)

Maybe try to replace Name with xName in your VBA code.


----------



## B-Man (Dec 23, 2022)

StephenCrump said:


> So, what's in cell C4?


Tom,  Sam,  Sally, 
Depends what's put in there


----------



## StephenCrump (Dec 23, 2022)

If I run your code, and C4 of the ActiveSheet is "Tom", then it correctly sets findcell to the last occurrence of "Tom" in wsDest column F (or if there is no occurrence, findcell will be Nothing).

I don't see how the literal string "name" can come into it?

PS, like *HongRu, I'd avoid using Name as a variable name, in case it causes a conflict.  Although it's not an issue here.


----------



## B-Man (Dec 23, 2022)

I had copied the name formula from another macro I had and it works same concept but using it differently im sure it could have been better suited but it done the job without having to redo the formula. I just hadn't used it in find. 

I went step by step through my code.

it seems that macro when opening the sheet had taken over being the active sheet so was causing issues .
once I added wsSource. infront of all the Range("C4") it worked.

sorry all I thought it was the find part of the code not recognising the name but it was looking in the wrong sheet for the cell and it didnt find what it was looking for.


----------



## StephenCrump (Dec 23, 2022)

No problem.  I'm glad you got it sorted.


----------

