Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Dynamic Range by all cells with the same value
I have a spreadsheet that is created from external data. I need to be able to
create a range for all cells matching certain criteria. Not all criteria will occur every day, and there may be one or many cells containing the same criteria. For example, Column A contains the Date, and column B contains the part number. There may be one part number for a given day, or none. I need to select all cells with part number 111, irregardless of date and create a named range called 111. I will then create a graph for each named range in my spreadsheet. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Dynamic Range by all cells with the same value
Do you need to create ranges, or do you just need a count of cells that
match your criteria? Perhaps you can set up a separate range where you enter the criteria, then use formulas to calculate the counts. Build the charts from that range. gwhenning wrote: I have a spreadsheet that is created from external data. I need to be able to create a range for all cells matching certain criteria. Not all criteria will occur every day, and there may be one or many cells containing the same criteria. For example, Column A contains the Date, and column B contains the part number. There may be one part number for a given day, or none. I need to select all cells with part number 111, irregardless of date and create a named range called 111. I will then create a graph for each named range in my spreadsheet. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Dynamic Range by all cells with the same value
maybe something like this.
Sub test() Dim rng As Range Dim lLastRow As Long Dim lRow As Long Dim iLastColumn As Integer lLastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastC ell).Row iLastColumn = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastC ell).Column For lRow = 1 To lLastRow If Cells(lRow, 2).Value = 111 Then If rng Is Nothing Then Set rng = Range(Cells(lRow, 1), Cells(lRow, iLastColumn)) Else Set rng = Union(rng, Range(Cells(lRow, 1), Cells(lRow, iLastColumn))) End If End If Next lRow ThisWorkbook.Names.Add "_111", "=" & rng.Address Set rng = Nothing End Sub -- Hope that helps. Vergel Adriano "gwhenning" wrote: I have a spreadsheet that is created from external data. I need to be able to create a range for all cells matching certain criteria. Not all criteria will occur every day, and there may be one or many cells containing the same criteria. For example, Column A contains the Date, and column B contains the part number. There may be one part number for a given day, or none. I need to select all cells with part number 111, irregardless of date and create a named range called 111. I will then create a graph for each named range in my spreadsheet. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Dynamic Range by all cells with the same value
How about an alternative:
Apply data|filter|autofilter to your range Create a single chart But change the chart's properties to show just the data on the visible rows. Select the chart and do: Tools|Options|Chart Tab|check Plot visible cells only gwhenning wrote: I have a spreadsheet that is created from external data. I need to be able to create a range for all cells matching certain criteria. Not all criteria will occur every day, and there may be one or many cells containing the same criteria. For example, Column A contains the Date, and column B contains the part number. There may be one part number for a given day, or none. I need to select all cells with part number 111, irregardless of date and create a named range called 111. I will then create a graph for each named range in my spreadsheet. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Dynamic Range by all cells with the same value
I am actually doing a little more than using a filter. Each part number will
have a set of attributes that will need to be graphed on a daily, weekly, or monthly basis. There are additional columns which I will add to the range, I just need to know how to select all cells where the value is equal to an argument I supply, and create a range using those rows, and the columns that I need. "Dave Peterson" wrote: How about an alternative: Apply data|filter|autofilter to your range Create a single chart But change the chart's properties to show just the data on the visible rows. Select the chart and do: Tools|Options|Chart Tab|check Plot visible cells only gwhenning wrote: I have a spreadsheet that is created from external data. I need to be able to create a range for all cells matching certain criteria. Not all criteria will occur every day, and there may be one or many cells containing the same criteria. For example, Column A contains the Date, and column B contains the part number. There may be one part number for a given day, or none. I need to select all cells with part number 111, irregardless of date and create a named range called 111. I will then create a graph for each named range in my spreadsheet. -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Dynamic Range by all cells with the same value
I want to create ranges, because i am going to set the graphs to use each
range as a series. "Debra Dalgleish" wrote: Do you need to create ranges, or do you just need a count of cells that match your criteria? Perhaps you can set up a separate range where you enter the criteria, then use formulas to calculate the counts. Build the charts from that range. gwhenning wrote: I have a spreadsheet that is created from external data. I need to be able to create a range for all cells matching certain criteria. Not all criteria will occur every day, and there may be one or many cells containing the same criteria. For example, Column A contains the Date, and column B contains the part number. There may be one part number for a given day, or none. I need to select all cells with part number 111, irregardless of date and create a named range called 111. I will then create a graph for each named range in my spreadsheet. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Dynamic Range by all cells with the same value
You could use data|filter|advanced filter to get a list of unique values.
Then depending on the layout of your data (is it sorted?), you could find the first and last cell with each value and name that range. Or loop through all the cells like Vergel suggested. gwhenning wrote: I am actually doing a little more than using a filter. Each part number will have a set of attributes that will need to be graphed on a daily, weekly, or monthly basis. There are additional columns which I will add to the range, I just need to know how to select all cells where the value is equal to an argument I supply, and create a range using those rows, and the columns that I need. "Dave Peterson" wrote: How about an alternative: Apply data|filter|autofilter to your range Create a single chart But change the chart's properties to show just the data on the visible rows. Select the chart and do: Tools|Options|Chart Tab|check Plot visible cells only gwhenning wrote: I have a spreadsheet that is created from external data. I need to be able to create a range for all cells matching certain criteria. Not all criteria will occur every day, and there may be one or many cells containing the same criteria. For example, Column A contains the Date, and column B contains the part number. There may be one part number for a given day, or none. I need to select all cells with part number 111, irregardless of date and create a named range called 111. I will then create a graph for each named range in my spreadsheet. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to create a dynamic named range? | Excel Discussion (Misc queries) | |||
#value! error trying to create a simple dynamic named range | Excel Discussion (Misc queries) | |||
Using dynamic range to create pivot table | Excel Discussion (Misc queries) | |||
How do I create a dynamic range in a macro | Excel Worksheet Functions | |||
create a dynamic Range() | Excel Programming |