Get The Name Of The File In A Vlookup VBA Using Wildcard Characters?

May 17, 2014

get the name of the File in a Vlookup VBA using wildcard characters. I have to do a VLOOKUP from a specific file which is stored in the same folder as the file in which I need to get this VLOOKUP done, however the name can change anytime keep a word common only for this specific file...

Ex:
I have a File called "Mapping as on 8th May" from where i need to lookup some information for my file and I intend to do this using VLOOKUP in VBA in a Module but since the name can change to anything such as"Mapping as on 16th May" I cant use it in the VBA code, so is there a way around this where If I have this file stored in the same folder then I keep the word "Mapping" specific and unique for all other files in the same folder that the search can pull this file name to be further used in my lookup..

how to use the wildcards in VLOOKUP lookup range for the file name as the columns are going to remain the same...

View 13 Replies


ADVERTISEMENT

VLookup In Array Search With Wildcard Characters

Jul 10, 2012

I have a sheet with Names in it. I need to look those names up and find which org they belong to. The problem is the the names have many characters after them, and many names belong to a few orgs, with a the rest belonging to many orgs. (See below)

I have been trying to get a Vlookup to work, but since it can't go left, it won't look up the orgs that don't own at least three names. And I can't get the search to work on the names.

Names:
WSGHG1-8410YFH

WSSMXG-8401B8T

NBSMXG-0310V6R

NBXG-0310V6V

[code]....

View 4 Replies View Related

Using Wildcard Characters In IF -THEN Statement

Oct 22, 2007

I'm trying to create 2 separate data sets based on year (06 vs. 07). To do so, I created a column and created an if then statement that populates with Yes If the date value is "like" 2006". Excel didn't like the ? or asterik. Is there some other syntax that will accomplish.

While I open to other approaches for filtering these 2 data sets, Ive been wondering for awhile about excel and wildcard logic.

View 9 Replies View Related

Strings And Wildcard Characters

Feb 21, 2007

I am attempting to set a value in a cell based on the content of another two cells. I want it set the value to 35 or 38 only if the txtProgram.Text contains Math*. Unfortunately, it always changes it to the Math value based on txtGrade.Text. What mistake am I making?

Application.Goto Reference:="SEFirstPass"

If txtGrade.Text Like "K" Or txtGrade.Text Like "1" Or txtGrade.Text Like "2" Then
ActiveCell = "27"

ElseIf txtGrade.Text Like "3" Or txtGrade.Text Like "4" Or txtGrade.Text Like "5" Or txtGrade.Text Like "6" Then
ActiveCell = "30"
End If....................

View 4 Replies View Related

Wildcard Characters Okay In Countdiff And Sumproduct?

Feb 2, 2010

Wildcard characters okay in countdiff and sumproduct? .......

View 9 Replies View Related

Lookup Formula With Wildcard: Force Excel To See * & ? As Characters

Sep 8, 2006

There might be an easy solution for this, but I can't seem to figure it out. I'm trying to do a vlookup with the lookup value being #CFDMSMCSA*4. But the lookup table also has a value #CFDMSMCSAM4 in it.
It keeps returning the value for this #CFDMSMCSAM4 because of the * in the first string. Any ideas.

View 9 Replies View Related

If Statement With Vlookup And Wildcard

Jan 7, 2014

I need to lookup data from four different columns to match a value in another sheet. The lookup value has to start with "4" in order for it to be the correct match. I've tried the following formula for just two columns:

=IF(VLOOKUP($C2,'Sheet 1'!$D$2:$H$10000,5,FALSE)="4*",VLOOKUP($C2,'Sheet 1'!$D$2:$H$10000,5,FALSE),IF(VLOOKUP($C2,'Sheet 2'!$E$2:$G$10000,3,FALSE)="4*", VLOOKUP($C2,'Sheet 2'!$E$2:$G$10000,3,FALSE),"False"))

The problem I have is that the wildcard function isn't working in my If statement (apparentley that's how Excel was designed). The first vlookup returns the value "40042565" but Excel doesn't show it matching the criteria with the wildcard ("4*").

Any way to lookup the value in C2, search through multiple columns, and return the value that starts with "4"?

View 3 Replies View Related

VLookup Of Concatenate With Wildcard

Jan 31, 2014

I would like to Vlookup an association of data in a matrix where not all information is available. e.g:

Vlookup of following data (concatenate):

