VBA Search cell help 2

[chadbi]

Hello, I am wondering if anyone has a script that will look for the contents of one cell in a list of other cells. For example: In cell C2 I have a 10 digit phone number. In column E, I have a list of phone numbers ranging from E2:E100. What I would like to do is take the number in C2 and look for it in the E2:E100 range and then if it is found, I would like it to be entered into D2 and then loop through all cells in column C that have a number repeating the search until the cell in C is empty.

 

[Gavona]

Clear as mud i am afraid!
What do you want entered in D2 - the value that you had in C2 or the address of the cell where it was found?
What if it is not found?
What if a second occurence is found?

nothing in your description would do anything to change anything in column C so what does "untiul the cell in C is empty" mean?

Gavin

 

[fumei]

Let me see if I can wipe some mud (although I tend to agree with Gavin that the question is not really expressed clearly).

COLUMN C has cells, some of which have numbers, some do not.

IF Cx - starting with C2 - has a match in E2:E100

THEN Dx value = Cx value

REPEAT down through COLUMN C.

Yes?

The phrase at the end - "until the cell in C is empty." is confusing.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[chadbi]

fumei, you are correct except that cx will all have data until the end of the list. The "until the cell in C is empty" means that the loop would stop once it hits an empty cell in C. Sorry for the confusion...

Thanks in advance for any resolution!

 

[fumei]

Now that it is clearer I shall leave the answer to people who actually know this stuff. I am out of my depth, which regarding Excel is about 1 millimetre.





Actually, it is about a nanometer, or if you wish to be archaic, about <1 Angstrom.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[Gavona]

No VBA required. Examples show formula in D2 which should be copied down.

A simple vlookup formula in column D would do the trick.
=VLOOKUP(C2,$E$2:$E$100,1,FALSE)
Where the value in C is not found then this will return an error. You can could make the formula more complex to deal with this:
=IF(ISNA(VLOOKUP(C2,$E$2:$E$100,1,FALSE)),"",VLOOKUP(C2,$E$2:$E$100,1,FALSE))

Finally you might want to copy the formulae in D2?? and use Edit,Pastespecial,Values to convert them to values.

To automate with VBA:
Once you have the formula working switch on the recorder, edit C2, copy it down and then copy pastespecial, values. Then look at the recorded VBA code.

Gavin