Selecting Data And Using It For Further Calculation?
Jul 11, 2014
I have got historical intraday-data of a stock index and first of all need to summarize them by seconds: The data looks like that:
time price
08.00.00 159
08.00.00 153
08.00.00 148
08.00.01 157
08.00.01 152
08.00.02 167
08.00.02 151
08.00.03 155
08.00.03 154
08.00.03 154
So I want to calculate the average price per second. Is there any formula to tell Excel that it should sort it by second and further calculate the average price of it.
View 5 Replies
ADVERTISEMENT
Mar 10, 2014
Using VBA, I need to Select A1:C14.
The problem is that A1:C14 contains blank cells, and there is also an adjacent column D that I do not want to copy.
So, UsedRegion and CurrentRegion aren't doing it for me. (It selects Column D too.)
Obviously, this is an example...the real data set is an export and varies in size.
View 1 Replies
View Related
Sep 15, 2009
when run a macro to make the macro select all the cells in a specific column that only contain data i.e. i have a spreadsheet 5 columns wide by 23 rows deep, the macro at the moment selects the 5 columns (this never changes) but the number of rows always changes 123, 56, 1, etc i need to to always select the rows that have data.
View 14 Replies
View Related
Dec 1, 2011
I have made a macro which generates a bar chart for many sets of data.
I'm having a problem at the moment however, as I need to generate a line graph on the same chart. How does one generate another x-axis and graph on a chart? Let me know if you want to see my current macro.
View 3 Replies
View Related
Mar 19, 2014
I have a worksheet with about 10,000 rows of data and from this raw data I need to create 60 or so separate spreadsheets for 60 different companies- so around 160 some row actually pertain to a given client. However, it's not fixed and from one month to the next the actual number of rows changes so I can't just use a simple range. There are two possible ways to mark that the data pertains to a new client. But it will be easier to just show a quick example:
1/1/2014
CompanyA
Total
CompanyA
1/1/2014
CompanyB
So anytime it runs into "Total" in column 1 it should know to start another iteration or when the cell in column b does not equal the cell directly above it. Below is a truncated version of what I have so far:
Sub MonthlyStatements()
'
' MonthlyStatements Macro
'
' Keyboard Shortcut: Ctrl+w
'
Dim taxi As String
Range("F32").Select
Workbooks.Add
[Code] ......
View 1 Replies
View Related
Jul 25, 2008
When I am trying to select a data range on another sheet within the same document, I get an error. My code looks like this:
activeworkbook.sheets("mySheet").Range("A1:D3").Select
View 9 Replies
View Related
Jul 22, 2009
I'm working on a macro that creates columnplots from a sheet of data.
The sheet is organised as a series of tables. Through a userform, the user selects the columns and rows that she wishes to include in the plot.
Is it possible to control the order in which the data is plotted?
For instance, the macro produces the following range to be plotted:
"$D$29, $B$29, $C$29,D33:D31,B33:B31,C33:C31"
where row 29 contains the table headlines, and rows 31:33 contains the data.
However, when I plot this, it always ends up as column B, then C, then D, rather than D first like I wanted.
I'm fairly new to both Excel and Visual Basic, so it's difficult to explain exactly what I'm after, but hopefully someone will understand what I'm after here..
View 9 Replies
View Related
Dec 21, 2009
I have that list in column B and I want to put only those cells that end in "RET" in column C and put only those that don`t end in "RET" in column D, by using an automatic method, otherwise I`m gonna have to do it many times in many worksheets manually ;/
View 9 Replies
View Related
May 16, 2006
I need a macro that will, (i) make a copy of the current sheet, (ii) let the user select the month to be selected from a list (Message box), then (iii) delete all other columns in the sheet that is not from the selected month and then, (iv) total the data of the selected month in the empty cell on the right.
The sheet contains data in the following way:
Column A = Description of data in rows
Column B to IV = in each column is a specifics day data
Row 1 = Branch Name
Row 2 = the date in the format 2006/05/16 for 16 May 2006
Row 3 – 361 = financial data
View 2 Replies
View Related
Jun 23, 2013
I have a table with 500 entries which is some physical properties for some chemicals. What I am needing to do is get something to find out what chemical formula the user has typed in another cell on a separate sheet, then VBA finds the component on this table and picks out information from columns within that row. Some of that data will then be used to do a calculation. The user can select up to 20 components, so VBA needs to be able to do it for 1 or anything in between 20 components. The data which I need to use for further processing is within columns D to J.
View 1 Replies
View Related
Sep 12, 2009
I have 2 worksheets where I need to transfer a certain amount of data from one to another depending on dates.
Sheet ‘A’, is the main data source. Col A holds all 365 dates from 1 Jan to 31 Dec(starting at A2), and the subsequent data for each day is held in cols B to P.
Sheet ‘B’ is the destination where the user will specify a key date in cell A3, then in A4 to A32, formulae populate the next 29 dates (ie so there is a continuous run of 30 days).
What I need to do, is to identify the same 30 day range from sheet B on sheet A, then to copy the data in Cols B to P for that range back onto sheet B (pasting in at D3)
View 8 Replies
View Related
Nov 28, 2012
I have a set of data spanning across 400 rows and I want to somehow be able to have a macro to randomnly select say 40 rows of data and highlight it..
View 5 Replies
View Related
Aug 5, 2009
I have ~1,000 rows of data utilizing columns A:C. The data is "grouped" by data that is in column C. Layout is as follows for example
Row01.....Column A......Column B.......Column C
Row02.....some data.....some data.....apples
Row03.....some data.....some data.....apples
Row04.....some data.....some data.....apples
Row05.....some data.....some data.....apples
Row06.....some data.....some data.....oranges
Row07.....some data.....some data.....oranges
Row08.....some data.....some data.....oranges
Row09.....some data.....some data.....oranges
Row10.....some data.....some data.....pears
Row11.....some data.....some data.....pears
Row12.....some data.....some data.....pears
I have been trying to come up with some code that will "select" groups of data and then perform an action on those cells. As an example:
View 5 Replies
View Related
Feb 12, 2010
I am having trouble with a number of workbooks when using vlookup (using the wizard rather than entering the formula directly)
First I select the cell with lookup value, then try to select the table array on the source sheet but the cursor changes to the hand symbol and I can't click on any cells. I have had this problem with many different workbooks. The only solution I have found is to copy the whole sheet, paste-special-values into a new sheet and use that as source.
View 11 Replies
View Related
Nov 22, 2011
I have the following table of data...
ABCDLetter--100.0%-100.0%-----100.0%----100.0%-100.0%---100.0%---100.0%---100.0%---100.0%--0.7%99.1%0.2%-
I would like to populate the Letter column with A B C or D depending on which contains the highest value.
View 8 Replies
View Related
Mar 8, 2013
I suspect this is extremely basic however how do I do the following in VBA? I have dataset with a column having a row of numbers as: 1, 1, 1, 2, 2, 2, 2, 3, 3, 3, etc. What I want to do is copy some information designated by the first 1 then the first 2 then the first 3 to another worksheet leaving the other data. How do I selectively choose the first of the 1, 2 and 3 etc.
View 1 Replies
View Related
Oct 6, 2008
I am trying to write some VBA code to select a range in a column going down to the last item in the column.
So I want to write a macro that will copy cells B4 - B17 into cells A4 - A17 but am having trouble with the code that will select B4 - B17. When I use the usual code which is as follows:
ActiveSheet.Range("b4", ActiveSheet.Range("b1").End(xlDown)).Select
it selects down to A75, and I know why this is but not how to fix it.
This is raw data extracted from a third-party system. It has an option to export to Excel, but where it appears to have blank cells Excel actually believes there is data in there.
So is a way to identify what data Excel thinks is in there and perhaps use this to be able to select the range I want?
Different extractions will need a different range moved over - however it will always start at B4 and go down to B-whatever.
View 9 Replies
View Related
Jan 27, 2009
Im measuring arrivals of buses during a two hour period, for example 10 til 12 Within in this time i have 12 buses.
The Problem
What i need to do is calculate how many buses have turned up in a "rolling hour". This means taking a sample of any 60 mins within the two hours and calculating how many buses turn up. There needs to be a min of 6 buses in that 60 mins
In theoy there would be 60 "rolling hours" within a two hour period.
For example first 60 min is 10 till 11, second 60 min is 10:01 till 11:01 and so on.
View 8 Replies
View Related
Oct 3, 2006
i am trying to find the formula which will calculate information that is between two dates. for example i have the dates Jan'05 right through to Dec'06 and in each month i have different values eg, Jan is 5, Feb is 10 etc. i need to find a formula which will calculate the data between Aug'05 and July'06.
View 8 Replies
View Related
Sep 4, 2009
I would like to create a spreadsheet where I enter a list of 30 – 40 names and associated data over 4 separate columns. I would like the option of having a check box next to each name so when checked, it copies that name and associated data in to a section below. This will give me a reduced list of names (lets say 20). From this section I would like the same again to reduce further and once more after that.
View 9 Replies
View Related
Jul 23, 2014
Attempting to match values in column E with column G and insert the corresponding number (column H) into column F See attached workbook for example.
Hoping to do this using VBA, not just a VLOOKUP.
The code below, places 0 values into the correct rows of column F, instead of the correctly associated value.
Attached file: Macro Match question.xlsx‎
View 4 Replies
View Related
Feb 25, 2014
I have a column that has mostly empty cells. I want to select only the ones that have data so I can perform an operation on all of them. Empty cells must be excluded from selection. I do not know where the bottom of the data is.
The solution needs to be in VBA where it is part of a larger macro affecting the user's worksheet.
View 7 Replies
View Related
Nov 30, 2011
I have a workbook with many tabs and I am writing a macro that works on the active sheet but needs to pick up data from another tab, which is labelled on the active sheet.
As I want to write this macro only once but different tabs have different tabs they look to, VBA code that slects the sheet named in a fixed cell on the active sheet.
This is the code that currently looks up to the tab labelled 'Cork, FA'. However, if this tab name is stored in cell AA2, I would like the code to choose the tab based on the information in AA2?
ActiveSheet.Range("A1").Select
ActiveCell.FormulaR1C1 = "=COUNTA('Cork, FA'!C)"
CountClients = Selection
Selection.ClearContents
View 1 Replies
View Related
May 15, 2013
I am using the following code to re-name filtered data in column 4 of my worksheet, but I am running into an issue when the Autofilter returns only one row of data. The End(xlDown) is selecting a range that extends to the last row of the worksheet (1,048,576).
Code:
rfiltered.Cells(1, 3).Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Receivables"
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
How can I write the code so that in the event there is only one row of data, it selects just that row for the paste (and yes it would be redundant to paste over the same value) and then continues on? I would usually use a Cells(Rows.Count, x).End(xlUp).Row to find the last row of data, but I don't think that will work in Autofilter mode.
View 2 Replies
View Related
Jul 9, 2013
I have to select and cut a column with on several lines empty cells. I use the following code
'Range("c2", Range("c2").End(xlDown)).Cut' but this code select only the data till the first empty cell.
How can I select the whole column till the last cell. The column B is filled with data on every row so I think that I have to use the offset code.
View 2 Replies
View Related
Jul 22, 2013
I have a termination report in which i would like to display data via a graph. However, when I right click to select the data it wont let me add the fields that I want to use.
View 1 Replies
View Related
Nov 12, 2006
i have a set of data range from B1 to K10 (10 cells all together).
and i created a graph by selecting from B1 - K10.
what i want is, if i key in range from 1-10 in A1, the graph will change based on the number i keyed in (from 1-10).
if i keyed in 5 in A1, the graph will auto select B1 to F1 and show data from B1 toF1
then if i select 6 in A1, the graph will auto select B1 to G1 and show data from B1 to G1
View 9 Replies
View Related
Feb 27, 2007
i want to write a macro which searches for data in a xl sheet.It should then format that data in a particular format.The data can be present in multiple places.For eg. formating a 5*6 matrix which starts from d4 cell and a 4*7 matrix which starts from e15 cell(please note that d4 and e15 location are not fixed).
View 9 Replies
View Related
Aug 13, 2006
is it possible to select the data to view by running a macro. my table is based on dates. i would like to see all data before the date i run the table and 6 weeks in front only.
View 3 Replies
View Related
Jan 23, 2007
I m trying to make a button to add values to another sheet in my xls. Ive done that... now i try to autofill the percentages from left and above one row....
View 9 Replies
View Related
