Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help with Excel 'Find' please

Status
Not open for further replies.

tecnik

Programmer
Feb 16, 2006
22
GB
Hi there,

I'm trying to limit the search routine, below, to only find values from one column, D for example, at the moment it will find values from the whole sheet.

I've tried to limit the find a number of ways but haven't had any success.

Thanks in advance,

Nick

Code:
Sub FindCodesV2()
    Dim rng1 As Range
    Dim cel1 As Range
    Dim counter As Integer
    counter = 2
    Set rng1 = Worksheets("Sheet1").Range("A2:A57")
    For Each cel1 In rng1
        
        cellValue = Cells.find(What:=cel1.Value, After:=cel1, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
        
        Worksheets("Sheet1").Cells(counter, 2).Value = ActiveCell
        counter = counter + 1
    Next cel1
End Sub
 
You may try this:
cellValue = ActiveSheet.Range("D:D").Find(What:=cel1.Value ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
The FIND function works on the input you give it. If you reference it against a Cells object, it is going to search all cells - If you want it to search in only column D you must 1st reference column D

change: cellValue = Cells.find(What:=cel1.Value
to: cellValue = Columns("D").find(What:=cel1.Value


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Hi Geoff,

Thanks for your reply and help with the code.

Your solution was one of the things I'd tried, however when I tried to run the
macro I got a 'Type mismatch' error, which is what I get now.

Please can you help further,

Thanks in advance,

Nick
 
Hi Geoff,

My fault, I'd not cut and pasted your code, and so had left '.Activate' on the end of the find.

Now when the code executes the find seems to post the first value found into all the cells.

Any ideas,

Thanks

Nick
 
Having looked a bit further, it appears to be using the value from the cell I have
highlighted when I run the script.
 
what is it meant to do ?? seems we have answered the original question and are on to "why doesn't my code work"?

The reason is because you are now not activating the found cell each time but are still using:

Worksheets("Sheet1").Cells(counter, 2).Value = ActiveCell

change it to:
Worksheets("Sheet1").Cells(counter, 2).Value = CellValue

as the CellValue variable is storing the found information

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top