A1&B1&C1&D1
A2&B2&C2&D2
A3&B3&C3&D3

but in the mapping I would have a wild card (*) in certain columns (not always the same column)

The concatenate would be for exemple:

A1&*&C1&D1
A2&B2&*&D2
*&B3&C3&*

I know it works on the other way around (Vlookup of concatenate with wilrdcards) but I can not have wildcards in my source data.

View 7 Replies View Related

Vlookup With Wildcard Search

Oct 28, 2008

picture this.

Cell A1 = xyz3
Cell B1 = 5
Cell C1 = xyz3-hgfe-56f

I need to do a vlookup on C1 in range A1:B1 that will return B1 if C1 contains the string in A1. I've tried using the TRUE variable in the vlookup formula which does not require an exact match. This works for about 80% of my data but is not 100% accurate. Of course I've got a long list of data ... not just the 3 cells.

View 4 Replies View Related

Can Use Wildcard For R1C1 (lookup Value) In VLookup

Aug 21, 2013

know if I can use a wildcard on R1C1 notation for my "lookup value" in my Vlookup, so that the Vlookup searches for a not exact match, but on the whole contents of the cell, not just the first several letters? I am using VBA, and the vlookup is in each cell of a for next loop, so I cannot use an actual cell reference which is where I've usually seen, and used wildcards. I've tried changing the Vlookup to have the lookup value be "Cells(n,2)" instead of "RC[-2]" to no avail. I need to make the vlookup evaluate all the words in cell "RC[-2]" prior to returning the value I specified, because otherwise it returns the wrong value even though I sorted my lookup table Column A A to Z.

Start Date
End Date
Event ID
"Trans-Pacific Melodies" : an East-meets-West concert presented by Carolina International Orchestra and the China National Orchestra
10/06/13
10/06/13
22297

[code]....

View 1 Replies View Related

Add Nested ISNA & VLOOKUP With Wildcard Via Macro Code

Aug 23, 2008

I have the following vlookup working exactly as I want it to in excel: =IF(ISNA(VLOOKUP(LEFT(C2,10)& "*", NAME,2,FALSE)),"",(VLOOKUP(LEFT(C2,10)& "*",NAME,2,FALSE))) -NAME is a defined section on another worksheet

The problem I have is when I come to use this within a vba macro I'm writing the wildcard section automatically gets spaces added so it goes from "*" to " * " and excel doesn't like it! The code I'm using to write it into the cell is simply:

ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(LEFT(c2,10)& " * ",NAME,2,FALSE)),"",(VLOOKUP(LEFT(c2,10)& " * ",NAME,2,FALSE)))"

how to stop the spaces being added?

View 2 Replies View Related

Open File Using A Wildcard

Mar 7, 2007

Currently I have some code in a macro that opens a file like so -

Dim theDate As Date, thePath As String, wb As Workbook
theDate = Sheets("ActiveDon").Range("Eventdate").Value
thePath = "C:PROJECT" & Format(theDate, "MM-YY") & " xxx Data.xls"
Set wb = Workbooks.Open(thePath)

This works swell if the file is, say "01-07 xxx Data", but in truth the xxx is variable (but there's always only one). I'd like to make

thePath = "C:PROJECT" & Format(theDate, "MM-YY") & "*wildcard*" & " Data.xls"

so that it will open the file whether it's called "01-07 CA Data.xls" or "01-07 MA Data.xls", or whatever.

Is it possible to use wildcards to do this?

View 9 Replies View Related

Importing .txt File With Wildcard

Sep 10, 2007

I am trying to create a macro that imports a text file that has a changing name (it has date/time info in it). The file name will have some consistent parts and the rest I hope to be able to use wildcards on. In the code below, the path will be constant and the file name will always have "Heavy Water" in it. How can I change the code to import any version of the file?

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:UsersG-MoneyDocumentsEVElogsMarketlogsThe Forge-Heavy Water-2007.09.09 184540.txt" _
, Destination:=Range("$A$1"))
.Name = "The Forge-Heavy Water-2007.09.09 184540"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells

View 9 Replies View Related

Import .txt File With Wildcard

Sep 10, 2007

I am trying to import a text file into an existing worksheet using wild cards (the file name changes because it has time stamp info in it). I would like to be able to import the Heavy Water file even when the time stamp info changes. The code I have so far is:

Sub Macro3()
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:UsersapatoGrandeDocumentsEVElogsMarketlogsThe Forge-Heavy Water-2007.09.09 184540.txt" _
, Destination:=Range("$A$1"))
.Name = "The Forge-Heavy Water-2007.09.09 184540_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False.................

View 8 Replies View Related

Can Use Asterisk Wildcard To Save File In Multiple PATH?

May 30, 2014

Can I use the asterisk (*) wildcard to save the file in multiple PATH as long as it the file is in the master drive C:

Ex: the file now is in "C:Public" then can I use "C:Public*" so user can save file at their desire sub-folder such as "C:PublicHenry" as long as it has to be in drive C

View 6 Replies View Related

Speficying Which Characters Are Used In Vlookup?

Sep 29, 2009

I'm attempting to use Vlookup to find order numbers and the orders date of completion. However the reports that I am using are formatted differently so the order numbers are not exact. One spreadsheet lists the internal order number and the other lists the external order number. IE 123456 8119(123456001) Is it possible to specify a string of numbers within a cell using vlookup? Or should I look at another function?

View 5 Replies View Related

VLookup - Pulling Last Two Characters Of A Cell

Apr 7, 2014

In one spot I have a formula that is pulling the last two characters of a cell (right,xx,2).

It results in a number. I am trying to that number in a vlookup

(=VLOOKUP(C2,data!$C$1:$D$6,2,FALSE))

But it appears not to recongize the number (c2), because I am getting #N/A for a result, even though the number is on my vlookup range.

View 4 Replies View Related

Does VLOOKUP Not Work If Too Many Characters In Cell

Sep 25, 2007

I have a VLOOKUP formula that works when searching some cells but not others when both cases should work.

Q: Does VLOOKUP have a certain maximum amount of characters it can search? If a cell contains more than this amount of characters will VLOOKUP not work?

This is what seems to be happening on my spreadsheet. The VLOOKUPS that refer to the cells with more than the average amount of characters seem to fail. The same happens with INDEX.

View 9 Replies View Related

Use Wildcard "?" And VBA To Open A File

Jun 28, 2006

I'm trying to open a file using VBA, part of the filename changes each week. For example, Atlanta_9EM_weekly.rep will change to Atlanta_8RH_weekly.rep. the code I'm using is: fname=Atlanta_" & "???" & "_weekly.rep. It appears that the program is looking for file named: Atlanta_???_weekly.

View 3 Replies View Related

Can I Open A File By Searching For First Six Characters?

Nov 26, 2008

I have a number of project files that are maintained on the network.

These files are named "XXXXXX Project Name.XLS".

XXXXXX is the project number. This numbers are unique, so there is only one file per number within the directory. Project names are mostly unique, but sometimes they could be same for a specific site.

We recieve financial data in excel file format from home office. These reports reference only the project numbers, not the name.

I have created an excel program that extracts project numbers and related financial data from the report [from home office].

I would like to create a button or check box next to each project number in my program and link it to the respective file on our network. Once I access the local file on the network I could copy the financial data in its right place.

The problem is opening the file based on project number only, while the file names contain Project Name as well.

So, can I look up a file by only searching for first six characters of the name?

Or can the Hyperlink function work with some sort of wildcard? I can write the formulas to create the hyperlink and then write a macro to look for that file, but I need to be able to find and open the file by looking up only the first six characters.

View 9 Replies View Related

Deleting Characters From A Word File

Nov 10, 2006

I'm trying to automate a redundant task. I have several Word files in one central folder. I need to open each file, remove all paragraph marks (via find/replace all), copy the result, and paste it into an Excel sheet. This process will be repeated for each file in the folder.

The code I have is almost there, however, I can't get it to do the find/replace. Below is an extract of my code for the Word application part. The code for the copy and paste will be inserted after the replace action works:

Dim Wapp As Object
Dim i As Integer

WorksheetsLocation = TextBox1.Value ' this is the folder location brought in from an input box

On Error Resume Next
Set wApp = GetObject(, "Word.Application")
If Err.Number 0 Then 'Word isn't already running
Set wApp = CreateObject("Word.Application")
End If
On Error GoTo 0

View 9 Replies View Related

Name File Based On First 2 Characters In A Cell

Aug 11, 2006

I'm trying to find a way to save a file based on the contents of cell A2. I've modified the code to Create Worksheets for Each Item in an Excel Table of Data, so that it creates a new workbook for each item. I am also trying to get it to save the master workbook based on the first 2 characters in the cell. Here is the code I have, at the moment (the FAIL.xls is what I put in for testing)

