Wrox Programmer Forums
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 27th, 2011, 09:25 AM
Authorized User
 
Join Date: Jul 2010
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Question Partial Vlookup

Hi All,

I am facing with a fuzzy issue working with vlookup. I require to do a partial search in a range of cell.

I have two sheets. Worksheet1 contains list of IDs for ex: 000A2324GH1100
Worksheet2 is the main database where I need to do a lookup.

For ex. I want to list in Worksheet1 all the IDs that contains text 'A2324G' from Worksheet2. Could you please help me on how can I do this. I have tried FIND & SEARCH functions but did not help.

Regards,
Beginner
 
Old July 27th, 2011, 12:15 PM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi

How many IDs will be with the text 'A2324G' . If there is going to be only one, vLookup could get you that; if there are more than one you need to chose some other method (query table etc)

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
 
Old July 27th, 2011, 01:38 PM
Authorized User
 
Join Date: Jul 2010
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default Re:

Hi Shasur

Thanks for the reply. Yes there are more than one ID with that text. Anyways I ll get some help regarding query tables from google.

Thanks
 
Old July 29th, 2011, 12:44 PM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Here is how I had used it some time back (http://vbadud.blogspot.com/2007/12/q...ta-source.html). The source was from different workbook

You need to add the where clause to the SQL statement

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
 
Old August 1st, 2011, 07:40 AM
Authorized User
 
Join Date: Jul 2010
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Red face vlookup

Hi Shasur,

Is it possible to do a Vlookup for a part of a cell. Usually I select a particular cell and do a lookup. How can a partial vlookup be done? Please help

Thanks and Regards,
Afzal
 
Old August 2nd, 2011, 10:11 PM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi Afzal

The last parameter in Vlookup can be used to find approximate values, but that will not cater to your demand. If you specify the range_lookup as false it will search for exact values

There are other functions like Find; it will get search for a string within another and get the starting character

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)





Similar Threads
Thread Thread Starter Forum Replies Last Post
Vlookup with VBA andygill Excel VBA 2 November 6th, 2008 06:44 PM
VLookup with Strings Problem dem1an Excel VBA 1 June 3rd, 2008 10:46 AM
Problem with VLookup and DATEVALUE in VBA Sharadk74 Excel VBA 2 April 25th, 2008 09:30 AM
VLookup, arrays, and worksheet names chp Excel VBA 0 April 7th, 2006 03:15 PM
character to range conversion for VLOOKUP gskoog Excel VBA 1 May 26th, 2004 05:10 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.