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 ;/
I have two excel sheets with lots of data in, that i import from a database. Based on these two sheets I make four other sheets that are suppose to get input from this two sheet. To decide what lines I shall copy to the other sheets, I use a filter. But my problem is that there can occur new data too the two main sheet....
View 6 Replies View RelatedUsing 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.
I have a database in Excel 2013 and now I want that when a value (a person's name) is entered in a cell. That then the database sort of filters the list for me, so it's still possible to make changes in the entries.
[URL]
Picture above to specify the search, which I would therefore like to edit
Dashboard_Action Pool Team 7.2.xlsm
I have been all morning working on a simplified version of the tutorial from YouTube: Create your own Excel Search Pt. 4. But came back later so only then that I can not change the data:?
How do I go about using an advanced filter to filter a list of data e.g.
boat
boat
boat
car
car
truck
and have the filter extract only the boat entries to another worksheet, so on another worksheet I end up with
boat
boat
boat
1- Force cell format date to by (yyyy/mm/dd) only, with worng msgbox( validation).
2- Make the first day of a month in a color cell
I've Tried this In Conditional Formating (=VALUE(right(A1;2))=1) but didn't work
3-Make Advanced Filter to filter data between two dates .
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.
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 RelatedI 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.
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] ......
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
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..
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
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)
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 RelatedI 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:
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.
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.
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 RelatedI 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.
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 RelatedAttempting 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‎
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.
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
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.
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.
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 Relatedi 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
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 Relatedis 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