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
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
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
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 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
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
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
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
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
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
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
|
TRACKER
