Checking to see if string is anywhere in a column

**vbaNewbieMan** · 06-25-2013, 01:57 PM

Hey there ladies and gents,

I would like to write a VBA code that would take the all the cells in a column, column B, and see if all of these different strings are anywhere as a string in column K and tell me where it exists in the respective row in column L. The only problem I continue to run into is the fact that there are more or some missing characters in column K than in column B (ex. B2= RSI=24MASI and some cell in K= 1K24MASI) or something along the lines of that. The amount of rows can vary also.

I also looked into writing a formula like this:

Please Login or Register  to view this content.

but that returned some interesting N/A's or #NAME errors.

I could really use some guidance/help in writing this.

Also, I am writing this on excel 2003 so some of the newer functions don't apply.

Thank you so much.

**JBeaucaire** · 06-25-2013, 02:05 PM

You sure this is a VBA question?

Attach a sample workbook. Make sure there is just data to demonstrate what is needed. Mockup your desired results, too. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**vbaNewbieMan** · 06-25-2013, 02:53 PM

I'm pretty sure this is a VBA question as I think you can write a code that fits this perfectly.

Anyway, here's the sample data and what I would like as a result:

Column:========B===========C===============K==============L
Row:=====Coats Order#=====Piece Count========ORDER #=========Result
1----------------G08435-------------8----------------1K3KLQTY----------Row 3
2----------------G08428-------------8----------------1K3KLQTY----------Row 6
3----------------G08419-------------8----------------1K3G08435---------Row 4
4----------------3KLQTY------------8----------------1FG08419-----------Row 1 & Row 2
5----------------G13754-------------5----------------1K24MASI----------Row 7
6------------RSI = 24MASI-----------3---------------1K3G08428----------Row 5
7---------------3PMWFX-------------5----------------1K3G13754----------Row 25

See how some of the cells in B are shorter/contain some random "RSI =" characters? It should compare what's in B to what is in all of K, and tell you where it found the match.

**JOHN H. DAVIS** · 06-25-2013, 03:21 PM

I don't think this will do everything you want but it may get you started. It will only find one instance.

Please Login or Register  to view this content.

**JBeaucaire** · 06-25-2013, 03:24 PM

Still not seeing the sample workbook.

**vbaNewbieMan** · 06-25-2013, 03:33 PM

John H. Davis,

Definitely worked for one instance, which is actually all I needed I just found out. Only things that happens that keeps it running for a while is it continues to look at all of the empty cells after B has no more inputs, and the fact that it shows up as "row 2" for those that have no match. I would prefer to have it stop searching after B becomes empty, and that it shows up as "Missing" for the ones that have no match.

Thank you so much for your help!

Edit: Sorry for not following the rules! I thought that he had meant write what the spreadsheet should look like in your reply, my bad. Below in another response I have attached a sample spreadsheet of what i would like done. Sorry again.

**Mordred** · 06-25-2013, 03:35 PM

.................My interpretation of a deleted post

...................

**vbaNewbieMan** · 06-25-2013, 03:51 PM

Attached is the example spreadsheet, sorry for not following the rules! I thought you meant a physical example on the reply. My bad. Anyway, in this spreadsheet, L is what I would like, B is the thing I need to find, and K is where to find it.

**JBeaucaire** · 06-25-2013, 03:52 PM

I didn't notice your text example is actually pretty usable. I do prefer a workbook, in the future, if you don't mind.

My idea is to install a new function so you can enter a formula into column L to get your answers. Put this function into a standard code module (Insert > Module)

Please Login or Register  to view this content.

Close the vbeditor, then put this is used in a cell like any formula. For instance, in L2:

=FindString(StringToFind, RangeToSearch, "OptionalStringToExclude")
=FindString(B2, K:K, "RSI =")

Copy down.

**JBeaucaire** · 06-25-2013, 03:57 PM

I installed that into your workbook and it works great. You've got a hidden character at the end of the string in B8, watch out for that.

**vbaNewbieMan** · 06-25-2013, 04:22 PM

JBeaucaire,

I just applied it to my original workbook, works like a charm. Thank you very much for your help! Saves me about an hour each day going through it by hand and checking them off. Much obliged.

**JBeaucaire** · 06-25-2013, 05:04 PM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Checking to see if string is anywhere in a column

LinkBack

Thread Tools

Rate This Thread

Display

Checking to see if string is anywhere in a column

Re: Checking to see if string is anywhere in a column

Re: Checking to see if string is anywhere in a column

Re: Checking to see if string is anywhere in a column

Re: Checking to see if string is anywhere in a column

Re: Checking to see if string is anywhere in a column

Re: Checking to see if string is anywhere in a column

Re: Checking to see if string is anywhere in a column

Re: Checking to see if string is anywhere in a column

Re: Checking to see if string is anywhere in a column

Re: Checking to see if string is anywhere in a column

Re: Checking to see if string is anywhere in a column

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions