Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Select Range Based On A Cell Value


I have a worksheet with a block of data. The size of this block changes.

I am calculating and storing the upperleft and lower right cell position of the block in say cells AA3 and AB3 on the same worksheet.

So far I have everything working fine.

Now I want to create a button to trigger a macro to sort that block.

I can figure out the button and make it trigger a macro.

One of the lines in the macro is


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Select A Range Based On Active Cell Location?
I'm trying to select a range of cells whereby the range is dependent on the currently active cell. I know you can use the "Activesheet.Range("A1:D2").select" method to select a range where the cells are always the same, but I'm after a dynamic selection where the values can be programmatically altered depending on some other result.

For example, let's say that I make a certain cell active (based on the result of some other formula), and I want to select the range of cells in the adjacent column that is X rows deep. Putting this into context, imagine the resultant active cell is B2, I then want to select the range C2:C10, but if the active cell is E10, the range selected would be F10:F18 (if active cell is X, then range would be Y:Z).

The Offset function would allow me to position the cell based on the current active one, but it doesn't let me select a range. The Range function only lets you choose either hard coded or index cells, e.g. "Range(cells(y,z), cells(y,z)).select", but this is still no good because I'd need to know the index value of the active cell (can this be done?).

View Replies!   View Related
Select Range Based On Variable Cell Content
Based on if the value in col A contains the characters "TT" I want to select the range starting with this cell and ending at the end at the end of the row I'm using (.End(xlRight) and then merge these cells, change colors etc. And then looping this through a 'range' so that it only occurs where the values occur. I can amend various cells based on this idea, but am unable to identify the range and then merge the cells.

View Replies!   View Related
Range Select Statement To Select A Cell
I want to put a range select statement to select a cell and count down 10 cells and copy.

View Replies!   View Related
Select Range Based On Last Row And Last Column Used
I have defined my last row and column by using:

lr = Sheets("week1").Cells(Rows.Count, "A").End(xlUp).Row
LC = Sheets("week1").Cells(1, Columns.Count).End(xlToLeft).Column

Now how do I select the range starting at A2 through the last used column ( LC ) through the last row ( LR )?

View Replies!   View Related
How To Select Range And Clear Based On LR Variable
I want to select and clear a part of a sheet.

here is what I have.

Sub clear_data()
sheets("sheet1").select
lr = activesheet.usedrange.rows.count

Here is where I get lost in translation (syntax).

I want to select starting at Bcolumn through bycolumn but the row be set with the LR from above, since the rows always change.

I could write B2:by2000, but i want to use the LR variable to define the number of rows i have.

View Replies!   View Related
Select Dynamic Range Based On Non-empty Cells
I require code to identify the last row in column 'A' that contains data, and then to select every row up to that one, and each column up to 'H'. My data begins on row 3, and the rows with data varies from row 7 through 120. The columns with data is constant so there is no need to test in that direction.

View Replies!   View Related
Merge Cells: Select A Range Based On Two Variables Which Store The Column Numbers
I am trying to select a range based on two variables which store the column numbers. what I have is:

View Replies!   View Related
Range.select Error (SELECT METHOD OR RANGE CLASS FAILED)
I have this:

Private Sub CommandButton1_Click()
Worksheets("Sheet1").Activate
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
ActiveCell.PasteSpecial
End Sub
it errors to: SELECT METHOD OR RANGE CLASS FAILED


View Replies!   View Related
Select A Worksheet Based On Value Of Cell
I am trying to write a macro, in VBA, that would select the proper worksheet, based on the value of a cell.

View Replies!   View Related
Select Tabs Based On Cell Entry
i'm trying to basically use VBA, so that when a list of work tab names are entered into a column in say Sheet 1..these tabs are then selected.

View Replies!   View Related
Select A Row Based On The Text Value Of A Cell
How can I select a whole row based on the text value of a cell.

View Replies!   View Related
Select Multiple Worksheets Based On Cell
I have a column where each cell contains the names of a variable number of worksheets in the format ["sheet1", "sheet2", etc. ] without the brackets but with the quotation marks. I would like to have some code that will select all of the sheets mentioned in a given cell. I found lots of threads about selecting an individual sheet based on a cell, but none about selecting multiple sheets. If it is easier, I can change the format of of the cells, or break each cell out into multiple cells in a given row. The best code I could come up with was

Sub Selects()
Dim strSht As String
Sheets("Criteria").Select
strSht = ActiveSheet.Range("L31")
Sheets( Array(strSht)).Select
End Sub

View Replies!   View Related
Select Sheet Based On Cell Content
I am trying to do some thing simple, but i causes me a lot of problem in several areas. I am using the

sheets( range("c5")).select

to simply select the sheet that is named in cell c5 on the current sheet. can anyone give me a correct way to accomplish this that I can use in other applications that i come accross?

View Replies!   View Related
Select Row Based On Cell Criteria
I am trying to create a macro that selects an entire row based on a date criteria. The explanation is as follows: I have fields in an excel sheet such as: Name, Date, Place, Time, Country etc….. There are many rows with data in them…. I require a prompt box that asks for a date range and then selects all the contents matching that criteria and copies into another excel sheet called Export.xls.

View Replies!   View Related
Select Cell Within A Range
how to select a cell in opposite (i.e right side of a range) while using a macro. My macro selects a certain range based on user input. The active cell is the left most upper cell. I need to move the active cell within the range to the right upper most cell. I tried the short cut key - tab, while recording a macro, but the macro just selcted a that certain cell.

View Replies!   View Related
Macro To Select Dates Based On Cell Values
I might be using something completely wrong here, but can anyone tell me if this code is possible or am I being very naive.

H4 and I4 are cells in which I want to enter dates, and then I want these dates to be used in a custom filter on another page.

Below is what my limited understanding of VB came up with.

Sub Date_Range()

Dim First As Date
Dim Last As Date
First = Range("H4").Value
Last = Range("I4").Value
Sheets("Graph Data").Select
Selection.AutoFilter Field:=1, Criteria1:=">=First", Operator:=xlAnd _
, Criteria2:="

View Replies!   View Related
Macro To Select Rows Based On Value In Cell A Of Last Row
What would a macro look like that would find the value in the cell of the last row in the A column, then set that as the selection criteria so macro could copy all rows that match that to another workbook?

View Replies!   View Related
Select Range Of Unlocked Cell
I am trying to select range of unlocked cells and clear it's contents? How can i do that?

Right now I have a code to select ALL cells.

View Replies!   View Related
Select The Top Right Cell In A Non-contiguous Range
Is it possible, through VBA, to select the top right cell in a non-contiguous range? For example, I have defined A1, A3, and A5 as a range. How could I tell Excel to select A5 from within that range?

I am using Excel 2007.


View Replies!   View Related
Select And Copy Range From Active Cell
I want my code to evaluate each cell in column B, and based on its value, copy the row from D to X and paste on the newly activated worksheet. I'm trying to use Offset, but it's not working.

View Replies!   View Related
Select Range Of Cells And Post Them Into One Cell
I’d like to copy a range of cells: (A1 – A4) and paste them into Cell A5, but here’s the rub: I would like the values after I paste them to be separated by commas.

View Replies!   View Related
Intersect In VBA: Select More Than One Cell In The Range
I have a piece of code that changes the format of a cell using an intersect statement on the range in question. However, if I try and select more than one cell in the range then an error message is returned: 'Run Time Error 13': Type mismatch. I know that it is caused by selectin multiple cells - is there a way to error handle this so that, when multiple cells are selected, it ignores the intersect statement?

View Replies!   View Related
Select Every Nth Cell In Column Range
What is the best way to select every third cell in column c starting with cell C5

View Replies!   View Related
Select Cell Range Relative To Starting Position
I need to change multiple conditional formats which apply to cell ranges 1 row x 17 columns wide. The specific change I am making is able to be acrried out with the code

With Selection.FormatConditions(1).Font
.ColorIndex = xlAutomatic
End With

However, I dont want the user to have to manually select each 17 cell area. I WANT THE USER TO JUST MANUALLY SELECT THE FIRST OF THE 17 CELL AREA. I want to write a macro that will THEN select a row of cells, 17 cells long, from the MANUALLY SELECTED position as at the moment of running the macro. Unfortunately the cells are not always on the same row in each sheet. On one sheet they may be on row 5, on another row 8 and on another, row 15 etc. I was wanting to be able to select a 17 cell range 'relative' to where the cursor is. How do I write the code to select the relative cell range? Is there an answer in an R[1]C[1]:R[1]C[17] sort of code?

