I was wondering if there was anyway to automate this process. Say take all the files in this folder copy visible cells on a specific tab and paste them into a new worksheet/model we already have built. They shouldn't be consolidated rather pasted below the next.
i have a validation list in A5 pointing to F6:F, i need the refmask_no to change according to the validation list in A5:A.
So if i choose from List LEADCASH the refmask_no= '100007995' etc
How can i automate the changing of refmask_no?
Sheet1  ABCDEF4Validation ListCorr _acc_no  Mapping 5LEAD CASH ABNAMROrefmask_no = '100007997'  Corr_acc_noLead Set6    '100007995'LEAD CASH7    '100020741'LEAD CASH ABNAMRO8    '100020645'LEAD CASH AUDNZNR9Â
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.
I have a huge database of textual files all of which have the same format - space separated text files. I need to find the average of the last column of all these files.
Each file is named uniquely. Is there an stress-free way to set up an automated procedure to go through each of these files automatically and to record the average of the last column along with the name of the file. I attached a few text files - these are daily prices of a few securities. The last column is Volume. I need to find and record the average of this column for each of these files.
how to process this non-numerical. I tried to use formulas but it wasn't fruitful. refer the table and.
Name English G Maths G Science G History G Aggregate Result Achievement
[Code] ......
****The aggregate derived from the grade of the subjects , where each grade will be given a pointer and the average pointer for all four subjects will be considered as aggregate :
Pointers for grades A =1 B=2 C=3 D=4 E=5
This is how the aggregate for Alex should be counted: English A = 1 Maths C = 3 Science B = 2 History A = 1 *****aggregate = (1+3+2+1)/4 =1.75
The result column be showing Pass/Fail. If the student fail one of the four subjects he is considered as Fail. **Grade D and E =>Fail
The Achievement Column should be indicating the numbers of As, Bs and Cs for example: O6= A(2),B(1),C(1)
I have a database which contain many rows and columns. I tried using Pivot Tables to pull out the data from sheet 1 to look like the format in sheet 2, but without success.
I'm trying to figure a to enforce dual data validation on a single cell. That is, I need to restrict the user to entering only a decimal value, only if a particular other cell (say A2) is blank. To put it another way, if A2 is blank, the user can enter a decimal value, but if A2 is not blank, the user cannot enter anything. I can use Data Validation to enforce either the decimal restriction or the ISBLANK, but I'm not sure how to make them work together.
I have a two workbooks, one which is a daily schedule and the other is a yearly summary. The worksheet in the yearly summary uses a date in the first column and the daily sheets each have a date on them. I've isolated my data in the daily schedule and I've retrieved the date in its numerical equivilant. I have data that is 6 cells wide by "x" amount deep on any given day. Essentially, I schedule different things to be made and each row has that designation, process, dimensions, quantity, etc.
I want to test the contents of the first cell in the array, copy the pertinent data, switch to the proper worksheet, and paste it into the line where it goes. I believe I need to check a few cells to narrow down exactly which group of cells on which sheet they would be copied to.
I am not trained at all as far as programming goes, but I've been practicing for a few years now. What I am just going to start doing is testing the cells in the rows in the array of data and trying to divide it out that way. This just seems like the long way around.
From a large set of process data, I would like to copy those instances where there is indeed a measurement available to a different sheet automatically.
The resulting data shoot look like this (in a different sheet):
Time Temperature 02-Jan-2010 11:41:50 156.1 02-Jan-2010 11:44:50 157.3 02-Jan-2010 11:46:50 156.8 02-Jan-2010 11:47:50 156.4 02-Jan-2010 11:50:50 157.2
I have actually been learning quite a few tricks on my own since I started this data analysis project but I believe this requires some experience with macros, which I really don't have.
My problem is that data from previous applications are "bleeding" into new applications while running my macro.
I am utilizing the macro below to paste an application and then to convert the application to upper case in addition to some additional formating changes. After performing the macro I save the newly created document to a different folder. With new applications I repeat the above steps.
I tried to incorporate the clearing of the clipboard in my macro using: Edit=>office clipboard=>clear all. The keys strokes during the " record macro" process do not seem to record in the macro.
I am using Excel 2003, SP2
Sub Process_Application() ' ' Process_Application Macro ' Macro recorded 3/23/2009 '
I have two macros one of which imports the data and the other processes the data. These macros were created at different times and need to be joined into a single macro that will combine their operations – with slight modification to the importing macro. Let me describe what each of the macros does:
PROCESS macro: Cycles through the CONTROL CELL on the “1” tab using the VALUES TO TEST. For each value to test it copies the values form the output tabs and pastes them into the A+B tab. Then it removes the duplicate rows there.
Option Explicit '====================================== Sub PROCESS() 'This subroutine is called when clicking on the first button Dim i As Long, j As Long, k As Long 'Those are the variables that will browse through the whole sheets Dim l As Integer 'this will be used to browse through the values to test Dim TheSearch(1 To 2) As Object 'This is an array of objects that will be used for the search Dim TheRange As Range 'This will be used to flag the range of duplicates in the output
'Searching for the Text string 'VALUES To TEST' in the sheet "1" in order to know where the array of values to be tested start Set TheSearch(1) = Range(Cells(1, 1), Cells(ActiveSheet.UsedRange.Rows.Count + 10, ActiveSheet.UsedRange.Columns.Count)).Find(What:="VALUES TO TEST", LookIn:=xlValues, Lookat:=xlWhole)
I have 2 columns First Name & Surname. What I want to do is create a data validation list on the surname which results in the 2nd data validation list only showing the first names which link to one of the surnames.
i.e. If I selected Smith in the 1st validation list then I would only like to see 'Paul' as an option in the 2nd list
I have attached a sheet that I am working on. I want cell G1 to be less than or equal to 165. That cell contains a formula. If the formula takes the number to over 165 the validation is allowing it.
I would like to know if it's possible to populate a data validation list based on what is selected from 4 validation lists?
for example: On sheet1: If 'Group1' is selected from data validation list1 then data validation list5 will show a list of all items from Group1. If 'Group2' is selected from data validation list2, then data validation list5 will display all the items in 'Group2'...
I found a template at microsoftofficeonline.com it's just a bracket. I feel it has some underlying functionality, as they have a template in visio as well, but I can't figure it out.
So, what I'd like to do is have a workbook with say 100 worksheets. Each worksheet will have a person's bracket. Each person will write in all their picks. Their will be a master bracket where the actual winners will go, as the results are posted. When the master bracket is updated, Excel will flip through each worksheet, checking to see if the win in the master bracket matches the individual bracket. Points will be awarded or not.
Can that be done? I've also done a google search and can't get anymore help. I know that the web has multitudes of free versions, which I'd use, the only problem is that people would hesitate to register. doing it in excel, on the office system, there's no need to register, your cpu login takes care of authentication.
I'm trying to make alot of graphs(400) and I'm not really sure where to start but here with what I've got so far.
Sub macro2() Dim x As Long, t As Long, y As Long, z As Long y = 3 z = 5 x = 4 While x < 1000 With Worksheets("cabernet (2)") t = Cells(x, 1).Value Charts.Add ActiveChart.ChartType = xlLineMarkers ActiveChart.SetSourceData Source:=Sheets("cabernet (2)").Range("By:Hz"), PlotBy:=xlRows ActiveChart.Location where:=xlLocationAsObject, Name:="Cabernet (2)" ActiveWindow.Visible = False ActiveChart.HasTitle = True ActiveChart.charttitle.Text = " " & t Windows("complete Favorite Genes.xls").Activate y = y + 3 z = z + 3 x = x + 4 End With Wend End Sub
I don't really know if putting a variable directly into a range works, so far it doesn't.
As you can tell I need a line graph of the range B3:H5, then the next is the those same columns but 3 rows down, so the next graph is at B6:H8. I'd also like to through in a title here, but I may just be playing with fire. The title is located in column A starting at 4 and corrosponds with each graph, so it too goes is increments of 3, i.e., the first title is at 4 the second at 7 the third at 10. Somebody help me out this isn't working very well at all.
Private Sub Database_Click() Application. ScreenUpdating = False masterfile = "S:OfficeMaster File.xls" Answer = MsgBox("Do You want to export to Final Database?", Buttons:=vbYesNoCancel) If Answer = vbYes Then ThisWorkbook.Worksheets(2). Range("q9").Copy masterfile.Worksheets(1).Range("a4").Paste ThisWorkbook.Worksheets(2).Range("q9").Copy masterfile.Worksheets(1).Range("d4").Paste ThisWorkbook.Worksheets(2).Range("b3").Copy masterfile.Worksheets(1).Range("b4").Paste ThisWorkbook.Worksheets(2).Range("b9").Copy masterfile.Worksheets(1).Range("c4").Paste ThisWorkbook.Worksheets(2).Range("e9").Copy masterfile.Worksheets(1).Range("e4").Paste ThisWorkbook.Worksheets(2).Range("g9").Copy masterfile.Worksheets(1).Range("f4").Paste ThisWorkbook.Worksheets(2).Range("i9").Copy masterfile.Worksheets(1).Range("g4").Paste End If End Sub
for some reason, it doesnt work as it said the masterfile.worksheets is not recognized, but as i go to the immediate thingee, i type in ?masterfile, it give me the exact address, i had been working on this thign for hours, and have no idea what i did wrong.
I have a very peculiar problem. Each month I have a set of financial products for which I calculate the IRR values using XIRR, as the cash flows are uneven. Imagine this, Product names in ColumnA, the cashflow dates in B, and the cash flows with transaction types in ColumnC and finall D has amounts in negetive and positive. Each month I add new cashflows for each product as rows.
Manually I scroll down to select the range for XIRR (both date and amount) Offlate this work has gone really high in terms of values, and manually cacluating XIRR takes hell lot of time for all the products (currently 80 odd)
The attached workbook is a follow up report that gets run every Friday. There are 1092 cells that contain formulas that reference a file that was created on Wednesday of the same week. eg.
I am trying to do is have the code display an input box asking the user to type in the file name, or generate this automatically? to an existing folder in a drive
My master file, imports data from another source (excel file) pastes it in the necessary columns at the moment, however the file is run every Monday so the file name is generic, last week was 10723, this week will become 10724 when the data is run next week,
Can I do what I want to? Via either an inputbox for the file name, or by using something so the file name becomes “107 “ & Weeknum(today()-3)
I have 1 spreadsheet in which I need to calculate the % weight of some numbers. Columns A to G have data I do not need for the calculation.
Column H has the numbers of whose % weight I need to calculate. The problem is that the no. of rows containing the numbers can change from week to week and I am trying to automate the calculation of the % weights.
So I want try to use column G (as a reference) (like go at the bottom of it so I can know how many rows of numbers there are) and then calculate the % weights.
I am trying to use Excel VBA to conduct a transaction automatically.So far,I managed to get all the required input in.Now,I need to click the "Submit" Button.
I was thinking of using the InternetExlorer.Document.Links(x).Title to search for the link of the "Submit" button and then use the .Navigate method to proceed to the link.
But when I wrote and ran the :
InternetExlorer.Document.Links(x).Title
i get the following mesage:
"Run Time Error 98
A property or method cannot include a reference to a private object ,either as an argument or as a return value "
I have a spreadsheet which i work on everyday, basically I have a formula which needs to be copied in the second column depending on the range in the first column.
Ex : one worksheet will have A1:A100, the B column should be copied to B1:B100, if the next one has A1:A200 then B column should be copied to B1:B200, how to do this with VBA?
My spreadsheet is linked to a stock data web page using the little documented .iqy function. I have set cell A1 to the web page ticker symbol, so that whenever this cell value is changed the data in the spreadsheet is automatically updated.
My problem is in several parts: 1. Read TickerSymbol.csv file which is comprised of just ticker symbols, 2. Insert (TickerSymbol.csv) symbol 1 into cell A1, 3. Save the results to a file c:XXXSymbol1.csv, 4. Insert (TickerSymbol.csv) symbol 2, 5. repeat, repeat
I have created a workbook consisting of a 'master sheet' (this sheet is a worksheet in itself but also summarizes the totals from the subsequent worksheet) and subsequent work sheets. I wish to be able to add worksheets as needed while having the 'master sheet' still updating as you add sheets.