If Range("A2").Value = "YE*" Then
ActiveWorkbook.SaveAs "YE_Master.xls"
ElseIf Range("A2").Value = "NE*" Then
ActiveWorkbook.SaveAs "NE_Master.xls"
Else: ActiveWorkbook.SaveAs "FAIL.xls"
End If

I've tried this, string arrays and everything else I can think of, and it's failing every time.

View 3 Replies View Related

Remove Illegal Characters From File Name

Dec 7, 2006

Remove Illegal Characters From File Name ...

View 5 Replies View Related

Read In Multibyte Text File Characters

May 28, 2008

I have a Text-File with characters coded in simple chinese (gb2312) rsp. codepage 20936 and I want to read this file, paste the content in my worksheet. Later (after a chinese edited the text) I've to write this back to a text file.

The only way I found to make this, is by copy/paste e.g. from WordPad, but I want to automate it with VBA.

View 6 Replies View Related

Excel 2010 :: Save CSV File With Non-English Characters?

Dec 5, 2012

I run excel 2010... I have xls file (see file attached) with both English and Non English characters.

When I save this file as xls or xlsx - everything is good, but when I save the file as CSV and try to open it later - I see that the English characters stays the same but the non English characters become gibberish.

How can I save a file (that include some non English characters) as CSV without loosing the non English characters? Is there a way to do that from within the excel 2010 menus? Or maybe there is an external tool?

Attached file : 913365454523.xls‎

View 1 Replies View Related

Change Characters In Specific Position Of A Text File

Aug 6, 2008

I have a very large file of data, over 500,000 rows, opening in one sheet in Excel is not an option with my current version. In each row I need to change the characters in positions 41-44 from whatever they current are, to '9999'. I'm sure there has to be a way I can do this using vba, does anyone have a sample snippet of code, or another post they can point me too?

View 9 Replies View Related

Delete Characters Unallowed In File Names In Cell Text

Feb 14, 2012

How can I delete characters unallowed in file names in cell text all in one go instead of below long macro. (i.e. can you minimize the below vba)

These characters that I wanna delete
"/", "", ":", "*", "?", "< ", ">", "|"

Code:
Range("A1").Select
Selection.Replace What:="""", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

[Code].....

View 6 Replies View Related

VLookup If Match First 2 Characters Of One Column With Another Column

Feb 24, 2014

I have a table as below (you can also refer to the attachment) and need to do the vlookup with EE Code if the left 2 characters of "EE Code" = 91 to 95.

EE Codedeptif left 2 characters of EE code= 91 to 95, vlookup dept code
921600012345678
vlookup arraryA12345623456700
39914123 test785637039914123
38214125 test915580057889056
40964197 test932660038214125
77014155 test913580039914123
345699940964197
945800077014155
953790300123432
915800012345678
045699957889056
585637039914123

View 3 Replies View Related

Vlookup And File Name Changes

Oct 24, 2013

Vlookup from another workbook that changes name daily.

Basically what I want is to put the date in cell M26 (formated YYYYMMDD) and the vlookup would got to YYYYMMDD.XLS and get the data I need.

When I change the value of M26 it will get other data and so on...

This is my current formula:

=IF.ERROR(VLOOKUP(A1;'C:UsersMiguelGoogle DriveVENDAS[20131020.xls]Sheet1'!$B$11:$G$50;6;FALSE);0)

I need to get the data from other days of the month... 20131021, 20131022, 20131023... which I write in cell M26.

I tried the indirect function, but I think I'm failing to get the " ' & in the right positions... Also the 20131021.XLS, 20131022.XLS, 20131023.XLS workbooks will be already opened, so that's not a problem.

View 4 Replies View Related

VBA Vlookup From Another File

Dec 3, 2009

I am working on a new project and am trying to use Vlookup in VBA.

The code i have so far is:


For Each cell In Range("B2:B" & CurrRow)
cell.Value = Application.WorksheetFunction.VLookup(cell.Offset(0, -1).Value, Workbooks(Comparefile).Worksheets(CompareActive).Range("A:A"), 1, False)
Next cell
CurrRow is the last row in the active sheet.
I have a workbook defined as Comparefile (not the entire filepath) and a worksheet as CompareActive.

Both files are opened...

The macro works untill this line, i don't get an error message but i just don't get any results...
Is there anything i do wrong? I really can't get it to work.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved