Has anyone ever used or heard of any products that will enable the automated testing of your Excel application using pre-written test scripts?
I have an Excel application that requires the manual data entry of over 250 test cases each time I make a change. It can takes days to run through all the cases.
I am trying to determine Long Term Gain (LTG,) Long Term Loss (LTL,) Short Term Gain (STG,) Short term Loss (STL,) or No Loss nor Gain (NGL)testing two cells (A1 and B1)and setting a third cell (C1) to the text LTG, LTL, STG, STL, or NGL depending on the results of testing cells A1 and B1.
A1 represent a number of years and B1 represent gains or losses (negative)in dolars.
The way I see the logic is as follows:
If cell A1 or cell B1 are either one of them equal to 0, then it is neither a Gain nor a Loss (NGL.)
If cell A1 is greater than or equal to 1, then it is Long Term; else, if A1 is greater than 0 and less than 1, then it is Short Term.
On the other hand, if cell B1 is greater than 0, then it is a Gain; if B1 is less than 0 (a negative number,) then, it is a Loss.
I need to find (if it is posible in Excel) one formula to test the two cells for posible outcomes:
If A1 = 0 then C1 = NGL
If B1 = 0 then C1 = NGL
If A1 >= 1 and B1 > 0 the C1 = LTG.
If A1 >= 1 and B1 < 0 the C1 = LTL.
If A1 < 1 and B1 > 0 then C1 = STG
If A1 < 1 and B1 < 0 then C1 = STL
I have a large excel sheet that will take a lot of time grouping it manually, so I was wondering if there was some way somebody could help me out with creating a code in VB to automate this grouping for me. All I would need is something to read through the rows of one column and when it finds a blank row, keep scanning until the next blank row and then group the data between the two blank rows. I have attached a small portion of the excel sheet I am working with if you need to get a visual of what I am needing.
View 9 Replies View RelatedI have a monthly report that I get with lab tests and amounts ordered. (I will copy a section for you to see). The Director wants it to be compiled into an easy to read, 1 test with totals. The way i get the data is broken down by site (we have 5). So, i may have between 1-5 rows of the same test with a total for each. I want to set up a Macro/Script that would go through the report and automatically add the same test codes together. My long term goal is to have it pull the Test Number, Test Name and Total Count into a separate worksheet, with the Total Count being the count of all the same tests.
see attached data: ...
I have a spreadsheet that deals with expiration dates. The whole sheet is conditional formatted so that when the current date is within 3days of the date showing in A2 then that cell changes colors. Ok is it possible that a script can be written so that when the cell changes colors... (Someone is going to think I am silly asking this but) excel sends an automated Email?
View 2 Replies View RelatedI m trying to achieve is generating ref numbers automatically.
I have attached an example.
In Cell A if the user choose yes it gives a ref number. The trouble im having is that say in cell A5 a Yes is choosen at a later date, it will throw the numbers out of order and the ref numbers get mixed up, there any way of stopping this from happening or any function that prevents this from happening
I am trying to write a formula which will consolidate notes for shipping orders
Column A Column B
Ship A 12/10 Talked to customer
Ship B 12/11 phoned cust
Ship C
Ship A 12/11 Called again
Ship D
Ship B
Ship A 12/12 Shipped
I want the notes in column B to be consolidated together so I have the history for each customer in one cell ie
Ship A 12/10 Talked to customer, 12/11 Called again, 12/12shipped
Ship B
The spreadsheet is really large so I want the formula to look up the customer name and consolidate notes automatically
I have been trying to create a macro that would create a schedule for me based on date and a product type. In columns K-N I have an IF statement that tells when a shipment would need to arrive in a row that corresponds with a date (column A is 1/1/2015 - 12/31/2015). On the next page I would like to generate a schedule that lists the dates that all of the products are needed in chronological order. I've tried to use custom functions like finding the nth_occurence but it just gets way too messy with so many shipments.
View 6 Replies View Relatedim having trouble formatting the left header, im not sure how to go about it, ive recorded macros to see how they do because i dont need talking marks im buggering it up somewhere
Sub OCHeader()
Dim Sheet As String
Sheet = ActiveSheet.Name
With ActiveSheet.PageSetup
.LeftHeader = _
"&""Trebuchet MS,Bold""&11"
.LeftHeader = Sheet
End With
End Sub
I keep a spreadsheet with the 50 or so wireless phones in our "lab". When someone checks them out, the rules are that they change the cell under Available to "NO" and add their user ID. When they return the device, they change the Available cell back to "YES". What I want is a log of who checked out the device, and when... which would be kept in "sheet 2".
I attached a small spreadsheet with the same columns and information you would see in the regular spread sheet. Sorry, I would rather have put it here, but I couldn't figure out how to do that.
The spreadsheet has code which updates the "Counter" when someone checks out a device. Many thanks to the "Rocket Scientist" who helped me with that.
I am attempting to automatically (once a week) pull down the data from this site (http://www.dot.ca.gov/hq/esc/oe/plan...bidsum_csv.php) which is posted in a csv and parse it. While this may seem to be a simple process, the people who posted the CSV did not take into account that there are dollar values in the data in the millions and billions. As a result, a normal comma parse does not work since it splits the values.
View 5 Replies View RelatedMaking an excel program that will automatically Email 5 people with either the whole document, a link to the doc, or just an email using the cells to inform them on what needs to be checked. And I don't even know where to start.
There will be a spreadsheet with everyone's name going down in column A, and then in the row next to the name it'll have a date of when they were put on it. It'll then have another date of the time they need to have done certain things, like see the doctor or get a class done, so when the current date reaches the date in the column the cell that is the same date will turn red, and the document will be mailed to 5 of the higher ups to inform them they need to check on this Marine to see if he has accomplished whatever task has expired.
Ive been trying to research a lot of this on my own, but ran into problem where the current outlook version does not suppose the command DATE within an excel, so I need to make a database using multiple excel sheets using Visual Basic(or so I'm told now).
I am having trouble trying to get an MS Access Query to run from MS Excel automatically.
I am trying to create an automated trigger in MS Excel that will automatically run my Access query by the times I specify in my statement.
For example: I need to run a query in Access at 9am, 12 pm and 10pm, that's it, but I believe I need to do it via Excel, I don't want the data returned to Excel, I just want Excel to execute the query at those specific times!
Is the anyway to have a macro wchich will automaticly open the IE browser when any of this word is clicked and search for this word in Google?
View 9 Replies View Relatedfind the attached file there is a form which i have created to update a data sheet, there are some fields in the Sheet which are there is the form, but what i want is in the form once i enter the data in first 2 to 4 fields 7th and 8 th, 9th ,10 th fields should updated based on some formulas given for caluclations:
See attached file: i want the following fields to get updated automatically
1) Tripcode should get updated as
CONCATENATE of Cab type and trip type ( See Sheet "ATT" for formula i have mentioned)
2) Trip cost is based on trip if trip code is TTA then some value or if tripcode is TTB some value like that...
formula i have used in the sheet is...:-
IF(J3="TTA","750")+IF(J3="TTB","700")+IF(J3="TTC","650")+IF(J3="TSA","750")+IF(J3="TSB","700")+IF(J3="TSC","650")+IF(J3="TQA","750")+IF(J3="TQB","700")+IF(J3="TQC","650")
3) Extra Kms Cost is field Extra Kms * 5(Some Value)
4) total cost is :
Trip cost + extra Kms cost
I use daily an Excel workbook that comprises 6 sheets. The first sheet has some very clever vba which at the click of a button converts the data in it to an xml file. I have looked through this vba and have on occasions, modified it slightly, sometimes successfully, sometimes not!! This is what I am wanting to do...... At the moment my data is entered into a different sheet in the workbook. This is because there is also additional information on this sheet that is not required on sheet 1. When a row in this sheet is completed the part of the row that is required is copy and pasted to the next available row on sheet 1. I then go back to where it was copied from and fill that row in a different colour to show me that it has been 'processed'
Although Sheet 1 is capable of 500 rows (set in the vba), I always produce my xml file from it when I get 20 rows of data. So, what I would like to do is be able to click a button, or a key press to:- copy part of a row of sheet 2. paste that into the next available row in sheet 1 ( upto a max of 20 rows)change the complete row of the copied data in sheet 2 to a different colour (preferably 'fill')
I have to maintain changing data in a date of the month format. However, a portion of the data is on an A/B week basis. The data I am maintaining is static data (MAWB in the example below) that is hand-entered each Friday for the following week.
Detail: ....
I've got a list of 50,000 book titles in one column, with thousands of sets of between 2 to 5 duplicate titles. The second column has one unique summary per set of duplicate titles. I want to duplicate this summary to the remaining duplicate titles of the set (their 2nd column cells are blank). The third column describes the general condition of each book, and the fourth the asking price. I have been manually copying and pasting, but this is very time consuming. Is there a simple routine that can do this?
View 8 Replies View RelatedI have attached a work book that has 3 sheets. Basicly I have 2 columns of records on sheet 3 that may or may not match Sheet1 has one set of those records. If on sheet3 the records in each row to not match I want a Comment added to that row on sheet one with the opposite value. I have make Comments to walk you thru want I would like on the workbook and have made and example comment of row3
View 4 Replies View RelatedI import into my website a standard .csv file that contains product details from my supplier. Columns contain info such as RRP, Wholesale Price etc and rows for each product. For some reason thats only known to my supplier the text descriptions of each of the products are held on a seperate .txt file, in a separate folder with the .txt filename recorded one of the columns cells, one for each product. There are 1000's of products, each with its own .txt file such as 38675.txt, 37828.txt. As you can imagine to copy and paste into each products cell or to manually import each .txt will take the rest of my life.
how I could automate the input of the diferent .txt contents into the required cells.
I have an excel sheet where automatically live data comes in.
When the cells reach a certain value the value in cell R1 becomes 1.
I want to make a macro in vba that sends me ONE email if the value becomes 1.
I tried :
Public Function MailS(mailtrigger As Double) As Double
If mailtrigger = 1 Then
Call makeandsendemail
Application.Run "makeandsendemail"
MailS = 1
Else
MailS = 1
End If
End Function
But this code send millions of emails.
I have taken on a project that is would seem is far out of my league right now, but need to deliver.
I have attached the example spreadsheet what I am trying to achieve, which is basically a random automated number allocator.
Look forward to receiving your replies,
Example.xlsx
it has been designed to send out automated emails using Excel and Lotus Notes, unfortunately the code was give to me to modify and the person who created it didn't comment the code at all so i'm finding it impossible to see what each part is doing: ....
View 14 Replies View RelatedI have 2 worksheets - Data & Reference.
in Reference worksheet, have 2 columns - Cards & State
* Cards column contains a listed points which will be added from time to time. The points can either be a word or part of a sentence etc.
* State column contain various general category
Table Example in Reference worksheet
Cards- State
MBB Visa- Classic
CitiBank Visa- Gold
Diners- Classic
Amex Corp- Platinum
MBB Mastercards- Gold
DirectAccess Persona- Gold
CitiBank Mastercard- Platinum
Amex Persona- Gold
DirectAccess Corp- Platinum
in Data worksheet, have 2 main columns - Remarks & Refs.
* Remarks column contains all sorts of data. The rows can be more or can be less day by day.
* Refs column will auto refer to specific reference.
Table Example in Data worksheet
Remarks- Refs..................................
My problem is that i have to create 100+ graphics (this my boss want),so after i create the first table i just copy past and change the data range and the name of the table. I want to automate this process with macros so i record a macros .
Explaining: "Eono1 is the name om my workbook.
So what i want ,the macros automaticali create new graph ,and change the data range ,(my data range is for january to december i row so i want to grab the next row from C3:N3 to become C4:N4)
Lets say i have 48 countrys (names R3C2),macros should automatically stop after c48:N48 for 2014,q48:ab48-2013 and AE48:AP48.(The last graphic is created).
Here is the macros and the exaple table with chart
Code: [URL] .......
Code:
Sub macro()
Range("B2:N3").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Range("Eeno1!$B$2:$N$3") -----this one unchanged
[Code] ......
i do not know or can seem to find out is how i can program the team list in the table to adjust each team position auto-maticly relevant to each teams points scored
IE 1st 2nd 3rd 4th etc in a vertical league list
I am writing a macro for people at work to use to automatically vlook-up data from another spreadsheet.
I wanted to base it along the use of a msgbox as they are not the most tech-savy people.
I want the msgbox dialogue to go something like this -
Which spreadsheet do you want to pull in data from?
The user browses to the file they want
Which columns do you want to transfer?
The user types in the columns
Which columns do you want them pasted to (in new sheet)?
The user types in the columns they want to paste to
and then macro does the vlook-up.
I have several Excel files with longitude values in column A, latitude in B, and data in columns C to DH. There is a lot of data in each file, so I was wondering if someone would be able to design a macro to achieve the following: I want any row containing zeros through column C to DA (not through to DH) to be deleted (deletion of the whole row, including DB to DH) - and the row beneath to be shifted up so no blank rows remain.
However, there are 16 instances where the row contains legitimate zeros and should be preserved in the dataset. These are at the following coordinates, where longitude (column A) is listed first, with latitude (B) second.
6.75, 27.75
8.25, 27.75
23.25, 27.75
24.75, 27.75
-2.25, 26.25
-0.75, 26.25
0.75, 26.25
2.25, 26.25
14.25, 26.25
-5.25, 24.75
-3.75, 24.75
-2.25, 24.75
15.75, 24.75
17.25, 24.75
9.75, 23.25
14.25, 21.75
So just to clarify, please could someone create a macro which deletes a row with zeros present from C to DA and shifts up the row beneath, except in the instances listed above.
I am using PrimoPDF, as suggested elsewhere in the forum, to attach a PDF to an E:Mail and send it automatically, it works a treat ... however, I do have to manually intervene to click on the OK button & then again to click on YES when the filename is duplicate. I would like to automate these actions and am looking for a way to do it. It looked like the SendKeys command was perfect, but I'm not 100% sure how to do it. The PrimoPDF window is open, named as "PrimoPDF", but is not my active window, so I have put PrimoPDF.SendKeys "{Enter}"
... into the program but I am getting the error message "Run Time Error '424': Object required". Is this something to do with my code ? I am guessing that the program isn't recognising that PrimoPDF is an open & inactive window, so am I missing a prefix here or is it something more serious, like having to add a new Component or Additional Controls ?
I'm trying to set up automated posting from journal entries to ledger accounts.
What it boils down to is having XL match account numbers, from a 'Post Reference' column in a journal, with the corresponding ledger accounts (carrying the same respective account numbers) on another worksheet.
Then the transaction from the journal needs to be added to the identified ledger accounts in a sort of 'below-the-last-entry' manner.