Select Cell Range Relative To Starting Position
Mar 5, 2007
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 7 Replies
ADVERTISEMENT
Jul 14, 2014
I need to insert a picture on a Excel 2010 worksheet.
The worksheet contains a variable number of columns, and the column width is also variable.
The picture needs to be aligned such that the right edge of the picture is in line with the right side of the last used column.
This is so that when the worksheet is converted to a PDF document the picture (company logo) appears on the top right hand side of the page.
I can find the cell reference for the last column, and I can move the picture around the page. But I'm not sure how to find the position of the last column on the page?
I've got this far:
'copy picture and resize and position
Sheets("Output File").Select
ActiveSheet.Shapes.Range(Array("Picture 1")).Select
Sheets("External data sheet").Select
Cells(1, 10).Select ' I can make this be relative to the last column on the WS
ActiveSheet.Paste
Selection.ShapeRange.Height = 50
Selection.ShapeRange.IncrementLeft 0 'put the picture in a position relative to the cell selected
View 5 Replies
View Related
May 16, 2014
I am trying to hide a column titled "Test". This column is usually column E, but it is possible it can be moved to column D or F. If I use:
[Code] .........
Then column E is hidden, regardless of whether Test is moved left or right. The reason Test moves is due to the user inserting or deleting a column.
View 5 Replies
View Related
Jan 19, 2012
The data is a copy of CSV file in to a spreadsheet. Below is what the paste looks like:
100100 Books
1234.ab
1345.bf
1456
1567
100100 Books
100200 Magazines
100300 Videos
1234.ab
1345.bf
1456
1567
100300 Videos
All of the numbers between the 100100 Books, 100300 Videos belongs to that category. This repeats hundreds of rows down. I need the column to the left of this range to display which division it under. I parse this in excel so Books, Videos, Magazines will be in their own column. I am not exporting the CSV so have to manipulate it in excel. I need to have the column to the left combine the whole identifier so I can perform a VLOOKUP on it. It should look as follows:
100100.1234.ab
100100.1345.bf
etc.
My approach was to get the column to the left of the range to display the correct division, then have another column combine by using (cell)&"."&(cell).
View 1 Replies
View Related
Apr 2, 2007
I am trying to create a form with a text box that when text is entered, it adds to the userform another textbox with that data in it. The focus then goes back to the original textbox so that more may be added indefinitely, all with relative position to the most recently added textbox. Here is what I have so far, works for one time, but not multiple textboxes. The original textbox is "txtAPID"
Private Sub MakeNewTextBox()
Dim newTextBox As TextBox
Dim ControlTop As Long
With LastAirport
ControlTop = 30
End With
Set newTextBox = Me.Controls.Add("Forms.TextBox.1", "txtNextAirport", True)
With newTextBox
.Left = 20
.Top = ControlTop + 3
.Height = 15
.Text = txtAPID.Text
End With....................
View 2 Replies
View Related
Aug 1, 2008
I've got a list of story titles that are organized by genre, and I'd ultimately like to have a formula (not VBA) that automatically generates a "story id" based on the title's position within the genre (not the list overall). Here's a snippet of the list:
CREATE TABLES LIKE BELOW?IDGenreTitle
1.1GeneralTitle 1
1.2GeneralTitle 2
2.1Horror / SupernaturalTitle 3
2.2Horror / SupernaturalTitle 4
3.1Sci-FiTitle 5
3.2Sci-FiTitle 6
So what I'm looking for here is the formula that would give me the ID shown in the first column (I can modify it later for specific purposes). I've tried variations on the MATCH, INDEX, ROW, and VLOOKUP functions but just can't seem to get it quite right. Note: All the items in the Title column are unique - there are no duplicates.
View 3 Replies
View Related
Apr 9, 2014
My OS is Windows 7. My version of Excel is 2007. I have read how to get a macro to move around a page in a relative way, but is there a way to move from page to page in a relative way as well?
View 5 Replies
View Related
Jul 4, 2007
I found dynamic range name across columns:
=OFFSET($A$1,0,0, COUNTA($A:$A),COUNTA($1:$1))
which expands across Columns in Row 1.
What if we don’t know range to be named in which row or column starts?
What will be a formula (assume range starts on cell named “BegRng”)?
View 3 Replies
View Related
Nov 10, 2009
I have a macro that is copying information from one spreadsheet into columns "A through E" on the next available row in a seperate spreadsheet. Beginning with column "G through L" I need to copy the formulas from the row above in the same spreadsheet. Is there a way to do that since the row being copied from will change each time?
View 4 Replies
View Related
Mar 13, 2014
I'm using the VBA code below for a piece of code.
[Code] .....
However, I want to use this same function in another place, without the limit set on the cells that will be cleared.
What I would like to do is send the formula the cell to start at (E14), offset that by one column, and then send a number of rows. With that, create the range to be cleared.
Something like:
[Code] .....
View 3 Replies
View Related
Feb 19, 2010
I need to accomplish selecting a range using variables for the starting cell and ending cell rows and columns.
Pseudo code (how I'd like the syntax to be, but isn't):
Worksheets(mysheet).range(startCellRow, startCellCol : endCellRow,endCellCol).Select
With Selection
...
End With
View 6 Replies
View Related
Mar 22, 2009
In 1 cell i need a forumla to give me a starting number and take one away each time a code or codes are dislayed in a cell range. Something like this
A1 = 23 days or any number days i needed
Cell range B1:C52
every time a selected 1 or 2 letter code appears in the cell range i want A1 to subtract 1 day from the starting number, i would need it to subtract half a day if one code appears ie HD, the codes may be P, OT, HD
View 4 Replies
View Related
May 22, 2014
Is there any way to set the list from a Combo Box to automatically start in the middle?
Whenever I select a Combo Box, the drop down list always automatically starts at the top of my selected range, ideally I would like it to start in the middle.
For example my Range that I am filling the Combo Box with lists dates: Today()-7 to Today()+7.
At the moment the first item in the combo box list is Today()-7, I would like it to be Today().
View 2 Replies
View Related
Oct 31, 2006
I would like to select a range of cells relative to the current cell and move them to the right two cells. Basically, if I were in cell A1 I want to be able to have a macro select A1 - A8 and move them over by two cells leaving A1 and A2 blank.
Excel 2003
View 9 Replies
View Related
Aug 26, 2013
I have a macro that copies three sections of data from a word document and pastes it within the Excel worksheet. The column of data is pasted in a single column (data to be space-delimited and evaluated later), and what I would like the MACRO to do is separate the three sections of data based on the starting and ending values of the section and place them in different columns.
Section 1 : starts with the word "Team"
Section 1 : ends with the word "City"
Section 2 : starts with the word "Location"
Section 2 : ends with the word " Date"
Section 3 : starts with the word "Member"
Section 3: ends with the word "Age"
So section 1 would be pasted into cell C1, section 2 pasted into G1 and section 3 pasted into J1.
Each section will have vary in number of rows between the starting/ending values, so to cut and paste the section based on the starting and ending value is vital.
View 7 Replies
View Related
Oct 24, 2013
I'm creating a Macro to paste a series of formulas from a template in hidden rows above to cells further down in the same worksheet. My code works fine if I set an absolute range as the Destination - here's a portion of it:
Sub TimelineRow150()
Dim TimelineMatch As Integer, ProjectPlan As Object
TimelineMatch = ActiveCell.Offset(0, -1).Value
If TimelineMatch = 26 Then
Range("E26:AQ28").Select
[Code]....
I'd like to be able to copy and paste this code to run it multiple times in the same worksheet, (the TimelineMatch variable actually has 26 different options). Is there a way to replace the [Range("E150:AQ152").Select] portion of the code with a range that is relative to the Active Cell? (I'd instruct users that cell E be the Active cell before running the Macro, and E to AQ is still the range I need the copied data to appear.) I've tried using ActiveCell.Offset, but I keep getting an error of Method 'Range of Object' _Global' failed.
View 3 Replies
View Related
Jul 7, 2008
I want to put a range select statement to select a cell and count down 10 cells and copy.
View 9 Replies
View Related
Jan 7, 2009
Is it possible to position a predetermined cell (e.g. A42) in the top left corner of the screen. (Not every screen users use has the same size)
View 3 Replies
View Related
Jul 23, 2008
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 9 Replies
View Related
Mar 2, 2009
I'm trying to sum a range of data consisting of 12 cells (each range is in a unique row going across adjacent columns). The range of data to be summed is based on a lookup value which is a column header - I want the range to start with this lookup value. In column B of my spreadsheet, I have a 'First Activity number code' going down...In Row 4, I have the same First Activity Codes going across the row (the column headers). In Column U, going down the column I am looking for my formula.
So For example, in row 5 of my spreadsheet, I am looking at 73 as my base code (the value in column B), and I want a range of data summed that lies in the same row where the column headers are 73 - 84 (12 rows) - always starting summing the range with the same number that is in column B. So in row 18, 78 is my base number; so I would want the sum of the numbers in the same row where the column headers are 78-89 ( 12 columns).
View 4 Replies
View Related
Jan 27, 2012
I am trying to write code to select a range in a worksheet where the last cell in the range is variable.
Sub DataTest()
Dim LastColumn As Integer
Dim LastRow As Long
Dim LastCell As range
[Code].....
View 8 Replies
View Related
Jul 4, 2009
I am using Excel to use stock ticker symbols such as FDX "Fedex", to return past volume and price daily performance found in Google Finance that will be manipulated with the current VBA I am working on that will result in a trading pattern. Each trading pattern will happen at a different range in the same column. I will be inputting hundreds of ticker symbols daily so using VBA and not having to enter formulas manually in each is greatly preferred as this will save me lots of time.
I am stuck trying to find the average value of a range starting from the minimum to the maximum value. For instance, Range("H10:H20") may have a maximum value at range("H12") and a minimum at Range("H18"). I would like the average of Range("H12:H18").
Sub Fnd()
Dim MaxStartPriceRange As Range
Dim MinStartPriceRange As Range
Dim MaxPriLocation As Double
Set MaxStartPriceRange = Range("h11:h21")
MaxPriLocation = Application.Max(MaxStartPriceRange)
Set MaxStartPriceRange = MaxStartPriceRange.Find(MaxPriLocation)
Dim MnPriLocation As Double
View 4 Replies
View Related
Feb 20, 2014
The code below is part of a larger script which I'm having difficulties in amending.
[Code]....
In it's current form, this row.
selects cell C7 and expands the range until the cell is blank.
I'd now like to amend this so that I can select the first cell in the range within an offset rather than C7
I can set the offset within the script as below, but I'm a little unsure how to select the first cell in the range.
View 3 Replies
View Related
May 18, 2012
I need a code to select the last cell which is having value in the range of A1:a200. if I use below, it is selecting cell beyond a200.
lr = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
View 3 Replies
View Related
Nov 8, 2006
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 4 Replies
View Related
Jul 23, 2014
I have created a button in an excel worksheet that when clicked, creates new tabs each containing a chart. I have tried to modify the code to position the chart within a specific range of cells on each tab, however, I have not been able to get it to work. Below is my code. Please see the code following 'Add the Chart.
VB:
Sub AddCharts()
Dim lastr As Long, r As Long
Dim shname As String
Dim ltitles As Range, rng As Range, rData As Range, x As Range, tbl As Range, r2Data As Range
On Error Goto ErrorCatch:
Set ltitles = Sheet1.Range("A3:N3")
Set rng = Sheet1.Range("A4:A" & Sheet1.Range("A3").End(xlDown).Row)
[Code] ......
ErrorCatch:
'Debug.Print Err.Number
'Debug.Print Error
If Err.Number = 1004 Then
shname = x.Value & x.Row + 1
End If
If Err.Number = 20 Then Exit Sub
Resume
End Sub
View 6 Replies
View Related
Oct 2, 2012
I am using 2010 and want to select a range starting at the same cell all of the time (regardless of whether or not it has contents - so let's say B7. Starting at B7, I want the range to include all of the columns starting at B and go to the end of all of the columns. Then I want the range to include all of the rows starting at B7 and end at the last row. I will eventually copy this range and paste it on another worksheet start on a specific cell of another worksheet. (Perhaps, we can add a name to this range).
View 2 Replies
View Related
Dec 27, 2012
I have data headings in A1-E1 and actual data in A2-E2. I have a SUMIF function in F2.
A B C D E F
A B A B B
1 2 3 4 5 =SUMIF(A1:E1;"A";A2:E2)
When I insert new data columns between column E and F, the formula still refers to columns A-E although I would like to have the newly added columns in that SUMIF-function included.
For example, if I add 3 new columns, the new SUMIF-function (now in cell I2) should become =SUMIF(A1:H1;"A";A2:H2).
Do to modify the SUMIF-function to do that?
View 4 Replies
View Related
Apr 8, 2014
I'm looking for formula or a method how could calculate a product of range of cells when I have only the first cell, but I need to multiply 260 cells that are in front of it.
I now find a cell using index with double match criteria and use "Cell(info_type, Ref)" to determine the cell where the first value is. I need to take that value and multiply it with with t+1, t+2, etc. to get the final number.
View 4 Replies
View Related
Mar 12, 2014
In the attached file, i want to select the range from cell B8 (which is fixed), to used range i.e. till Cell F18 (but the problem is, cell F18 is not fixed). Last used row or column can change. I want to do it by using VBA.
Here is the sample file Select Range.xlsm
View 3 Replies
View Related