View Replies!   View Related
Select Single Cell After Paste Large Range
I would like to select cell A6 after pasting a large range so that when send to user the entire range is not selected. I tried

wsDestPage.Range("A6").Select

And

wsDestPage.[a6].Select

I receive

Select method of range class failed

How can I select a single cell after pasting the large range?

View Replies!   View Related
AUTO SELECT THE ACTIVE CELL RANGE IS VARIABLE ON MY REPORTS
I RECORDED THIS MACRO BUT I WILL LIKE TO MAKE TO AUTO SELECT THE ACTIVE CELL RANGE IS VARIABLE ON MY REPORTS MY CODE

Sub FORMAT_AS_A_TABLE()
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$L$1900"), , xlYes).Name = _
"Table1"
Range("Table1[#All]").Select
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleDark5"
End Sub

View Replies!   View Related
If Select Sheet, Select Range Statement
I have a command button on sheet MASTER. When the workbook is Activated I want it to check and see if in sheet COSTM, cell B3 there are the words "Project Number", if so then show command button (ClearPrevious), if not, don't show. Also, when the If statement is finished, then the workbook needs to end up showing the sheet MASTER. I have tried various codes and none work, or they are on perpetual loops. I know this has got to be simple, but cannot find an example to take from to solve the issue. Would appreciate any help offered. Below is code I have right now.

Private Sub Worksheet_Activate()
If Sheets("COSTM").Select Range("B3").Select = "Project Name:" Then
Me.ClearPrevious.Visible = True
Else
Me.ClearPrevious.Visible = False
End If
Sheets("MASTER").Select
End Sub

View Replies!   View Related
Find Last Cell In Range, And Changing Another Cell Based On Offset Text
If i have a range say E12:O12 and want to find the last cell before blank, lets say it finds m12 as the cell with the value before blank. then with m12 it needs to determine weather row 11 in the same column has Text either "S" or "F" if "S" then m12 = t if "F" then m12 offset(1,-1) = t. And just to make things more difficult i need the range E12:O12 to step 2 as well until it gets to E208:O208 .Noting that row 11 never changes and will always have either an "S" Or an "F"

and also t = time()

View Replies!   View Related
Dynamic Range Based On Cell Between A Header And Footer Cell
I need to create a dynamic range based on cell between a header and footer cell. The header cell ( A8 ) will remain static, however, the footer cell starts at A10 and then will move down as rows are added.

View Replies!   View Related
Select Specific Cell Then Select The Row
I have column A with various values in cells.

For instance, DG, GS, HG etc

I wanna do if a cell in column A is equal to DG then select the entire row that contains the cell. then call other sub.

View Replies!   View Related
Display Cell X Of Range Based On Another Cell Number
I am trying to create a table, based on what people predict in sports games. I have most of it in place, but I have hit a brick wall where one part of it is concerned. For example, cells M43:M56 have the scores for each week of what one person predicted., so it could be 2,5,7,2 and so on. What I then need the table to do is determine which round or week it is and display the relevant score. So based on the above let's say it's round 3, and his score would be 7 for the week, and the cell to the right of this shows the week's score plus his total score so far.

View Replies!   View Related
Selecting A Range Based On The Value Of One Cell
I have rows of data, and the first number corresponds to the number of subsequent numbers that are relevant.

I want to paste that number of values into a second spreadsheet. How do I do this?

e.g.

A1 B1....
6 21 15 14 32 85 14 16 21 25 26 24

In this case I want to copy the 2nd-7th number, as the 6 tels me I want the next 6.

View Replies!   View Related
Selecting A Range Based On Cell Value
how (in VBA) to select a range within a column where all values are the same?

For example, given the following:

A B
1 ID Value
2 1 A
3 2 A
4 3 A
5 4 B
6 5 B
7 6 C

How would I select range(B2:B4) where all values = A?

View Replies!   View Related
Hightlight Range Based On Cell Value
I would like a macro to run everytime A1's value changes.

The following works for an entire row, however, I would like range A:F highlighted not .entirerow.

I have thought of conditional formatting, but I thought the range I was using was to large. (A3:F40000)

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("a1"), Target) Is Nothing Then Exit Sub
If Range("a1").Value > 0 Then
Call mymacro
Else
Cells.Select
Selection.Interior.ColorIndex = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("A1").Select
End If
End Sub

View Replies!   View Related
Sum A Range Based On A Cell Value
I am looking fo a simple way to carry out the following..

My Spreadsheet has a range of data from column D to O

I have a Total Column Q

I have a Cell, eg A2, where I enter a value from 1 to 12

Depending upon the value entered into A2 will dictate the range of cells to sum, ie

A2 = 4 - Column Q would Total Range D?? to G??
A2 = 12 - Column Q would Total Range D?? to 0??
etc.

View Replies!   View Related
Switch Based On Cell In Range
I would like to find a way to chose a string and number based on cell location

ex.

_____A_________ B _______C
1___Mike,1____Mike,2____Mike,3
2___John,1____John, 2___John, 3
3___Steve, 1__Steve, 2__Steve, 3

I Want to run this in a subroutine, but I am not entirely clear on the abilities of datachecking in ranges.

View Replies!   View Related
Formatting Range Based On First Cell
I'm trying to format a range of cells based on a single cell. Below is the code that
I am using:

Private Sub Worksheet_Change(ByVal Target As Range) ...

View Replies!   View Related
SUM Range Based On Cell Value
I'm trying to create a = SUM( RANGE) forumla, but, I need the first cell reference to represent the value of a different cell...

something like...

=SUM("A1"1:H1)

Where A1 holds the value of a cell number. ie, cell A1 may have the value 'e', so I want the result to be the sum of range e1:h1.

View Replies!   View Related
Lookup Array/Range Based On Cell Value
I need the array portion of a lookup formula to change based on a cell value.

On a worksheet(named "groupings")that groups students according to ability level, I have data validation lists where teachers select the criteria for the group. In cell I5 they choose the period, and in cell L5 they select which assessment to look at.

Cell B8 will either say high low or medium. Then in cell C8 I have this formula: Lookup_Occurence($B$8, ??????, 1,1,2)

Again the array will depend on what is entered in cell I5 and cell L5.

I have attached one of the worksheets the lookup_occurence will be referencing. In this exit card worksheet if cell B10 (the name of the assessment) matches what the teacher chose in cell L5 on the groupings worksheet, then the array formula will be 'Exit Card'!Card1. If cell G10 matches what the teacher chose in cell L5, then the array would be 'Exit Card'!Card2. Where A9:C339 is named Card1 and cell F9:H339 is card2 etc.....

However, it gets even more complicated then that because within Card1 there are 8 periods, and if a teacher chose period 1 in cell I8 on the groupings sheet, then I only need the array to look at the period one data in the card1 array.

Essentially what I am trying to accomplish is create a page where teachers can group students by ability level according to any assessment they choose. On the exit card page the formulas currently equaling # Div/0 will say either high, med, or low depending on how a student performs on that assessment.

View Replies!   View Related
Delete Sheet Based On Cell Range
Let's say that I've got 125 sheets. The 1st "Reference", and the rest sheets names are "1" to "124". I need a macro to delete sheet based on cell range A1:A300. If the range contain 25 data so the number of sheet will be 26 (Reference and 1-25)

View Replies!   View Related
Assign Range Name Based On Cell Values
In my workbook sheet 11 has some ranges that need to have names based on cell values in sheet2 (for purposes of data validation lists).

Range S28:S46 will assume the name of sheet2A11 & sheet2A3.
(example name period_1unit_1)

Range U28:U46 will assume the name of sheet2A11 & sheet2A4.

Range W28:W46 will assume the name of sheet2A11 & sheet2A5

Right now I am calling the code when something is entered into A11.

I have tried if statement and select case, but I ran into complications with both.

I have posted both codes with the questions I have concerning those codes.

View Replies!   View Related
Populate A Cell Based On Range Of Data
I need to modify the code below:

PHP
Sub Macro1()
 Macro1 Macro
    Dim strbody As String
    For Each cell In Range("B2:B640")  
    Sheets("MASTER").Select
    Cells.Select
    Selection.Copy
    Sheets("Final Merged").Select
    Sheets.Add
    ActiveSheet.Paste  
   Next
End Sub 

The code above is creating a new sheet for each different value in column B. I would also like to take that value and place on the newly created sheet in cell A17.

Also, how would I get the sheet to be ranamed to match the value in cell A17 of the newly created sheet?

