I currently have a range of documents which I know the name of but not the file extension. The Macro I have been working with is fantastic for checking the existance of the file but I need it to also return the file name if it finds a match. I am currently using Excel from the 2007 Small Business Suite on a Windows XP PC. The Code I am currently using works as follows:
View 4 RepliesPublic Sub ListMyFiles()
Dim fso As New FileSystemObject
Dim fso_Folder As Folder
Dim fso_File As File
Dim file_count As Long
Set fso_Folder = fso.GetFolder("S:SHARED All of TransportationTraffic AssessmentMultiway Warrants")
file_count = 1
For Each fso_File In fso_Folder.Files
file_count = file_count + 1
Cells(file_count, 1).Value = fso_File.Name
Next fso_File
Set fso = Nothing
End Sub
This works great, but is there a way for the macro to recognize the file names that have already been added to the spread sheet and only return new ones that have been added to the folder?
I have some code traversing a directory of folders and subfolders on a shared server and returning a list of those file paths. Around 6000 folders with about 2 subfolders on average. This returns a list of 12000 filepaths but takes around 13-15 minutes to do. Each path looks something like this:
C:Folder1
C:Folder1Folder1SubFolder1
C:Folder1Folder1SubFolder2
C:Folder2
C:Folder2Folder2SubFolder1
C:Folder2Folder2SubFolder2
VBA coding for automatically saving an excel file as another file using the current date as part of the file name together with "32ga" as a constant add-in. I also what this macro to run at a particular time of the day let say 00:20hrs. The excel file i want to save as is always open . It has data that changes every 24-hrs.
View 1 Replies View RelatedI 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.
I am trying to use the following code to compare the password that was entered, to the one that is stored in the worksheet to make the user aware that the password they have entered is incorrect
View 2 Replies View RelatedI have the following table:
A
B
C
D
E
F
1
Most Recent
1st
2nd
3rd
4th
5th
[Code] ......
I am wanting to populate the "Most Recent" column by selecting the last value in each row between B to F so that I get the following result:
A
B
C
D
E
F
1
Most Recent
1st
2nd
3rd
4th
5th
[Code] ........
I am also wanting the result under Column A to indicate by cell colour red if the value is less than the previous value and by cell colour green if the value is equal to or greater than the previous value.
For example; Cell A2 should be highlighted green because the last value which is D2 is greater than the previous value in C2. While Cell A4 should be highlighted red because the last value which is C4 is less than the previous value in B4.
I do have another question actually. In the Total Sales worksheet, I have a a cell in S2 that I wish to show the max value from the "total sales" colum and then show the first and last name related to that particular row where the max value was. so, I only have =MAX(O2:O112), but I want to show the Name associated with the max and not just the $ amount.
So in S2 I want ---> John Doe and not $512.50, but I can only think of =IF(MAX(O2:O112), $B2+$B3, 0) becase I want both the first and last name. I used this formula with only $B2, but it just returned the first name in the B2 field
i have this formula in cell e28 =IF(B28<>"",LOOKUP(B28,STOCK!A1:A500,STOCK!B1:B500),"") and when its empty it returns a blank cell
but in cell e29 i have exactly the same formula =IF(B29<>"",LOOKUP(B29,STOCK!A1:A500,STOCK!B1:B500),"") but i returns #n/a
why is this and how do i stop it i want the cell to be empty unless there is a code entered in b29 in which case it will look up the relevant info for me
I have a sheet with this:
cell a1 = numbers from 1 to 10 or nothing
cell b1 = TO or OFF depending on cell a1
cell c1 = numbers from 1 to 10 or nothing
Now I have a formula in cell b1 that detects if theres text in cell a1.
IF A1 = nothing, then b1 = off, else b1 = to.
What I want to achieve is to do 2 things on the IF.
If a1 = nothing, then c1 = nothing and b1 = off. else, b1 = to.
how can I do this?
I looked everywhere and no one speaks of double answers on IF.
i have a drop down list in cell b16 which then corresponds with what to bring up in the next drop down list in cell d29 this works fine.
View 2 Replies View RelatedI am trying to create a formula that will search a row left to right for the first instance of zero and return the corresponding column label. I had this working for a smaller table using a couple of hidden columns with nested ifs however the new data set is approximately 25 columns wide and it will get very messy using the same approach. See attached sample with desired results.
View 2 Replies View RelatedI have just decided to dable in the 'VLOOKUP' function. I have looked at a few examples on the net and I have tried to replicate them with no luck (I have modified titles and values). I have attached my example to this post.
View 2 Replies View RelatedIf I have a column full of data is there a way to copy every nth row to a new column? For example, I may have dates and corresponding days of the week. Can I copy the date of each tuesday (i.e. every 7th row) to a new column?
View 2 Replies View Relatedcode to scan a datasheet for a value and return "MATCH" if it is found and to return "ERROR" written in Red or Yellow if it is not found.
View 4 Replies View Relatedi was trying to see or figure out if there is a way to bring over information on a row from one sheet to another. I have a sheet with dates in column A and information going across to column h.
as an example i have row 1 that has date in A1, text B1, text C1, nothing in D1 but text in E1 all in Sheet 1.
I want to see if there is a formula that will say if it is todays date then grab that row and bring over the row with the cells that have data
I am building a form that uses a combo box to lookup up a row in a table and return row data. To make things more complicated I want to return more than one record. For example: the table has a list of jersey. I search for a style jersey. I want to search the list and return all the colours for the chosen style. Sometimes there is only one colour or there can be up to 10. Have a look a the attached example.
View 2 Replies View RelatedI am trying to use a sub to count files that some one has helpd me out with in the past. It was working but for some reason now it is not. I always have 0 returned as the number of files in the selected location (SourcePath), which is a filthy lie as there are files in there. The idea is that all .xls and all .xml files are deleted, and then if the folder is empty, that is also deleted, but as the Count_Files sub is always returning 0 the code is trying to delete the folder, which causes an error as it has files in it.
View 8 Replies View Related=IF(ISERROR(VLOOKUP(B14,'Metal Worksheet'!$A$8:$L$157,11,FALSE)),"",VLOOKUP(B14,'Metal Worksheet'!$A$8:$L$157,10,FALSE))*D14
As long as the lookup value is true the formula works but if the lookup value is false then I get #value! when it tries to multiply by D14. How do i get the formula to return a blank cell or 0 value if the lookup value is false?
I would like to get the last value in column A & B and place them into another sheets cell. The number of rows will always be changing too.. In the attached spreadsheet I want the totals, which is the very last value in the colums.
View 8 Replies View RelatedI am including the entire code. It does a regression analysis on data for a second degree polynomial. In doing so, it creates a worksheet called Regression. So I test first to see if this sheet already exists within the workbook and then ask the user to delete it if it does. I've stepped through the function and the function is always returning False whether Regression exists or not.
View 5 Replies View RelatedI want my worksheet to search a column for a specific date (3-Dec-2008, for example) and count cells containing only that specific date. If it finds the date, I want it to count them.
However, if that particular date doesn't appear in the column, it returns 0. I want the cell to return blank. Basically, instead of giving me 0, I want it to return empty. I've tried IF statements, COUNTIF statements, combinations of IF/COUNTIF statements.
=AVERAGEIFS(Activity!K2:K80,Activity!A2:A55,"Complete",Activity!C2:C62,"Oakpointe")
K2:K80 holds dollar amounts. I would like the average of those dollar amounts where Column A=Complete, and Column C=Oakpointe
For some reason it won't return the average. Could it be because Column A can C are dropdowns?
I'm hoping someone can help. I've written a =VLOOKUP formula that works fine. What I'd like to do is if the value can't be found within the lookup then the formula returns a 0 rather than the standard #N/A. Can these be joined up in 1 formula?
View 6 Replies View RelatedI am using the following formula, but for the cells that dont have data I get the #DIV/0! error.
=AVERAGEIF($B$6:$B$48,B59,$W$6:$W$48)
I need to search across 15 columns and return the lowest value in that row? How to do this?
View 8 Replies View RelatedI have this code
Code:
Selection.FormulaR1C1Local = "=INDIRECT(CONCATENATE(""["",R4C26,""]Sheet1!"",RC))"
Which creates this in the excel cell:
=INDIRECT(CONCATENATE("[",$Z$4,"]Sheet1!",D4))
But for some reason, it returns a 0. However, when I change the code in the excel cell to this
=INDIRECT(CONCATENATE("[",$Z$4,"]Sheet1!D4"))
It works. But I need to be able to change D4 in the macro, so doing it that way won't work for me.
The message box in the Standard module procedure returns 00:00:06.
Standard Module
Code:
Sub New33()
Dim clsCampaign As CCampaign
Set clsCampaign = New CCampaign
clsCampaign.MailCode = "AWSXBETC"
[Code]....
Code:
Option Explicit
Public MailCode As String
Public LandDate As Date
Public MailDate As Date
Public FirstPanel As Date
Public FinalPanel As Date
Below formula matches two criteria
=SUMPRODUCT(('[Value.xlsx]test!$B$5:$B$50=$B5)*('[Value.xlsx]test'!$C$5:$C$50=$C5)*('[Value,xlsx]test'!D$5:D$50)).
What it does is:
Matching B5 value in range B5:B10 of tab "test" in worksheet "Value"
Matching C5 value in range C5:C10 of tab "test" in worksheet "Value"
AND capturing the matching value in range D5:D50.
The problem is that i am getting 0 if the matching value is a blank or 0.
I want the formula to return nothing (i.e. blank) when the matching value is blank and 0 only when the matching value is actually 0.
trying to come up with a formula that will return the last non-zero value in a row. In the example below, I'd like the formula to return the value "456". If there is a way to do this without VBA or array formulas, that much the better!
View 2 Replies View Related