Loop Through Files, Match & Return Data

Oct 26, 2006

I found a use for the “Looping Through Workbooks” code (recommended by Dave in my last thread - thanks Dave).

I have a number of small files in "C:Files" that I’d like to loop through and find a match with data from a Master (in another folder) workbook as follows.

1. Match columns ”C” of each of the files with column ”H” of the Master.
2. For each matching cells, insert data from column “I” of the Master (“ Date” field) to into col “D” for each row of the individual files (about 300 rows)
3. Save & Close the workbook and loop to the next one in that folder

All that's needed here is code to search, match & paste, to insert into Dave's "Looping Through Files" code.

View 9 Replies


ADVERTISEMENT

Loop A Comparison And On Match Return Earliest Date?

May 25, 2012

Using online examples I am usually capable to reconstruct whatever I need. However, it's the combination of things I need to do now, which has left me banging my head on the keyboard for days now

Here's the data I am working with:

Sheet 1:

Sheet 2:

And here's what I need to get done:

From sheet 1, select first ID entry from Column A.Find matching ID in Column A of sheet 2On match, find in Column B the earliest date belonging to the concerned IDCopy that earliest date next to the corressponding ID in Column B back on sheet 1Return to step 1 and repeat for next ID entry. Do until end of list (sheet1)

So the result should look as following on sheet 1:

The major issue I am having is the combination of step 3 and 5. Because it probably means something like moving through an array that's within an array through which one is moving. And I am just missing that bit of experience that allows me to make that thinking step. I just keep falling off if you know what I mean...

View 3 Replies View Related

Convert Horizontal Data To Vertical - Loop Through Files In Folder

Apr 9, 2014

I have a set of workbooks-all located in the same folder-in which data are shown "horizontally"

i.e. file 1 - sheet1 looks more or less like this:

1 2 3
4 5 6

same with the other files.

what I am trying to achieve is that in another woorkbook, all the data are copied in one vertical vector

i.e

1
2
3
4
5
6 from file 1 and then
1
2
3
4
5
6 from file 2

I can seem to be able to resize the array to the correct size but the only values copied are for the last file. in the example that I have above, the result would look something like:

<empty>
<empty>
<empty>
<empty>
<empty>
<empty>
1
2
3
4
5
6

This is the code I have so far:

Sub Consolidate()

Dim a As Workbook
Dim b As Workbook
Dim x, y(), i&, j&, k&
Dim myPath As String
Dim filename As String
Dim wb As Workbook

[Code] .........

View 11 Replies View Related

Nested Loop Not Working; Pulls Data From Multiple Files Into One Table

Aug 8, 2006

I've got this code that pulls data from multiple files into one table. the file name is in the top row, and each file's title, and two different sums display below that. Then it is supposed to loop through and display each task and the start and end date for that task within each file. After that the first loop brings it to the next column and file. The primary loop works fine, but the inner loop only seems to run once, as I get only the first result for each file. All I can figure is that maybe my row numbers aren't resetting like they're supposed to, or I'm completely overlooking something, which is likely, as VBA is by no means my forte.

Sub Worksheet_Calculate()
Dim sBook As String
On Error Resume Next
Application.EnableEvents = False
Col = 8
' Sets start column
Do
sBook = Cells(2, Col)
' References file name
TaskRow = 6 ........................

View 5 Replies View Related

Loop Through Multiple Files And Call Macros (but Unable To Loop)

May 14, 2014

Macro which loops through a number of files and calls the same macro in each of them. Unfortunately when I add "Application.Run..." to the code, it no longer loops through the process and instead stops after updating the first file in the loop. If I remove the "Application.Run..." code and add any other code, the loop works fine and it continues through the process repeating all the steps for each file found.

Why it stops after one file when using "Application.Run..." to call the macros?

NB I have a list of path and file names starting in row 8 of columns A and C. Each file in the list has a macro called UpdateS1 and promoupdate1.

Sub C_Run_Loop_Macro()
Dim lastRow As Long
Dim i As Long

[Code]....

View 4 Replies View Related

Open Set Of Files To Fetch Data Starting From A Master File Where The Links Are Given Using A Loop

Aug 29, 2006

I am trying to open set of excel files to fetch data starting from a master excel file where the links are given using a loop. Some of files given as links are either absent or the link is wrong.

* What is the syntax to find if the link is correct/present

* What is the syntax to find if the file is present in specified location

* What is the syntax to find if the file is password protected to open

* I use error handler to resume the next statement if OPEN statement fails.It works fine inside the loop only for first two files.

For example if the third link is errorneous the error handler fails and RUN TIME ERROR occurs.

View 5 Replies View Related

Match Data From 2 Columns And Then Return Value From A 3rd One

Oct 4, 2008

1. i am comparing text
2. i have >100 countries, and with vlookup i have to type every single one, and this is what i want to escape (mb i need to refer to an array, or string, or no idea)
3. mb a solution could be sth with index, or match, i just tried it, copying some similar formula from www, but it didn't match the countries, and return the value respective for that country.

E.g. I have China in col1 (1st file), #employees 10 in col2 (1st file), and China in Col 1 in 2nd file. So, a formula should match China & China from both files, and return 10 as number of employees.

View 2 Replies View Related

Macro To Match And Return Data

Oct 25, 2011

I'm trying to match the data in a column from one excel file with another column of data from a separate excel file. If there is a positive match, I would like it to return a cell data on the same row in the following column on the first excel file. I understand that this can be done with vlookup but because the data in separate excel file contains commas and thus I can't get a true match.

To visualize what i'm trying to illustrate, i'll breakdown into steps.

I would like to match the data below in excel file 1 to excel file 2.

Excel file 1

83883
89342
83603
84445
434961

Excel file 2

83883
89342, 541879
83603
522354, 84445
83985, 434961

Lets imagine that there is another column of data next to the list of data in excel file 2. If there is a positive match between excel file 1 to excel file 2, i would like to return cell next to the data in excel file 2 on the same row.. to the cell next to the data in excel file 1.

This is difficult as I have a column with few thousands of such data.

View 9 Replies View Related

Match Data And Return Datalength?

Nov 26, 2011

I have data

in A1 = ADGSTSJDUDKDKFHDHFGFGDHAPPPPSODIDFKLFK

in B1 = GDHAPPP

I need to show a number of characters (=LEN(B1)) in C1 if data in B1 matches to A1

so in this case I expect C1 will show 7

View 6 Replies View Related

Match Data In Column Against Another & Return Value From Another

Sep 27, 2007

I have data in 1 column that I want to match data in the second column. If it matches a value in the second column I would like it to return the value from a row in the third column.

View 9 Replies View Related

VBA - Carry Out Task On Set Of CSV Files - Return Values And Leave Files Untouched?

May 14, 2013

Trying to write a script to carry out a number of tasks but is there code to enable an Excel to look in a particular file for the list of csv files - carry out the tasks and return the appropriate values in the Excel sheet whilst leaving the original csv files untouched?

View 2 Replies View Related

Excel 2007 :: Match Data In Two Columns And Return Header

Apr 20, 2012

I have two columns with team abbreviations, one simply says whether they are AL or NL the other is the result of a search with multiple occurrences of the team name. I need to match the long list with the short and put the column header AL or NL in the adjacent cell.

I'm using Excel 2007

The last formula I tried was =INDEX($AY$1,MATCH(BB2,$AY$2:$AY$15,0),0)

The screen shot actually just part of the sheet copied since the screen shot look like a broken html page Ok I give up. I copy and paste a jpg it turns into code, I copy and paste the spreadsheet and formatting vanishes, I don't have a URL for the picture... I did look at FAQs and didn't find picture rules but I will look again. Meanwhile. . .

It's just six columns of data. The short list is in AY from AY2 : AY15
The long list is in BB from BB2:BB505
The column header "AL" is in AY1 and NL is in AZ1

View 2 Replies View Related

Using VBA To Find Exact Or Nearest Match And Return From Data Sheets

May 15, 2014

I has number sheets with thousands rows of unsort data. I need to find the price, with optional name and date if given, to return the rows values.

Example from Summary sheet, to find the price range and return 3 rows (even there are four set of answers, highlighted in light blue), with sorting the highest price first.

Summary Sheet
Sheet name
S01
S02
S03
S04
S05
S06
S07

Product
1
2
3
4
5
6
7

Search Fields

[Code] ........

View 1 Replies View Related

Offset / Index / Match - Using 3 Functions To Search For And Return Values From Data Sheet

Jan 19, 2014

Trying to grasp the concept of using these 3 functions to search for and return values from a data sheet.

The attached spread sheet has performance data for a group of employees.

What I need to do is find a particular employee then return a value for one of the category's.

For instance, I need to find "10TE03 ANGIE HOLLIS" Parts Usage on color or cell C10 in the attached sample.

Sometimes new category's are added to column A adding to the number of rows so a simple offset is not reliable.

Once I get that working, I then need to use a named range to total and average different data points for groups of employees by teams.

Maybe Offset-Index-Match is not even the way to go here?

View 7 Replies View Related

Index And / Or Match Formula With Multiple Match Required To Return A Value

Jan 21, 2014

See attached file, "Rate Sample Index-Match Formula".

I need a formula to return the value at the cross section of two (2) lookup values that match. This formula will be input into column D under, "RATE" on the 1st tab, "TEST FILE".

In the 1st tab, "TEST FILE" there are a series of columns as follows;

A = Service
B = From
C = To
D = Rate

In the 2nd tab, "RATES" there is a series of rates with drivers From (green) & To (blue)

The formula needs to do the following;

1. Lookup the "From" value in column B on tab, "TEST FILE" and match to column B2:B59 on tab, "RATES" both highlighted in green
2. Then Lookup the "To" value in column C on tab, "TEST FILE" and match to row C1:BH1 on tab, "RATES" both highlighted in blue
3. Then return the value at the cross section of the match "From" (point 1 above) & "To" (point 2 above) in range C2:BH59

For Example;

The rate From SYD To CBR = 0.33. I have highlighted this in yellow on both tabs to show where the formula needs to lookup the data to return the answer.

Additionally, if we were to add service as an additional lookup match how would this work?

View 3 Replies View Related

Match 1st And 2nd Columns In 2 Sheets And Return 3rd / 4th And 5th Column If Match

Dec 15, 2013

[URL]

What im trying to do is match columns A & B from AUDIT Sheet to Columns A & B in MASTER sheet. If they match then pull columns C, D & E from MASTER into AUDIT.

View 9 Replies View Related

VLOOKUP / INDEX And MATCH To Return All Values That Match

Feb 4, 2014

I have two sheets of data , sheet A and Sheet B.

Sheet B contains a column called "Name" and for each name, and for each name there are corresponding numbers. In sheet A, I have a "list of interest" in column A. What i want to do look through the data in sheetB to find a match from the list of interest and return the corresponding letters, located in column A.

I have filled out the first two rows of results that should be returned as an example.

One idea i had was to put a vlookup formula in each column result 1 to result 6 so i can catch all 6 "Serves" columns from column B, but there may be duplicates in the serve columns and vlookup only reports the first match.

View 3 Replies View Related

Loop Through Files In A Folder

Mar 11, 2014

I would like to create a Macro which does the following task. I have a Workbook Master.xlsx with a worksheet "source". The path of Master.xlsx is C:Test

In the Directory C:Testprojects i have about 50 files which all contain the String "Forecast" in their filename. These Forecast Workbooks need an update in the Worksheet "actuals"

So, i need a Macro that copies WS "source" from WB "Master" to WS actual in all WB's Forecast.

View 5 Replies View Related

Loop Through Files And A Run A Macro

Aug 4, 2009

I want to loop through all files in a directory and run a macro named "Main" in each file. The file that contain the loop-macro will stay in the same directory as the files I loop through.

View 4 Replies View Related

Loop Through Files On Share

May 30, 2008

I have the following code, and it is working well for one file. I'm trying to figure out how to do a loop to go through each region folder and update, save, and close each file just like the code below is doing for one file.

There are 10 region folders
Each region folder contains 8 or fewer files.

Please see the sample code below for the sample file location.

Sub TrendByRegion()

Application.EnableCancelKey = xlDisabled

Set wbOpen = Workbooks.Open(Filename:= _
"S:High Level ShareReportsReports by RegionPivot Table Files
Region 1Catalog File.xls")

wbOpen.RefreshAll

ActiveWorkbook.Save

ActiveWorkbook.Close

End Sub

View 9 Replies View Related

Using Sendkeys And Loop To Download Files

Jul 7, 2012

I have created some code that directs me to a web page, and i use send keys to navigate my way to the input field. In this input field I am trying to put a cell value in a range use send keys to download the file, and the repeat the process for the next cell.

Here is my code so far, it does everything right up until pasting the copied cell into the input section of the web page - the issue is it is pasting nothing

VB:
Shell "C:Program FilesMozilla Firefoxfirefox.exe " & "https://website.com", vbMaximizedFocus
Application.Wait Now + TimeValue("00:00:05")
SendKeys "{TAB}"
' Input username

[Code] .....

View 9 Replies View Related

Loop Throough All Files In Folder

Apr 28, 2009

I did search but I couldn't find what I want. I have a load of .csv files in a folder. I want to loop through all files, run the macro below and close the .csv file without saving. My macro copied data onto another workbook and saves, but I don't know how to loop through all files within a folder.

I need some code that will open each file in the folder, run the macro below and close it again. I don't know how to put code tags in so I have put the code in a .txt.

View 3 Replies View Related

Macro Run As A Loop On All Files In A Given Folder

Mar 22, 2008

macro to run as a loop on all files in a given folder but allow for the option to Browse for the folder I want this to run in?

Sub CleanUp()
Dim i#, rng As Range
With Application
.ScreenUpdating = False
.DisplayAlerts = False
For i = Worksheets.Count To 1 Step -1
If Worksheets.Count = 1 Then GoTo e
Set rng = Intersect(Worksheets(i).[11:11,13:13,23:23,25:25], _
Worksheets(i).UsedRange)
With rng
.Replace What:=Chr(32), Replacement:="", LookAt:=xlPart
.Replace What:=Chr(160), Replacement:="", LookAt:=xlPart
End With...........................

View 10 Replies View Related

Loop Through Files, Using Filename As String

May 8, 2008

I have some code that I use to convert files, and it works well, but I would like to make a couple of changes to it. If I run it now it requires user input for every file that is processed, using the input box. Instead of this I would like to use the filename minus the extension as this input.

The next step is selecting the file using the . Instead of this I would like the user to browse for the folder containing .csv files, and select the folder, then use the macro to loop through all files in the folder.

View 9 Replies View Related

Loop Through Files In Dated Folders

Nov 30, 2008

each folder is named by date, for example a folder name may be 04-01-2008. Within each folder there is one xls file named daily.xls.
(this never changes)
In date order from the folder name, I need to open each daily.xls run my code, then close daily.xls and then open the next one etc until there are no more.

example path is C:4-01-2008daily.xls

View 9 Replies View Related

Loop Through Folder, Run Macro On All Files

Mar 5, 2009

I am trying to process 60+ data files. I've recorded/written macros that do all of the processing, and now I would like to write a loop that will go through a folder with the data files (.txt, tab delimitted), and run the code on each file. I am using the Mac Version of Excel 2004. I have tried to adapt some code that I found in the forums, but after fixing a few errors, now nothing is happening when I run the code. The code is below. I have it in a module in the VBA editor.

length of the code; I'm assuming the problem is in the very beginning or end (the code I added for the loop), as the code in the middle that does the actual processing works.

Sub ProcessData()

Dim strDocPath As String
Dim strCurrentFile As String

strDocPath = "Macintosh HD"":Users:thomasarmstrong:Desktop:SF08_Macrosv2:Loop_test:directory:"
strCurrentFile = Dir(strDocPath & MacID("TEXT"))

View 9 Replies View Related

Loop Through All Files In Subdirectories Of A Directory

Feb 26, 2010

I am having a bit of trouble with a bit of code. I have a bunch of code that I want to run on all excel files that live one level down from my main directory.

I have a general directory (orders) which contains a variable amount of subfolders each month (in picture below, 'Dan', 'Frank', 'Steve'), and I would like to look through all the excel files in 'Dan', 'Frank', 'Steve', but not in the main directory (orders), or any subdirectories within 'Dan', 'Frank', 'Steve'.

I am able to loop through all the excel files in the subdirectories individually if I hardcode the directory names, but as they are variable,

View 9 Replies View Related

CreateTextFile In Loop Not Creating Files

Dec 7, 2006

I have a problem with the code below. When i place the statement "MsgBox ("Done at " & Tester_array(N))", i am able to get the files i need. However, when i remove this statement, no files can be retrieved. Why is that so? Can somebody advise ?

Option Explicit
Sub File_Transfer()
Dim N As Integer
Dim fs As FileSystemObject
Dim A As TextStream
Dim Lotid As Variant
Dim Testerid As Variant
Dim dRetVal As Variant
Dim Tester_array(1 To 15) As String
Tester_array(1) = "aaa"
Tester_array(2) = "bbb"
Tester_array(3) = "ccc"
Tester_array(4) = "ddd".......................

View 2 Replies View Related

Loop Through Directories & Files With Dir Function

Jan 18, 2008

I am trying to do is loop through a series of directories under a target directory. When a directory is found, it looks within that directory and finds any .xls files, loops through those and copies/pastes them to the new target directory. I have managed to get it to work only on the first iteration of the loop through the directories, then it crashes. The error code is "Invalid Procedure Call or Argument." Here's the

If valFilePath = True Then
MsgBox (msg)
Else
'Search for directories within source directory
strDir = Dir(ebsSource & "", vbDirectory)
'If a file has been found
Do While strDir <> ""
If strDir <> "." And strDir <> ".." Then
strFile = Dir(ebsSource & "" & strDir & "" & "*.xls")
Do While strFile <> ""
'Copy .xls files and paste in destination
copySource = ebsSource & "" & strDir & "" & strFile
copyDestination = ebsDestination & "" & strDir & "" & strFile
FileCopy copySource, copyDestination
strFile = Dir
Loop
End If
'Crashes here after looping through the first iteration of Excel files
strDir = Dir
Loop

View 2 Replies View Related

Loop Through All Files Rename And Save In Another Folder

Feb 27, 2013

I have excel files in a folder, I need a macro to loop through all files, rename them and save them in another folder. the file's name is available in a cell in each file, I am able to get the file name as text (I had to dig a little to get the text as it was between ""). Well I am stuck right now because the macro is doing what it's supposed to do for one excel file only and does not continue with the rest of the available files for a reason.

VB:
Sub RenameAllFiles()
Dim Bk As Variant
Dim n As Integer
Dim NewName As String

[Code] .....

View 8 Replies View Related







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