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 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 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
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 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 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 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
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
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 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 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 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
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
'Select Method' Failure 'error 1004 Select Method Of Range Class Failed'
My workbook holds a month template and sheets for each month. I work on modifications in the template ,but would then like to update all the monthly worksheets. I recorded a macro to show me how to start programming the vb sub, but get a runtime failure 'error 1004 Select method of range class failed' when trying to select the column to copy,

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 A Range That Will Be Changing By Column: Method 'Range' Of Object '_worksheet' Failed
I'm trying to select a range that will be changing by column. I'm not sure why my syntax isn't working. What I've got:

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
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
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
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
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
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
Color Row Range Based On Cell On Same Row
I have an excel sheet in which i have a cell A11 with drop down list values=YES/NO.
Now based on the value in this cell i want to fill color in the cells(B1 to B10) i.e for e.g. if i select YES in the cell A11 then the cells(B1 TO B10) should become green in color.

Attached is an sample of what i want.

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
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
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
Colour Range Of Cells In A Row Based On Value Of One Cell
I would like to change cells A2 to G2 to a different colour based on the value of G2

(0 to 48) Dark Green
(-2) Light Green
(-4) Yellow
(-6 to -48) Red

The range of the sheet would be A2 - G130. Each Row would change on value of cells in Column G.


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
Add Hyperlinks To Range & Link Based On Cell Value
I have a spreadsheet of various works-in-progress items, the first three columns of which refer to relevant documents, effectively the request, the estimate and the work order, i.e. RXXX-0001, EXXX-0001 & WXXX-0001.

So the text is entered as above (without file extension as it's also our reference number), and I'd like each to be linked to their respecive .doc files, which are held in different locations, i.e. \servernamefolderRequests or Estimates or Work Orders
e.g. \servernamefolderRequestsRXXX-0001.doc

There's a backlog of over 300 items (so 900 links) so I don't want to go through and manually create the links and as each day can see over 12 items being created it'd be much easier to have it done at the click of a button

Most of the solutions I've seen involve creating a new cell/button with the hyperlink in it - I just want to effectively "right click, create hyperlink" but with the cell contents inserted as the file name (adding ".doc") and with correct folder name too.

FYI the cells are adjacent to one another e.g. A1, A2 & A3 so the selection can be Offset (0,1) for the estimates and work orders.

I don't mind if it ends up being a case of running the macro on each line - some links have already been created already.

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
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
Copyright © 2005-08 www.BigResource.com, All rights reserved