Multiple File Searches In One Macro
Apr 11, 2007
Is it possible to do multiple file searches in one macro? Or does the data get stored in such a way that they'd conflict? First I want to search a folder for other folders. Then I want to search each folder for a certain type of file and then run a macro that does a whole bunch of things to each file. Including saving the file as a different format in a folder with a similar but different name. Currently I have everything written except the loop that will go through each folder. I was kind of hoping to use a 2nd file search to do so.
View 5 Replies
ADVERTISEMENT
Jan 10, 2014
To summarize - Columns B through E are current MLB Players who I am trying to compare with the data in Columns G though J. My goal is to both use index and match to find the following in Column L: A comparable player name using the criteria of having an exact match for both position and age, but the closest approximation match between Columns E and J.
I have deleted a significant amount of player data to be able to upload the document on this site.
I am trying to find a way to combined exact and approximate matches in one single formula, but have been unsuccessful thus far.
View 10 Replies
View Related
Jan 9, 2013
I love Excel and love learning new things to do in it to create some amazing number-crunching techniques. I have created a workbook with multiple worksheets [one for every month in the year]. I searched this amazing forum and succesfully implemented the search box macro to find and copy the found data on a separate worksheet.
My question is that the macro I found in the this post Creating a Search Box in Excel only searches for the data in one worksheet. How do I tell it to search for the inputted data in all worksheets?
Here is the first part of the macro. I was trying to fiddle around with the strDataShtNm but to no avail.
Code:
Sub myFind()
'Standard module code, like: Module1.
'Find my data and list found rows in report!
Dim rngData As Object
Dim strDataShtNm$, strReportShtNm$, strMySearch$, strMyCell$
Dim lngLstDatCol&, lngLstDatRow&, lngReportLstRow&, lngMyFoundCnt&
On Error GoTo myEnd
'*******************************************************************************
strDataShtNm = "Sheet2" 'This is the name of the sheet that has the data!
strReportShtNm = "Sheet1" 'This is the name of the report to sheet!
View 2 Replies
View Related
Jan 21, 2009
I have data created by a program where it gives me a column "Code" with different text categories (in this case names of people). One person appears multiple times in this column as it track each time they register a statistic for the sporting event. The statistics are text fields registered under the column Labels
So I have two columns titled "Codes" and "Labels"
I need to extrapolate from this data a count of times each statistic happens for a specific player.
Example:
Code: Labels:
John FGA
John FGM
Joe FGA
Joe OREB
Zach TO
What I need is broken down by player with counts of each category FGA, FGM, OREB ect specific for each player creating a box score for the event.
View 9 Replies
View Related
Feb 27, 2009
I would like to ask recommendations whether to use DSum or Vlookup, in order to do multiple search.
We receive a flat file on a daily basis, which contains four (4) columns. They are Group, Team, Taskname, and Number_of_Employees. The first three columns have text properties and the last one has number property. We copy this sheet into our workbook. There are more than 1,000 rows. I name this tab, "TotalWorkersbyTask".
Each group (first column) has its own tab. We try our best to name each tab to match the name of the first column.
We would like to find the unique value, in order to put the correct Number_Of_Employees into the correct column on each sheet. However, the relationship per column is many to many, which makes it more difficult.
For example,
Group,Team, TaskName, Number_of_Employees
12GR, Robert, Analyze raw data, 2.5
12OV, Tim, Analyze raw data, 1.25
12OV, Bob, Prepare reports, .85
16CJ, Jim, Prepare reports, .50
1008, Mary, Analyze raw data, .25
............
Each tab, such as 12GR, will have the task name on the line "Analyze raw Data" which matches the information "TotalWorkersbyTask" tab.
The problem is, if I do a Vlookup for Taskname only, only the first value of 2.5 will return in to the Number_of_Employees column.
For the tab of group 12GR, the logic supposes to be -> if the group is 12GR, and the name of the task equal to the one appeared in "TotalWorkersbyTask", return me the value of 2.5 in the specific row. With the same logic, on the tab 12OV, the same task name should return me the value of 1.25 as well.
As you see from the example, line 2 and line 3 are the tasks in the same tab, from the flat file received.
Then, this formula can be copied and populated into other rows and other sheets as well.
*********************
Questions:
1. Is there anyway to write the formula to gather the required information? My thought was using the Vlookup. However, it can see only one column.
2. Do I need to rename all the tabs to match the name of the first column received from flat file in order to make it more efficient? (In the present, there are some tabs that match the name, such as 12OV, and some tabs are something like ->Engineering, Research, etc.)
View 9 Replies
View Related
Jul 10, 2014
I've been trying to get power query to search a list of parcel numbers on an assessor site: [URL]. The problem I'm getting, every time you put in a different parcel number, you get the same URL for the results. I want to extract just a few pieces of information for each parcel search, but I can't initiate a search through excel. I'm thinking the problem is in the Data source settings. How can just get it to show me the assessors page results for a single parcel search in excel?
View 5 Replies
View Related
Sep 21, 2013
I have a WB with multiple sheets. I have a Feed sheet that automatically pulls data from the web. I have a Scores sheet that currently has all head to head matchups each week for an entire season. So I want to check the cells on the Feed! sheet to find a partial match and if partial match exists then copy cell that contents score from feed sheet to the score sheet next to the appropriate teams name. Currently I have to manually enter all scores each week for the rest of my WB to update.
I need to look at Cell A3 (on the Feed sheet) which has "New York Jets" then search for a partial/similar match on the score Sheet (which is NY JETS, in this case). NY JETS could be in column B (rangeB2:B257) OR column D (rangeD2:D257) BUT I need to search by row, not column, then once a match is found check the cell to the immediate right and only if the cell is blank copy data from the (!feed) sheet to that blank cell on the (!scores) sheet
This is what happens if working right =
look at cell A3 on the (!feed) sheet = "New York Jets" then search (!scores) sheet Column B and Column D by row for a partial match, finds "NY JETS" as match in cell D8, if cell E8 is blank then copies cell H3 from (!feed) sheet, and pastes to cell E8 on the (!scores) sheet, if cell is not blank continues search until 1st blank cell to the right of matching cell is found (as there will be mutiple matching cells with blank cells to the right but I am only interested in the 1st blank cell found, once found and data copied the process is done and then starts over with cell A4)
Look at cell A4 (!feed)= "New England Patriots" then search (!scores) sheet Column B and Column D by row, finds "NEW ENGLAND" as match in cell B8, if cell C8 is blank then copies cell H4 (!feed) sheet, and paste to cell C8 (!scores)
Once this is done it moves on to the next cell in the next row on the (!feed) sheet, A5, to find a partial match for the data in that cell. I need to continue the search for each cell A3 to A74, and if no match is found to move on to the next cell A6... (based on the way the data is pulled in from the web there are some blank cells as well as some cells that say Game Final, this data won't be on the scores sheets in column B or D).
View 1 Replies
View Related
Sep 20, 2012
I have 400+ workbooks in a folder. If they are all .xlsm (macro-enabled) workbooks, how can I import a macro (.cls file) to a particular sheet (sheet1) in each workbook using VBA so that when a user on another machine opens one of them, the macro works? Right now, I have this code to loop through all workbooks; I only need the import function to go in the middle. Copying and pasting or importing manually 400+ times is just plain unrealistic!
VB:
Sub OpenfileUpdate()
Dim strFile As String
mFolder = "C:files1Files"
strFile = Dir(mFolder & "*.xlsm*")
Do While strFile <> ""
[Code]....
View 9 Replies
View Related
Mar 27, 2014
how to save multiple file with other names in the same folder
View 1 Replies
View Related
Aug 15, 2006
The reports I build work from a basic template with 10 or so separate, slightly different versions. In order to make them I currently update the data in the template and then click on "File>Save As..." ten times, renaming the file as appropriate.
Example:
Overall Report for yyyy/mm/dd
Partner A report for yyyy/mm/dd
Partner B report for yyyy/mm/dd
ad nauseum..
Is there an easy macro that could save and rename my file multiple times? It would need to accept manual input for the date. Preferably it would be something that I could easily tweak to change the filenames and number of reports saved as this is most of what I do.
View 6 Replies
View Related
Jan 25, 2010
I need a macro to do the following
1.Get list of .xls files from specified folder.
2.Append the files in new workbook in same folder.
Ex:
D:/users/excel/Sample_sheet1.xls
D:/users/excel/Sample_sheet2.xls
D:/users/excel/Sample_sheet3.xls
I want to get the list of Sample_*.xls and
create Sample.xls master file which adds the above three .xls as sheets in it.
Actually my macro as below
View 10 Replies
View Related
Oct 11, 2013
I am trying to write a macro to open a specific file, but need to search multiple folders within folders to find it.
The file name I need to open is "Escalation Adherence-Details " & Format(Date, "mm-dd-yy") & ".xlsx".
I need to drill down to the Adherence Report folder and then have the macro search through folders for each year (2012, 2013, 2014, 2015, etc) and then each month within each year (1 January, 2 February, 3 March, etc), at which point I would then find that day's file.
So far I have the following:
Code:
Sub Open_ESCL_Report()
Workbooks.Open Filename:=*****.****.****.******.comsharesPurchasingTeam XEscalationAdherence Report & "Escalation Adherence-Details " & Format(Date, "mm-dd-yy") & ".xlsx"
End Sub
Each file is stored in it's respective month folder as .....Adherence Report(Year)(Month)(File).xlsx
View 3 Replies
View Related
Feb 7, 2014
I am trying to create a master file, with a macro built in, that will allow multiple users to use it at once. The macro is to open a dilouge(sp sorry) box showing the contents of a specific folder, allow the user to selct one of the sheets, then copy and paste the set details from the hidden tab on this sheet (All sheets will be the same barring title), append the details to the master list in the first empty row.
I've got this far thus
Sub Macro1()
'
' Macro1 Macro
'
'
' Modify this folder path to point to the files you want to use.
FolderPath = "My Folder name here"
' Set the current directory to the the folder path.
ChDrive FolderPath
[Code] .....
So I can copy the row, but I can't get the first part to open .
View 3 Replies
View Related
Oct 29, 2013
I have approx 11 files in one folder and one master file with same format. 11 files are split user wise and user inuputting the remarks against the invoice in coloum Y and Z in their respective files. I want macro/forumul to collect all the remarks coloum from all users to master files against the respective invoice no.
user file format :
file name temp-1.xlsx
A B Y Z
USER
INVOICE
Remark
Follow-up Date
[Code]......
View 1 Replies
View Related
May 13, 2013
I currently have a spreadsheet setup on a Macro to send & distribute a message from an 2007 Excel file to multiple users through Microsoft Outlook 2007. It is currently setup to send as an attachment. I need to find a way to have this file be sent as a PDF file. I know you can save Excel as PDF's so there must be a way to send them & attach them to as a PDF.
View 3 Replies
View Related
May 27, 2014
I need a formula that can return the value from a cell in the same row it searches. The row is unsorted and has blank cells. Seacchs row A12:X12 returns the text in C4 (data validation List) in A19 on a different sheet.
View 6 Replies
View Related
Mar 19, 2014
Table 1: in week 6, there is 1 unique client, in week 8 there are 2 unique clients,...
ClientsPMCWeek
AAAMoscowW6
AAAMoscowW8
AAAMoscowW8
BBBMoscowW8
BBBMoscowW9
CCCMoscwW9
DDDSTPW9
I want to create the following (not with pivot-table)
W6W7W8W9
Nr Clients 1023
I tried with combinations SUMPRODUCT/COUNTIF/... The problem is that you must combine with W6/W7/W8...
View 1 Replies
View Related
Mar 6, 2014
Problem is Findstring finds cells that contain "SS1" AND "SS1 EXT". These are separate grades.
I need Findstring to only find SS1. Using xlWhole doesn't work for me, as the cells also contain other data. For example: " SS1 48 /"
CODE:
FindString = "SS1"
Findstring1 = "SS1 EXT"
With Sheets("Sheet1").Range(Rng4)
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
[Code] ........
View 1 Replies
View Related
Dec 9, 2013
I need to figure out a vlookup function that searches for two variables and return an answer. such as the example attached;
vlookup -- 12253 & 605.34 (if these are matching then) 131001
View 5 Replies
View Related
Jul 28, 2014
I am trying to create an IF function that searches for the word "No." I have created a table with monthly payments and if the payment is >= 0, it returns the word Yes. If the payment is
View 3 Replies
View Related
Dec 24, 2013
Develop one system that can search and make changes on the user form. I already find the solution on my first worksheet(USER PROFILE) but when i used the same coding on my second worksheet(INVENTORY&RET), it doesnt work. Here is the example of my INVENTORY&RET worksheet example.xlsm
Hit MAKE CHANGE button to see the problem.
View 2 Replies
View Related
Feb 11, 2014
I am doing a spread sheet for participation in a class. What I want is for whenever a student is absent, i.e. has a 0 in their point box for the day, that cell turns red. I have tried to make the .find method work but it has been uncooperative and so far and I can't seem to get it to even run. This is what I have so far:
HTML Code:
Private Sub For_Loop_Click()
Set v = .Find(0, LookIn:=xlValues)
For Each v In [B6:B46]
Do
If v.Value = 0 Then v.Interior.ColorIndext = 3
Set c = .FindNext(c)
End If
End With
End Sub
View 2 Replies
View Related
Jan 5, 2009
I've got a spreadsheet (sheet1) that has details of vehicles helpline numbers.
I want to create search box on a seperate spreadsheet, where the user enters a reg number, and it is searched for in sheet1. The row containing the result is then shown under the search box. Is there anyway to do this?
Also, I'd like to lock sheet1 so people dont see anything other than their search criteria. (ideally, I'd like sheet1 to be in a totally separate workbook).
View 9 Replies
View Related
Jun 23, 2009
I am looking for a formula that searches a range of data and then returns the sum of the numeric values in the adjacent cells. I have a calendar style spreadsheet with 2 columns for each day. In the first column it has a list of names and the second column a number.
I would like a cell to return the sum of the numeric values next to each name. All the names are entered into the columns randomly and are unorganised. The same name will appear multiple times in the range and have different numbers next to it each time. I am currently using (for example) =COUNTIF(H6:AL14,"bob") to tally up the number of "bobs" but really what i want to do is find a formula that in plain english does:
Search a range of columns and where cell = "bob" take the value of the adjacent cell (to the right) and total them up!
i have attached an image - i hope it makes sense what i'm trying to do - i would like Q3 to total up the numbers to the right of all "bobs" across all the days - so it should return 42
View 2 Replies
View Related
Feb 13, 2008
This is a sample of the data with which I am working. I know that a macro can do what I need, but I am only versed in Excel formulas and not that much programming. I need to be able to first sort the data by Column C ("Element Type"), then by Columns E, F, G ("Year", "Month", "Day"). Then, I need to be able to copy all rows that have the same "Element Type" and "Year" to a new file, using the same header from the original spreadsheet on each new spreadsheet - doing this multiple times until the end of the file is reached.
Ideally, the new files would have a strict naming convention: XXXXXX-ZZZZ (YYYY).xls, where the X's are the value of the "COOP Station ID" in Column A, the Z's are the "Element Type" from Column C, and the Y's are the "Year" from Column E. If this theoretical macro were run with the Sample Data file I provided, it should result in the creation of five new workbooks. Is there a way to write a macro to do this, or at least something similar
View 3 Replies
View Related
Mar 16, 2007
found the following code which works for 1 workbook at a time. I am trying to save 7 workbooks at 1 time. Is it possible?
Sub SaveAsCell()
Dim strName As String
On Error Goto InvalidName
strName = Sheet1. Range("V77")
ActiveWorkbook.SaveAs strName
It will work on the first sheet but none after that. I need to have each workbook saved with the value in cell V77. Also if that is possible, is it possible to change where the file is saved as well?
Right now I have a master workbook that will open the 7 other workbooks, paste data onto several pages in each workbook. I would like the macro to save the workbooks. The workbooks are named: 02 Tuesday, 03 Wednesday, 04 Thursday, 05 Friday, 06 Saturday, 07 Sunday, 08 Monday.
View 9 Replies
View Related
Dec 18, 2012
I have a template file for ordering trafolyte and steel plates. I have added macros to this template file. The existing macros do the following (shortly described):
Macro 1: clears order
Macro 2: update order date + send a read only file to the supplier of plates + save a read only copy of the file into one of three folders acc to info in one of the cells.
It's the Macro 2 I want to edit.
I want to add a "function" which copy a selection of data.column A to N from row 12 to 548 but only the rows where there is a value in column A.
Row 1 to 11 includes standard order info and Macro buttons.
Row 11 includes the heading for order data.
For everytime someone click on the Macro 2 button in the template file, I want the selection to be paste into the first "available" row in a "Total list" file.
The "Total list" file may have to be open (or a function to open, paste selection and then close the "Total list" file may be added)
File and Folder info:
To simplify suggestions, the following file and path info can be used (I can change to the correct later):
Template file name: template_order.xlsm
Template file location: \servershared emplate
Total list file name: total_list.xlsx
Total list file location: \servershared otal
Selection info:
The template file exists of a "general order info area" A1:N10
The column heading for order data is located at A11:N11
The selection to be copied is A12:N550 - But only rows where column A includes data (not empty).
(If the spesific order consists of 14 plates than there will be item no 1-14 in column A and I then I want to copy A12:N25 (row 25 will be item 14).
When I try to use record macro it looks like it only records what's happening in the template file - It doesn't record the pasting in the total list.
View 1 Replies
View Related
Feb 23, 2014
I have a requirement to download a lot of historical data files from the archieve of an website. The url goes something like this: [URL]....
This downloads file for 21022014 ie. 21-Feb-2014. I need to be able to have a facility to have a selection criteria on my user form (in excel) where I specify a date range and the macro automatically downloads all the valid excel files available within that date range (files for Saturday, Sunday and some holiday dates will not be available in the website archive database) one after the other (like at single clcik of button) into a specific location (predefined viz.. c:/Users/EOD files/) on my laptop. Best would be if I am able to select the save location run time by using a 'Browse' like feature where I go and choose my local laptop folder...same type when we try uploading a file from our laptop to the web..
View 1 Replies
View Related
Jul 7, 2008
I am setting up a "save as" macro that saves a file by replacing another file in a folder. Even though the macro has been recorded by approving the replacement (the prompt appears "the file --- already exists. Do you want to replace the existing file?"), when I run the macro, I am again prompted about replacing the file. Is it possible to avoid the prompt so the file is automatically saved by replacing the named file?
View 9 Replies
View Related
Feb 8, 2013
I do routine tasks every day which involves opening 3 different files and pasting data into my main file. The data is always in the same format, and the 3 files are saved in the same location (3 different folders through). The 3 files are saved each day and the naming convention is constant, with only the date changing. For example, the files are always saved in C:My Documents and the files are called test_05.02.2013.xls. Tomorrow the file will be called test_06.02.2013.xls and so on.
So each day I will be rolling a file forward and I want to bring in the info from each of these files based on the new day.
How to do 1, and I will do the others (because the concept will be the same).
View 1 Replies
View Related