View Replies!   View Related
Return Value Based On Cell Being Within Numeric Range
I have a spreadsheet that has (amongst other things) 2 charts based on dynamic data with ranges that redraw using vb... The charts will draw envelopes that correspond to an aircraft's moment or c of g envelope - there will typically be 2 envelopes drawn, "normal" and "utility" or "aerobatic".

Plotted on the charts are an aircraft's takeoff and landing weights and moment/C of G.

I'd like to be able to use a formula or VB to determine which envelope takeoff/landing information falls in (or if it falls outside all envelopes). This data would then be used to generate an error message/possibly also in conditional formatting, etc.

View Replies!   View Related
Set A Range Based On Active Cell
I would like to start off in col A, do an offset until a certain value is reached:

View Replies!   View Related
Copy Range To New Workbook Based On Cell Value
to add additional questions to this link it says that the thread is expired so here i have posted a new thread.

Copy Cells To New Workbook If Match Criteria what i need is to copy the cells in new workbook "as is" i mean copy only the values and not the formula.

View Replies!   View Related
Formatting Cell Based On Match In A Range
I have two sheets (Results and August) with numbers in column A, I want to change the color of the cell on Results if its contents matches the number on August. I was feeling adventurous today, so I tried writing some VBA code, but it changes the color of ALL the cells. What am I missing?

Sub FindMatches()
'Compares student numbers on Results sheet to those on August sheet; if match is found then highlights the student number
Dim Sht1Rng As Range
Dim Sht2Rng As Range
Set Sht1Rng = Worksheets("Results").Range("A1", Worksheets("Results").Range("A65536").End(xlUp))
Set Sht2Rng = Worksheets("August").Range("A1", Worksheets("August").Range("A65536").End(xlUp))
Set d = Nothing
For Each C In Sht1Rng
Set d = Sht2Rng. Find(C.Value, LookIn:=xlValues)
If Not d Is Nothing Then
Sht1Rng.Interior.ColorIndex = 10
Set d = Nothing
End If
Next C
End Sub

I suspect that I shouldn't be using sht1rng.Interior.Colorindex, as I think that may color the entire range? What should I use instead?

View Replies!   View Related
Variable Range Formula Based On Cell
Here is what I would like to do: Have a formula like Max(B12:B14) change to Max(B12:B18) when I change the value in different cell from 3 to 7. I would like to stay away from the macro world and keep in formula world if that is possible.

View Replies!   View Related
VBA To Color Range Based On Cell Value
if you have an error on a spreadsheet, why doesnt this come under " case else" when vba is run past it? I have this simple code, but it doesnt work when the value on the spreadsheet is an error ("N/A").

Sub RQVChng()

Dim lgrow As Integer

lgrow = 7

Do Until Cells(lgrow, 5) = Empty

Select Case Cells(lgrow, 17).Value

Case 1
Range(Cells(lgrow, 1), Cells(lgrow, 18)).Interior.ColorIndex = xlNone

Case 2
Range(Cells(lgrow, 1), Cells(lgrow, 18)).Interior.ColorIndex = 6

View Replies!   View Related
Expand Named Range Based On Cell Value
I have a workbook containing two worksheets - Sheet 1 being my "data entry sheet" and Sheet 2 is a sort of summary report containing multiple named ranges (all grouped according to similar values in a cell i.e. all cells containing 1-1 is named GRP1, all cells containig 1-2 is named GRP2). Basically, my workbook looks like this:

Sheet 1
A B C D
1 Lea Female 1-1 Lea Wells
2 Leo Male 1-2 Leo Anderson

Sheet 2
A B C D
1 Anne Thomas Female 1-1
2 John Doe Male 1-1

4 Jimmy Beads Male 1-2
5 Mary Fox Female 1-2

Hence, when I enter the data as shown in Sheet 1, Sheet 2 should look like this:

A B C D
1 Anne Thomas Female 1-1
2 John Doe Male 1-1
3 Lea Wells Female 1-1

4 Jimmy Beads Male 1-2
5 Mary Fox Female 1-2
6 Leo Anderson Male 1-2

View Replies!   View Related
Copy Range X Times Based On Cell Value
Is it possible to have the user inputing a value in cell $G$8 and have the range B11 to G11 to be copied x time depending the value of Cell $G$8 down. ie: Value is 5, it will be copied on 5 rows under B11 to G11??

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved