Simple VBA to look for value

**Ztv** · 04-22-2014, 05:23 AM

Hi
I'm new to VBA, and I have already tried to google this, though I can't find something that fits to my problem (or that I'm able to adjust to my problem).

The problem is simple: I have a list of values and I need to checke if these values appear in one of the other worksheets. The problem is that the sheets are unstructured, so in some sheets, the value could be macthed column D, in other sheets in column E. But the value to search for will always be in the range A:H in the underlying worksheets, if there is a match.

So.. The code should search for a value and return where the value appear(s).. Please see the attached simplified file.

I really hope that someone can help me.

**ragulduy** · 04-22-2014, 05:52 AM

hi,

This code:

Please Login or Register  to view this content.

will work for your example workbook...

**Ztv** · 04-22-2014, 03:48 PM

@raguldguy - thanks! As you say, the code works perfectly for my workbook. I'll try to implement it to my original file tomorrow

.

Sorry for the late reply, I had to go for a meeting earlier.

**Ztv** · 04-23-2014, 03:29 AM

@ragulduy: How do I make the code to ignore small letters/capital letters, spaces, etc.? For example: ABC.. if abc appears in another sheet, it should be a match.. Also for aBc or a bc.

EDIT: It can already distrinquish between small letters and capital letters, now I just need it to ignore spaces/embedded spaces

**ragulduy** · 04-23-2014, 04:28 AM

My suggestion would be to clean up the data to get rid of the spaces first, using the find method, I think the only option would be to use wildcards but that would probably make the search too "fuzzy" and end up picking up incorrect entries.

You could change it to loop through and test every cell but it would take too long I think.

**Ztv** · 04-23-2014, 04:38 AM

Yeah, you're right. I was thinking about to first run a loop that would "clear" the lookup values. But if that should work, this should be done in all sheets.

But I totally agree with your "cleaning up"-argument. It just simple takes too much time in this case. As I told the one who asked me to do this: Garbage in, garbage out.

**ragulduy** · 04-23-2014, 05:19 AM

Can you not do find and replace? find " ", replace with ""?

Simple VBA to look for value

LinkBack

Thread Tools

Rate This Thread

Display

Simple VBA to look for value

Re: Simple VBA to look for value

Re: Simple VBA to look for value

Re: Simple VBA to look for value

Re: Simple VBA to look for value

Re: Simple VBA to look for value

Re: Simple VBA to look for value

Thread Information

Users Browsing this Thread

Similar Threads

Simple adding formula, seems simple enough, what forumla would I use for this?

A simple question requiring a simple answer

Simple Simple Excel usage question

Make it more simple or intuitive to do simple things

Posting data from a simple form to a simple database

Bookmarks

Bookmarks

Posting Permissions