about making my code a bit more useful.
I started with a basic code as below which basically looks at upto 1000 part ids in column A and goes to my database to find corresponding Product Codes from a table in the database call PART
I am wanting to calculate the the processing time for an order that takes place within normal business hours and workweek. A normal day is from 8:00 to 5:00 If a task is started at 2:00 PM Monday and finished at 10:00 AM Tuesday then the result should be 5 hours as I do not want to include any time outside of normal hours. I can figure out how to subtract dates and times but not how to bridge a day(s). My data is somewhat flexible as I have not started the project yet. I can use separate cells for the times and dates or have two cells that use both incorporate the date and time (8/27/09 2:00 PM) for start and end time or any other idea.
View 6 Replies View RelatedI have created 2 seperate VBA's that I would like to combine and improve upon. One removes Duffs, and the other eliminates blank rows. My goal is to do a copy and paste/special, then remove the duffs, and then delete all rows that do not have data in column H.
I have tried several VBA's listed on this sight, but cannot seem to get a VBA that does everything I need it to do, and do it quickly. I have attached an example of my spreadsheet.
I want to open each file in a folder, one at a time, and save each file with a new name (based on cells in the worksheet) to a different folder. This is what I have so far:
Sub aaa()
Dim i As Long, files As String, data As String
Dim fName As String
ChDir _
"C:Files1"
Cells.Select
Range("A1").Activate
Selection.ClearContents
Application. ScreenUpdating = False
i = 0
files = Dir("*.txt")
Do While files <> ""
Open files For Input As 1
This code doesn't open any files unless there is a value already in the cells that fName uses.
I created this macro (below) using the record function but when I kick it off it takes 40 seconds or more to run.
View 4 Replies View RelatedI need to count the number of records having hospital admissions within one year prior to the index admission. The list of all admissions is in one worksheet, the index admission in another worksheet. They can be matched on ID number.
View 3 Replies View Relatedi have to removed the duplciate records with older dates. The date column is made up of date and time.
IDExpiry Date
1202 Mar 2013 12:57:04
1220 Apr 2013 10:49:24
5020 Apr 2013 10:57:06
5021 Oct 2013 07:20:54
5024 Oct 2013 08:41:56
I have a range of columns i.e. 23 columns (i.e. B through X). Someone can write records in these columns (starting from B21).
Duplicates are considered the rows with similar data in columns 3 and 11. I know about the removeduplicate method and works really well but i want the duplicates not to be removed. Instead another column shall be checked for date of entry (user will entry date in format dd/mm/yyyy). The newest entry will change the value of the cell in column 4 (islatest column)to TRUE while all other records will be FALSE. This will work with the filtering of data on a pivot table on another worksheet.
Book1 and Book2 are workbooks that I have modified in order to protect private information.
Book1 will have 11,000 records (my example Book1 has only 100). I need to rearrange Book1 such that it looks like Book2. Book2 has 20 complete records from Book1 combined into one single row, and my example Book2 has populated 3 rows only (3 rows x 20 records, making 60 records now appear on 3 rows only).
Macro for getting Book1 to Book2? 11,000 records in Book1 will take a lot of hours to transform into Book2 unless a macro can do the job for me.
Book1.xls
Book2.xls
I have a column of "timestamp" data (in mins) which i want to filter by a given time interval, say 10 mins. Then i want to count the number of records for each time interval and output the data to a sheet. how can i achieve this? through vba?
I attached a pic illustrating what i want to accomplish.
QQ截图20140429104406.png
is there a vba code which I could use such that it will show me how much processing has be completed.
So basically the user first enters all the information required for the calculation(which is basically a bunch of records on which the processing is carried out), then they press a button which will start the calculation however currently the user has to keep on pressing the refresh button (ctrl+alt+F9) and keep on refressing until the results have been calculated then they are available to view.
I was thinking if it is possible that after the user click on the calculate button a form appears which will start the calculation and within the code it will automatically refresh the worksheet ('s) and then when a field within the worksheet (Status) changes to completed the form will then disappear.
I am trying to do a conditional format for a cell and I think that it will contain some VB.
If a cell(C3) is a certain background color (gold), then I want another cell (E3) to subtract the number within the cell(C3). If the cell(C3) is not gold, then it is not to be subtracted.
A1 would be the cell that users would enter quantities of 500, 1000, 1500, 2000 etc and based upon what they entered-cell a2 would give a corresponding entry of 10,20, 30 etc.
View 4 Replies View RelatedI'm currently working on a project with data that includes columns for both latitude and longitude which I will need to convert from its current form into decimal degrees. The problem is that the coordinates are in the format DDDMMSS followed by a single letter indicating the direction, which means I have to first extract proper degree minute second format before I can do anything. Further complicating the problem is that each of the entries does not necessarily have all of the components I just described (eg some of the latitudes only have degrees and minutes but not seconds) and also if a longitude is less than 100 degrees, the third digit is not used in the data. There are over 2000 records so doing the conversion by hand just won't work and I've only got a few days to present this part of my project anyway.
I'm looking for a formula or series of formulas that will reliably convert something like 1083000W into -108.5000 when the last two zeros may or may not be there and the first digit may also be missing.
I get equation in the form of a string from the output of a software that I want to modify by Excel. I am no expert in Excel and I am wondering if some expert in Excel at this forum can do this for me.
see the attached Excel file. The string in cell A1 is an example string that I will be explaining how I want this string to be modified. Here are the attributes of the string.
a. The string will have ASCII characters in it.
b. The string is a mathematical equation. I will be pasting it into Mathcad once the Excel program modifies it.
c. The string has parameters and mathematical operators in it (+,-,*,/,^). Each parameter is seperated from another parameter by at least one space. There is no space in a parameter. So, each parameter starts with a space and ends with a space. The parameters of the example string in cell A1 are listed from cells A5 to A14 in the attached file.
d. If a parameter has paranthesis in it (), then the program will disregard any character in the parameter outside the paranthesis and take the characters inside the paranthesis. For example, it will modify the parameter in cell A5 from "F(u)_X1" to "u". If the parameter has no paranthesis in it, like the one in cell A14, then it will take it as is.
e. The format of the string is such that the multiplication sign may be omitted between parameters. If there is no mathematical operator between two adjacent parameters, it should be assumed that the math operator between them is multiplication. So, the Excel program should insert a multiplication sign between them.
f. Then the program will reassemple the string in the form of an equation by appropriately keeping the mathematical operators in the original expression.
The cells B5 to B14 show the modified parameters of the original parameters in the string in cell A1. The Excel program should put together the parameters in B5 to B14 as shown in cell A18.
I have a cell in my spreadsheet that is used to designate if the row needs to be hidden or not. If the user enter's "Y", the row is hidden. I need to check some of the previous cells for entered data before the row is hidden. If data is not entered in these cells I want the empty cell(s) to be filled with a color. The user will then have to enter a value into all the correct cells before the function to hide the row will work. What is the best way to do this? I am using the following function to check for the "Y" and hide the row.
View 11 Replies View RelatedI have a large number of sheets with data in the following general format:
x ..... y ............ z etc
A Unique value Unique value
A Unique value Unique value
B Unique value Unique value
B Unique value Unique value
C Unique value Unique value
C Unique value Unique value
D Unique value Unique value
D Unique value Unique value
E Unique value Unique value
E Unique value Unique value
In seperate sheets, I want the following:
x y1 y2
A
B
C
D
E
x z1 z2
A
B
C
D
E
And so on for a number of columns - I always want column "x" with another column, however I want the two values for the same value of x to be next to each other, not above one another.
Attached is a sample Excel file. In one worksheet, the RawData sheet, there is a column of text data. Each cell in that column has one or more values in the form: 2.x, or 2.x, 2.y, 2.z. The x, y, and z values are "Rqmt Numbers".
In the second worksheet, Counts, I am trying to count the frequency of each Rqmt Number. I've been trying to figure out the VBA code to do this for longer than I'd like to admit.
Ive found a few ways to speed up the macro in my workbook thats taking about 40 minutes to process usually.
One way - Press Escape and it goes about twice as fast! - Wy does it do this?
Another way - Inserting these lines into the code -
Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic
I am having trouble coming up with a way to retrieve certain pieces of data out of a text file. It is a file full of commands for some custom software we use. Here is a snippet of the file, the red parts are what I need to capture.
TOTAL RESET
Set 1=-1,8=-1,14=1,51=5,53=2,64=1,67=0,68=1,72=-1;
TABULATE
TA-TOTWWRTHP
TA-TOTCOLES
TA-BI-LO
TA-TOTIND3
TA-TOTIND2 (NORANK)
BY
L-MAT
With ZVARIABLE
FUTS-TOTWWTP
FUTS-TOTCOLS..........................
I have to apply formulas to many different excel files. They are all seperate, and there are litterally thousands of them. They are all seperated, cause each of them represent and individual user.
I am unsure of how to work on so many files. I cannot select all of them to open at once either, cause they are in different folders.
I need some sort of batch running program, that could go thru each and every one of them, and apply some formulas to them (probably using VBA scripts)
I often use multiple workbooks containing large data, (more than 10MB) with formulae built in. When I update one workbook, i cant use the other because Excel is still processing the first. Is there a work around or a MS patch to rectify this, or this is just a limitation of Excel program?
View 9 Replies View Relatedhow can i do the following equation?
40$ if the sum is less than 1000$
75$ if the sum is less than 1001$ tO 3000$
100$ if the sum is greater than 3001$
C16 X C19 if sum is greater than 12000$
all should go in one cell
I've been working on a variety of ways to display a 'loading' animation into Excel while other processes are running and although there seems to be a lot of strategies out there, none seem to jive with the form I've created. I've been reading a lot about "DoEvents", because that seems to be the common theme in trying to display a UserForm while other processes are running. However, I just don't have enough of a grasp of how it works to make it function properly with my form.
Basically, the form I have features several combo boxes, several command buttons, a few text fields and a bunch of labels. On initializing the form, captions for the labels and items in the combo boxes are added based on the ActiveCell and its surrounding values. Additionally, it includes an 'UPDATE' button that changes the captions and values on the form itself without hiding it, and once again makes reference to the active cell. This 'UPDATE' function can take quite some time and this is where I want to have a loading animation running.
Like I said, I've tried to achieve this in a few ways and the most hopeful avenue seems to be creating a separate UserForm with a WebBrowser element that references the animated GIF in question. However, when one UserForm is showing, the code from another will not run until it is closed. Thus, if I call the animation from the UPDATE button, the animation form will appear, but the update itself will not occur until the animation has been manually closed. Which defeats the purpose of a loading animation.
I understand that "DoEvents" is supposed to surrender some of the allotted processor power from the application so other code run, but the documentation surrounding it is rather convoluted to me. I can get it to work for simple operations, but I have not been able to simulate a situation where the command button on one form will show the animation form, run some code and then hide the animation with one click.
I have a VB codes to process the data from a file that located in the folder from directory. This VB will copy the data from the file and paste it into new workbook by clicking the command button. I want to modify this code in order to make VB can process the data from multiple file from multiple folders. Previously, I just write the code for one folder only. So now, I want to modifiy the codes in order to expand more folders. All the results will be pasted into the same workbook.
Private Sub cmdStart_Click()
Application. ScreenUpdating = False
Set outbk = Workbooks.Add
Workbooks.Open "C:" & TextBox1.Value & "" & TextBox2.Value
Sheets("bin_trip").Select
With ActiveSheet
How to stop my 100% from changing to 1000% after it pastes?
Here's the pc of code that is performing the copy/paste from one sheet to another sheet within the same workbook..
Code:
With Sheets("BOM Worksheet").Range("C" & nr)
.Value = c.Offset(, -16).Value
.Font.FontStyle = "Bold"
.Font.Color = RGB(186, 85, 211)
End With
Based on the 1st example below, I have a file with 1000's of columns where every 3 columns has the same type of info....
View 9 Replies View RelatedI have a very long (> 2 hr) macro that sifts through thousands of files and creates a table of contents (more or less). Anyway, the macro will operate just fine when I am also working at the same PC but if I walk away for an hour, Excel will sometimes generate an error (application defined or object defined error). I believe that it has something to do with the PC going idle although it really isn't going idle because I turn off sleep and hibernate while running the macro. Also, there are only two locations where the error seems to occur. See below:
Code:
Dim oApp As ObjectSet oApp = CreateObject("Shell.Application")
Dim files As New Collection
Dim fZip As Variant
For Each fZip In oApp.Namespace(zipFile).items
I use a worksheet full of formulas to know what divisors a number have, but i need to see what numbers have no decimals in about 500-1000 cells. Is there a formula whitch computer can use to see if it shows a number or not (if a number have or dont have decimals)?
View 2 Replies View RelatedI have about 300 cells filled with different formula's like the ones written below.
=SUMPRODUCT(--('Book 1'!$B$1:$B$1000="H"),--('Book 1'!$C$1:$C$1000=D21),('Book 1'!$W$1:$W$1000))
=SUMPRODUCT(--('Book 1'!$B$1:$B$1000="H"),--('Book 1'!$D$1:$D$1000=I21),('Book 1'!$S$1:$S$1000))
Spreadsheet works great, but I've realized that I should have expanded beyond the 1st 1000 cells (example: $1000). One book 1, my data entry will extend beyond row 1000.
Is there a way I can replace all $1000's on the spreadsheet with say $3000 without having to correct each of the 300 cells separately?