Locate Files Of Predefined Extension, In Predefined Location & List In Spreadsheet
Oct 22, 2008
I require a bit of code that locates where the excel file is stored - it then searches that directory folder for all file names. Any file names with an extension *.hm for example are listed in column A, while their relevant 'Dates of last Modification' are listed in Column B.
Every time the spreadsheet opens it should ask the user if he/she requires a re-search of this directory and update of any file names accordingly. NB: if a file name is deleted from the directory, the file name should remain in the spreadsheet.
View 3 Replies
ADVERTISEMENT
Oct 12, 2009
Struggling a bit on this one! I have tried the index function, but doesnt quite work how I need it. Consider the attachment as an example:
I have three cells, 2 of which are drop down lists, and the third is simply the result from the input variables. The yellow and green section contains the info for my drop down lists, and the orange contains the data range. Trouble is Index is looking at the contents of my cells, and not the corresponding cell address. So lets say I have cells F5 and A8, the answer I would be looking for is 23.7, but because the contents of my input cells are 185, and 5, its looking for column 185 and row 5, when I want it to look for the column/row that relates to these values, i.e. column 6, row 8.
View 5 Replies
View Related
Jun 25, 2014
I've seen a lot of traffic around running macro's on a clock at regular intervals. But what if I want to run a Macro (Macro1) at 8:55, 9:55, 11:55, 12:55, 13:55, 16:55 and another Macro (Macro2) at the top of each of those hours? The string of times is not consecutive, so I have built a table to define the release times. At each of these times I need to run a macros, and I'm not sure where to begin.
View 3 Replies
View Related
Jun 25, 2012
My wife is a teacher and she has asked me if there was a way of randomly selecting a child's name from a pre-defind list and for that child not to be selected again during that session.
Some criteria:
Up to 35 children in the class (selected from a pre-defind list)A child can only by picked on once during the lessonA method needs to be in place for seelcting the child's name (either a button that can be pressed, or another option).
View 2 Replies
View Related
Mar 13, 2007
I want to create a long list of 10,000 random numbers between -100 and +150, but do it so that the average of all the random numbers equals 20.
View 9 Replies
View Related
Jun 15, 2009
I sent this before but the formula I was given wasn't correct (as you will see from the file). In Column B of the second sheet (Data) of the attached file, I want to bring back the relevant name form the first sheet (List), which involves searching through the long descriptions from column A on the Data sheet for the name on the List sheet.
View 2 Replies
View Related
Dec 8, 2008
but, after subtracting those days when they fell between Holidays / vacation periods.
See question, in details, inside the attached workbook.
View 10 Replies
View Related
Feb 13, 2014
I am having a set of names in one column and would like to print various combination of names in to another column. In sample data set, there are 30 names of entries in column B and like to print 4 data set i.e. 1st three set of combination of column B print into the column C similarly for other set of six, twelve and all kind of combination. Please find the detail in attached excel sheet.
View 3 Replies
View Related
Dec 24, 2008
I have a button in my worksheet. When clicked on I want to change the text in this button
so far so good. I have however a problem how to return to the cell that was active, the moment I clicked the button. so far I have the following code
View 3 Replies
View Related
Apr 9, 2009
I currently have a 200 row table spanning 10 columns. There is one numeric column and the rest are free text. The table charts the different contents on one of the shared drives here at work. The drive is frequently in use with things being added and removed from it.
I have an excel table already which can sort the contents in order of size (numeric row) or name of the folder (the first text column). I have two buttons at the top.
The first inserts a row, which I did by recording a macro and then copying the code into the button.
The second I would like to do the following: When a user selects a cell in a row, the button, then clicked, deletes the entire row. Not just the data in it, but excises the row itself and stitches the surrounding materials together. The code to delete a row I could obtain easily, but I can't work out to say "find the selected cell, and take out the row it belongs to".
View 2 Replies
View Related
Mar 24, 2007
After all the work and calculations applied to data from various macros, I would like to have a final macro simply insert a title row, always the top row with various titles. Theres 11 columns, thus it would be great for the code to simply insert the row with predefined bolded titles, such as T1, T2, T3....T11. These 11 cells would than share then same bolded bottom line, making it visibly separated from the rest of the rows. I know this is easy, and Ive looked throughout the forum, but I can find things close, but not close enough.
View 2 Replies
View Related
Jun 23, 2009
I want B1 to copy from A1, but if A1 contains a certain text string, then remove that text string and keep everything else. For example, I want B1 to remove "ru" "la" "fm" and "mu" so if A1 is 5464ru-xl then B1 will return 5464-xl or if A1 is 36944la-s then B1 returns 36994-s
View 14 Replies
View Related
Dec 30, 2009
My problem is that I need to get button to move information from sheet1 A5:E5 & A8:E8 & A11:E11 to sheet2 B1:P1 if Sheet1 A1=1 to B2:P2 if A1=2 etc
I can only make it by if or case macros, but it becomes so long code
View 2 Replies
View Related
Feb 8, 2012
I'm looking for a cell formula. here is an example:
In cell A2, I want it to populate automatically with a predefined value if cell A1 is a certain colour, as below.
A1 A2
559.00
I have conditional formatting in cell A1.
View 6 Replies
View Related
Apr 16, 2008
I'm looking for: a cell (in column C) that sums Column C from C6 to the row above said cell. (A Total) So if new rows were added, these values would be included in the sum.
What I'm currently working with is a simple Sum formula, but this sum does not include rows added after the predefined sum range.
View 9 Replies
View Related
May 22, 2007
I have a workbook which has a variety of command buttons with assigned macros, one of which is a product detail button which redirects to a new worksheet.
Is it possible to be able to click on the button, display a new window (the new worksheet), but only display a section of it?
For example, I have 5 products. Each of the five products are detailed on the same worksheet and I have a button for each. I would like Button 1 to open up the details for product 1 only and button 2 to open up product 2 details etc.
I could go to individual sheets, but if I had 2000 products, the book would be a little large.
View 3 Replies
View Related
Feb 27, 2009
Here is a traditional Indian time calculation
(1 day) 24 Hr = 60 N
1 N = 60 V = 24 Minutes
How to formulate this in excel 2003.
If A1= HH (hour), B1=mm (minutes) - Inputs
A2 should show NN : B2 should show VV - Result
And Viceversa.
If A1= NN (hour), B1=VV (minutes) - Inputs
A2 should show hh : B2 should show mm - Result
hh - hours, mm - minutes, NN - Nazhigai, VV - Vinadi - all are numbers
View 13 Replies
View Related
Apr 27, 2009
Excel 2003: I need code that, when an "x" is entered in a cell in the "Activity" worksheet to assign a temporary unit #, it will look for the next available Temporary Unit # in the "Assign" worksheet. Then mark that unit # as "assigned" (by placing an "X" in the column next to it) and copy it to a cell in the "Activity" sheet.
I will be doing the same thing with assigning different types of PO numbers. I figure if I have the code for the Unit #, I can use the same logic for the other assignments, with some modifications, of course.
I've attached a sample workbook.
If I am not considering the most effective way to accomplish what I am trying to do here, I have no ego at all about someone suggesting a better solution.
View 7 Replies
View Related
Apr 5, 2009
I have a worksheet that logs equipment activities. Equipment may already have a standard unit # assigned to it or it may need a temporary unit # assigned for tracking. In a separate worksheet is a column of predefined temp unit #'s that can be assigned and next it is a column to place an "X" once a unit # has been assigned.
I need to write logic that, when an "X" is entered in Column F (Assign a Unit #) of the Activities worksheet, the Unit # (Column G) will automatically select the next available Temp Unit # in the Assign worksheet...and mark that Unit # as no longer available. Quite frankly, this may be more easily done with VBA, but I am not experienced in that at all. I have attached a sample worksheet showing the format of the unit # to be assigned, etc.
View 4 Replies
View Related
Apr 22, 2014
I had posted earlier to see if there was a way to protect certain cells from being updated when using a linked excel spreadsheet to access. Since there have been no replies I'm assuming not.
Different approach, can I create a Macro that would run after the update that would insert my required text into each cell from A2 through to R2.
The text would always be the same and always in exactly the same cells. This would need to be an insert into and not a replacement of the data already in those cells.
A1 B1 C1 D1
State
BUName
InsuranceCompanyName
Claim
ROnumber
Renter Last Name
[Code] .......
A2 B2 C2 D2
CategoryFilter CategoryFilter CategoryFilter NoFilter
The text under each of the A2, B2, C2 etc is what I would want the macro to do for me, essentially putting back what the update from the linked source removed.
View 3 Replies
View Related
Feb 14, 2014
So i want to be able to send a single Excel sheet to a recipient, not already pre-defined in the VBA. I am trying to get to where i can click a button/link and a window pops up, asking me to enter recipient's email address. When I enter the address and press- 'Send', the email is sent using Outlook.
View 4 Replies
View Related
Oct 30, 2012
I have a spreadsheet that allows users to paste set data from a PDF Image (using OCR) straight into Excel and then use the MID function to split the data accordingly.
Unfortunately, the OCR isn’t too intuitive and gets it wrong sometimes.
So to counter this, in another sheet (in the same workbook) I have a manual input section, and a simple macro button that pastes this data into the same fields where the OCR text would be, so that the main sheet works exactly the same way as before.
The problem is, and most likely due to the simplicity of the sheet, if a combination of OCR pasting and manual inputting is used, when I hit the paste button, it over rides the OCR data with blank cells
In the link below I have shown what is currently happening (1, 2, 3), and an example of what I would actually like it to do (4, 5, 6).
Example - Online Spreadsheets - EditGrid
So, in the 2nd scenario, I would like “5” to recognise that the respective cells in “4” already contain data and fill them ‘Grey’. This I have already achieved with basic conditional formatting.
However, I need to take it 1 step further and say that if the parent sheets cell (Auto OCR) contains data, as well as filling cells (in sheet Manual) lock these cells off and prevent the end user from adding data and/ or being copied over to the parent sheet.
Is this possible?
The result then being the parent sheet with both OCR text and copied text from the manual input sheet.
Both sheets are protected anyway and only allow for user input in certain areas, so is it even possible to apply further protection once the sheet is locked already?
View 1 Replies
View Related
Dec 13, 2013
I want to create an Excel sheet that calculates the charges of a freight based on predefined parameters.
I got 3 service types as shown below, two freight types and 12 different destinations both NT and SA have 6 different destinations:
I want Excel to do the following:
If Road and Carton and to NT-Country and weight is 7 then 7*1.000089 is the result
1 to 15
>15
Service
Type
NT
METRO
0.337284
0.337284
[Code] .......
View 7 Replies
View Related
May 18, 2009
I need to fixed width-text to column macro and found a reply in the forum.
However, when I apply the macro, the result of zeros in front of figures disappear since the format of value in splitted column doesn't predefined as text
e.g. sample text to split to column:
000122042009ABCDEFG00567
Required result:
0001|22042009|ABCDEFG|00567
when running below macro; result shows:
1|22042009|ABCDEFG|567
(Beginning zeros figures of the first and last column disappear)
Applied Macro:
View 3 Replies
View Related
May 21, 2008
Sorry that I'm very new in Excel VBA coder. And, for this topic, I don't even know how to start. I want to make a MS Excel database of a numerous files. This database must be consist of Filename, Location, and it's attribute (let's say updated date, size, hidden status)
View 2 Replies
View Related
Apr 10, 2013
I'm trying to get something like - If a cell contains certain text AND certain text then display set text OR If a cell contain certain text AND certain text then display set text.
For example:
Vauxhall Corsa 1.5Litre Petrol = vauxhall corsa petrol
Vauxhall Corsa 1.5Litre Deisel = vauxhall corsa Deisel
Vauxhall Nova 1 litre Petrol = vauxhall nova petrol
So it the formula is effectively searching for the car type and the fuel type and then returning the relevant set text. So far I'ver only been able to find IF functions criteria to identify one feature of the text.
I'm thinking it's got to be variation on =IF(ISNUMBER(FIND("Vauxhall Corsa",A1)),"Vauxhall Corsa Petrol") but there should be something else in there like =IF(ISNUMBER(FIND("Vauxhall Corsa"&"Petrol",A1)),"Vauxhall Corsa Petrol")
I know this is wrong but just trying illustrate my thinking. I need it to pick up vauxhall corsa AND petrol. I understand that I would have to stick a lot of IF functions together in a string, but that is fine as long as I can ask it to pick certain text AND certain text within a cell.
View 6 Replies
View Related
Apr 21, 2006
I have a few thousand files that all have the extension of .kdm.kdm. I need to change them all to .kdm.xml. All of the files are in a single directory on my hard drive. I know there's a way to do this in VBA that's simple, but I can't seem to locate it in any of the books I have....
View 2 Replies
View Related
Aug 21, 2012
I have a folder containing 1000's of files. All the files end in .txt (for example test.txt) but are actually excel files. When I go to open the files with excel I get the following warning:
The file you are trying to open is in a different format than specified by the file's extension. Verify that the file is not corrupt and from a trusted source before opening the file. Do you want to continue?
I click "yes" and it opens fine as an excel file.
I want to merge all these files one after another into one file.
View 1 Replies
View Related
Apr 16, 2014
I know how to delete duplicates using the function under the data tab, but how can i locate them. For instance, a spreadsheet with a list of PO numbers, how can i do a search to see if it is showing the same PO number multiple times?
View 9 Replies
View Related
Oct 4, 2006
Is there a way of finding all files in a directory which contain a macro? I have script which will loop through all files in a folder but I don't know, and can't find, the appropriate syntax for referencing macros/modules.
View 9 Replies
View Related
