Link to home
Start Free TrialLog in
Avatar of Thomas Zucker-Scharff
Thomas Zucker-ScharffFlag for United States of America

asked on

Excel O365 VBA to query another tab's info

I did a search on EE and found this comment (https://www.experts-exchange.com/questions/29044535/Excel-VBA-search-userform.html?anchorAnswerId=42219448#a42219448) in an answer to a question.  It almost works for me, but not quite  And I am not that good with VBA to make the alterations.

 I am attempting to do the following:

Search a tab called "2019-2020 LT Members Table" - in the same worksheet - for the currently selected value in the tab I am working on and return the value in the "Primary Last Name" column and the value in the  "BH Member" column from the other tab (checking to see if BH Member is equal to 1).

Due to the nature of the spreadsheet, I am not at liberty to post the working copy.  I am attaching a version with the data taken out.
LT-Full-membership-list-blank.xlsm
Avatar of Bill Prew
Bill Prew

That workbook that you posted is password protected, can't open it...


»bp
Avatar of Thomas Zucker-Scharff

ASKER

sorry use 4288

Do you even need VBA? sounds very much like a VLOOKUP?
I don't know what I need actually.  I just want to accomplish the task.  I have migrated the excel spreadsheet to an Access database, so the question is more for my own edification now.

I am not an expert with Excel, although I am fairly good at it.
ASKER CERTIFIED SOLUTION
Avatar of Scott Fell
Scott Fell
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Scott,

I marked this answered, but I just tried the code and it didn't work.  Is there different code if I am comparing two tables and preferably concatenating the first and last names?  In the attached, I want to be able to enter a name in one table on sheet 2 and have it check another tab's (sheet 1) table for the matching name and return whether that person has LTB member checked or not.  They could be anywhere in the other table's entries, but the name will be in the same column.  There may be duplicate names but the first name will be different if the last name is the same.  Ther may also be duplicate entries for the same person on sheet 2, but not on sheet 1.

Copy of zucker.xlsx
Sure, I will look at your workbook.
Tom,

To keep things simple, I made 2 new sheets, 1B and 2B without any fieldnames. Let's get this to work first, I ran into an issue too with your data and rather than figuring it out, I just like to simplify, then add one.

Also, you didn't have any matching name on sheet 1 and 2.  You will see highlighted I have jane name 9 in both. Also, I am just doing the vlookup at this point. You can always add the if(vlookup stuff =1,"yes","no").  For now, just look at what is being produced.  Notice that o Sheet2B in Column J, rows 2 through 14 are "#N/A" and this represents nothing found.  With this in mind, you will also want to use ISERROR https://support.office.com/en-us/article/is-functions-0f2d7971-6019-40a0-a171-f2d869135665 and if there is an error, either also set it to "No" or perhaps, "Not Found", whatever you choose, just make sure to account for it.

Another issue is the vlookup works best when there is one unique value.  In the example I uploaded, "jane name9" has both a 0 and 1 but the 0 is in a row above the 1 and that makes all the subsequent lookups set to the first occurrence which is 0.

Depending on what you want to do, you may want to get into some vba to better account for this.
Copy-of-zucker.xlsx
Scott,

I am obviously doing something wrong.  I input the formula and each and every entry came out as NA.  I did the following

Created a concatenate column in both spreadsheet tabs (column2 - actual column I or 9th column - in the table doing the lookup and row AH in the table housing the name and address info - 2019-2020 LT Members Table)
use vlookup to compare the first concatenated name to the table with several hundred unique names
=VLOOKUP([@Column2],'2019-2020 LT Members Table'!A:AH,9,FALSE)

Open in new window

LTB membership data (1 or Null) is in column A in the 2019-2020 LT Members Table tab.

Much of this has become academic since I converted the spreadsheet to an Access database (a mistake to use Access - I think, or maybe I am just not that good an access programmer).

The NA's are most likely because of no data.  What you want to do is really better suited for your Access (or any) database though.
Scott,

Yeah, that is why I ended up porting it.  Thanks for the help!