Processing Information
Oct 31, 2007
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.
View 9 Replies
ADVERTISEMENT
Jan 17, 2007
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.
View 9 Replies
View Related
Dec 12, 2009
I'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.
View 11 Replies
View Related
Jun 8, 2007
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.
View 10 Replies
View Related
Dec 7, 2007
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 Related
Jul 25, 2008
I 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.
View 11 Replies
View Related
Feb 10, 2010
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.
View 3 Replies
View Related
Aug 27, 2009
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
View 9 Replies
View Related
May 12, 2006
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..........................
View 2 Replies
View Related
Oct 10, 2006
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)
View 4 Replies
View Related
May 11, 2007
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 Related
Nov 20, 2009
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.
View 9 Replies
View Related
Jan 24, 2007
I 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.
View 5 Replies
View Related
Mar 1, 2007
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.
View 6 Replies
View Related
Mar 8, 2007
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
View 9 Replies
View Related
Oct 30, 2008
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
View 14 Replies
View Related
Dec 30, 2013
I 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
View 7 Replies
View Related
Jun 17, 2009
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 Related
May 8, 2007
I have an Access application with a form containing an Excel chart based on a pivot table based on an Access query based on an Access table which is refreshed every 5 minutes using a Timer event. When the table is refreshed the Excel chart is refreshed. The chart monitors some processes and is active throughout the day. As the day progresses more and more data points are available for plotting on the x-axis. The x-axis labels get very crowded as the day progresses and I need to programmatically change the x-axis scale at various times of the day. To that end I am trying to find an event that is activated at some point during the automatic refresh of the chart/pivot table.
I have tried the Workbook_Change event, the Worksheet_Change event, and the Chart_SeriesChange event. None of these are executed during the refresh process or I have done something wrong in trying to use them. At the moment they only have a MSGBOX message in them so that I can determine if they are even being executed.
View 9 Replies
View Related
Apr 10, 2007
I have the need within a spreadsheet to automatically compare the value of 2 cells and if they match exactly to move the cursor to a group of cells. If they do not match then move to another group of cells. The only way that I can see to do this is via Macros. However I still need to do the automatic compare first.
View 9 Replies
View Related
Feb 11, 2010
I have created a userform that allows input of information and then deposits the information on a specific sheet. I am looking for a way to have that information not only deposited on the specific sheet it is already set to but also to another sheet based on a selection made from a combo box.
here is my current
View 5 Replies
View Related
Apr 6, 2013
Basically I am trying to create a worksheet in which everytime I input information into Sheet 1, it is copied into Sheet 2. I want to have each entry in succession on Sheet 2 such that my first entry would be on Row 2, second on Row 3, third on Row 4, etc. However, everytime I put something new in Sheet 1, it just overrides the information in Sheet 2.
Basically I type in ticket sales in sheet 1, it calculates the prices and keeps a transaction log in sheet 2. But everytime I do a new ticket sale, it just overwrites the previous transaction witht he new transaction information. I've pasted my VBA below:
Sheet 1 VBA:
Code:
Option Explicit
'Form level variables - used in more than one event
Dim intAdult As Integer
Dim intStudentSenior As Integer
Dim intBalcony As Integer
Dim intChild As Integer
Dim sngAmountDue As Single
[Code] .......
Sheet 2 code:
Private Sub cmdSummary_Click()
'Declare Variables
Dim intCount As Integer
Dim i As Integer
Dim intAdult As Integer
Dim intStudentSenior As Integer
[Code] ......
View 3 Replies
View Related
Oct 24, 2013
The way I have this set up right now is that data is being submitted by an Infopath Web Form and emailed to me, it produced an XML file with values for all the possible entries for each person but only certain departments are filling in certain blocks.
That XML file is uploaded into a worksheet and then I'm trying to process data off that table to get the information I'm needing. I'm trying to divide data if a certain piece of the formula matches (IF CCU & August then Divide F column on same line by G column on same line and display the result)
I'm also trying to ignore empty blocks which is where my problem is at. So far I've come up with
[Code].....
but that is only working if the data happens to match on the same line in the data table.
Added the excel workbook: patientdashboardexperience.xlsx
View 1 Replies
View Related
Aug 28, 2009
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 Related
Jun 22, 2008
I'm developing a loan processing system for members of a club. When an applicant asks for a loan, the club will calculate 10 % of that interest and the applicant will have to pay it back in 5 successive fortnightly instalments. If he asks for a loan in the first fortnight (1), for example, he will have to start paying instalments in fortnights 2,3,4,5,6 to pay it all back.
The system currently has 4 worksheets. The first sheet is a the loan application form. The cells outlined in thicker border, are the cells in which details must be input. Once it is input, the data will be automatically placed in the Processing worksheet using IF and VLookup functions (See spreadsheet attached), which is used as a basis for the loan schedule Worksheet. What I need is a macro that will copy the range filled in the Processing worksheet, and copy it to the exact same location in the Loan schedule worksheet (The cells with the same fortnight columns and the same member name. This is how the loans are to be filed.
View 2 Replies
View Related
Jan 27, 2014
Imagine I have 2 columns of information that look like this:
Column A
Column B
AS
Dog
AS
Cat
AS
Hamster
FT
Fish
These are my key columns. The letters are initials of people and the animals are the pets they're responsible for.
Now, I have 3 more columns that look like this:
Column D
Column E
Column F
These columns can go on for hundreds of rows.
What I want to do is pull out the information from columns D, E and F where the initials and pet match those in the key list, then paste that elsewhere (say to columns J, K and L).
So, for instance, the first entry would be copied across because, according to the key list, AS is responsible for a Dog, but the bottom entry for AS wouldn't because he was looking after a fish, and that pet isn't listed as one of his animals in the key list (Fish is listed alongside FT). Likewise, the entry for AH wouldn't come across because AH doesn't appear on the key list at all.
View 1 Replies
View Related
Dec 26, 2006
I used Excel to take inventory of the items in my store.
I have two columns that I would like to export into my point of sale program.
They are, "product code" and "inventory count". When I try to copy them into my pos system it won't work because I would have needed to hit the enter or tab key on the keyboard inbetween the two fields. So if I were going to enter it all by hand I would just type the product code, hit enter or tab and then type the quantity.
Is there anyway to make Excel export an Enter key press or tab key press inbetween the two columns? If I can't figure it out I will have to do it all by hand. (over 2000 items)
View 9 Replies
View Related
Jun 26, 2009
i need to counting some info from my excel, but i don't know how to set the formula for it. i have upload the example for your reference.
View 2 Replies
View Related
Aug 8, 2009
i 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
View 2 Replies
View Related
Dec 1, 2009
i have workbook in which I have two sheets, one of them cotains colums with information and in the other sheet I have to create a Search box to retrieve all from any Contact number, of course it has to be based on contact numbers culumn.
example:
CONTACT_NUMBEROPPORTUNITY_TYPEQUARTERCON_TYPECONTRACT_TYPE_DESCRIPTION_LINECURR_CODE
379159OUTSOURCE201002C4PSMARTnet Onsite Premium 2x7x4USD
389859OUTSOURCE201002SNTSMARTnet 85xNBDUSD
379159OUTSOURCE201002C4PSMARTnet Osite Premium 4x7x4USD
388859OUTSOURCE201002SNTSMARTnet 85xNBDUSD
388459OUTSOURCE201002SNTSMARTnet 85xNBDUSD
388459OUTSOURCE201002SNTSMARTnet 85xNBDUSD
and in the other sheet I have to create a search box template that based on the contact number it will retrieve all other information.
View 8 Replies
View Related
