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


Advertisements:










Selecting A Range Of Cells With R1C1 Notation


I'm trying to select a range of cells using the R1C1 notation. But I'm making an error in the syntax. I know it's really simple, I just don't know what's wrong.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Referring To Range() Using Shortcut Notation
I have noticed in many of the Excel help files that the developers liked to use shortcut notation for Range reference in VBA.

For example, Range("A1:B10") would be [A1:B10].

I was doing a bit of testing with this, and was not able to make the shortcut notation work with a variable. Does the shortcut method have the capabilities to do the equivalent of Range("A1:B" & LR)?

If it does have that capability, is it just due to force of habit that we always use Range() to refer to ranges, or would there be a more in-depth reason.

View Replies!   View Related
Selecting Range Of Cells
I am trying to select a range of cells. The range I need to select starts in Cell A1. I need to select every row with data in column 1 up to the first row with no data. I then need to expand the range to all columns up the last column with data, including columns with blank cells.

The problem with my code relates to the selection of columns. It is only selecting up to column F. Column G is empty, Column H has data, Columns I-J are empty, Columns K-R have data.

View Replies!   View Related
Notation Of Formula With Range (small Question)
Ok, I want to use a dynamic range in my Vlookup formula.

I want to use this formula: ....

View Replies!   View Related
Selecting Non Sequenced Cells In A Range
I'm trying to use the SUM function with non sequenced cell but the Range or any other function won't allow me to activate the selecting cells if there are more then 41 cell, I wrote somthing like so:

Sub Test()
Dim str AS String
Dim R AS Range

str = "A1,A3 ...... ,A57" 'this is only an example, assum that there are _
more then 41 cells
Set R = Range(str) 'Here is the Problem, I get a Runtime error 1004 _
Application-defined or object-defined error
R.select
End Sub

But if the str input was a 41 or less Cells address string then it works

View Replies!   View Related
Can I Sum A Range Using R1C1 Format
I'm trying to figure out how many Widget As are in Order 0001, for example.

Order # Product
0001 Widget B
0001 Widget A
0001 Widget A
0001 Widget C
0001 Widget A
0002 etc.

I would think that I should use a sum(R1C1:R1C1) format to figure this out, but I might be making this harder than necessary.

View Replies!   View Related
Ctrl-A Selecting Limited Range Of Cells
I have a spreadsheet wherein if I press ctrl-A (normally Select All) it is only selecting a limited range of cells ($B$12:$M$22). If I press ctrl-A again from there then it selects all.

I can see something like that being useful in some cases and annoying in others, but I have no idea what is causing it. Some of the cells in that range are locked and others are not. Some of the cells outside that range are also locked while others are not. In any case, the sheet is not protected.

View Replies!   View Related
Selecting A Range Of Cells Relative To The Current Cell
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 Replies!   View Related
Updating Named Range When New Contact Is Added Using R1C1 Format With A Variable
The code below is for a macro that allows the user to create a list of contacts. The column containing the names of these contacts are then referenced by a userform (code not present) by way of a Named Range. I want to update the named range whenever the user adds another contact so this new contact shows up in the user form.

I am currently referencing the range containing the names using R1C1 style, but I cannot get the variable aspect to work correctly.

View Replies!   View Related
Copy/Paste Range(s) Without Activating/selecting Range(s)
To initialize some cells/ranges, I am copying a given range and pasting it to another given range using the. Copy and .PaseSpecial methods. However, it would seem that both methods actually select the range(s) for the operations, i.e. the given ranges(s) are activated/selected thus changing the focus on the spreadsheet. I would like to perform both operations without actually selecting the given ranges.

View Replies!   View Related
Subscript Out Of Range When Selecting Range In Closed Worksheet
I am receiving a 'subscript out of range' error on the lines of code below.

I would note that all variables are declared and all seem meaningful as regards what you would expect at that point.

Below is a snippet from the immediate window which indicates what the values are:
completecashname C:CashDevelopmentMyFolderoutputCASH042706.xls
cashsheetname Formatted Sheet
cashcurrcolumn A
cashfirstrow 2
cashlastrow 876

Also the workbooks are both closed at this point (but it makes no difference)

Set CashCopy = Workbooks(CompleteCashName).Sheets(CashSheetName). _
Range(CashCurrColumn & Cashfirstrow & ":K" & Cashlastrow).Value
Set PelPaste = Workbooks(completepelname).Sheets(PELSheetName). _
Range((PELCurrColumn & PELlastrow)).Value

View Replies!   View Related
Selecting A Range Based On Date Within That Range
What i'm trying to do is select a range based on the date which happens to be in the first column of the range.

For example, I have a column (A) that contains dates and column (B) that contains names.

Its easy enough to select the entire range using

Range("A1:B4").Select

I need someting that will analyse column A and conditionally selct the range based on the date in column A (ie. it will only select the rows where column A has todays date).

View Replies!   View Related
RC Notation
I dont know why this wont work
it falls over on the ".Columns(6).FormulaR1C1=" line

View Replies!   View Related
Selecting A Range Using VBA
I am having trouble selecting a range in my data to copy to another worksheet. I have declared two variables (startcell and endcell), as seen below, as strings:

Cells.Find(What:=startdate, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

startcell = ActiveCell.Address

Cells.Find(What:=enddate, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

endcell = ActiveCell.Address

After this I would like VBA to copy all the variables in a range that is bounded by startcell and endcell. Here is what my code is currently for selecting the range:

Range("A2:" & endcell).Select

Instead of A2, I would like to select startcell, but I cannot figure out how. Currently I can get VBA to recognise endcell. For example, using the formula above I can copy the range from A2 to endcell. However, when I try and use startcell as the beginning of the range (in place of A2) the code does not work.

View Replies!   View Related
Selecting A Certain Range That Changes
I have two tables in two different worksheets. In the first table, rows get deleted if the value in column 'S' (the first row to the right of the table) is not '1', and columns get deleted if the value in row '2' (third row above the table) is not '1'. In the second table, the number of columns stays always the same but rows get deleted if the value in column 'N' (first column to the right of the table) is not '1'.

After the rows and columns are deleted the tables get pasted in a ppt presentation.
Now the problem: I am a beginner in VBA and up til now I am only able to define a certain Range, e.g. ("A5:M17"), to be copied and pasted into ppt. But since the tables will sometimes be bigger than other times depending if rows/columns are deleted, I would rather define a dynamic Range. The top left cell of both tabels to be copied is always 'A5' but the bottom right cell changes.

View Replies!   View Related
Selecting A Range And Then Summing That Range
I am trying to select a range of numbers based on an active cell. I then want to sum those numbers and have that total reported to a specific cell.

For example:

Say I have a column with a list of dates (Jan/04 thru July/06). I want the user to be able to click on any given field and have that field plus the 11 fields above it summed and reported. Any help?

I came up with something like

ActiveCell.Resize(12, 1).Select

This only selects the data from the active cell and goes down...I need the opposite. I need it to select the 11 cells above the active cell (plus the active cell) and sum that data.

View Replies!   View Related
Selecting A Range Of Numbers
I'm trying to obtain a formula that allows me to select the last 20 numbers in a range of numbers that continue to increase weekly. i.e column a3-a3000 increases by one number every week. I need the formula to sum the lowest 10 numbers of the last 20. If there is 30 numbers from a13-a33 week one. I require the formula to pick up the 10 lowest from the last 20 (a13-a33). Week two the cell increases by on to a34. The formula would then have to pick up the lowest 10 from the last 20 (a14-a44) and so on.

View Replies!   View Related
Selecting Variable Range
I need to select starting with:.

Columns A-O
Rows 1 - ( LR of ColB - Col A is empty )

View Replies!   View Related
Selecting Specific Value Within A Range
I have a spreadsheet with Employee details and each employee has a Indicator and a Score.
I'm battling to get Excel to do a lookup on the Indicator, find the applicable Score Range, see if the individual's Score falls within the range and then inserts the relevant % into a cell in a specific row of each employee.

If we did not have more than 2000 doing it manualy might have been an option. Also, the Percentages and Score Ranges in the matrix might change a couple of times....

View Replies!   View Related
Selecting A Range Using Variables
How would I select a range using variables? For example, say I wanted to say

myRange = Sheets(1).Range("A1:A100"),

but the column was variable. How would I do this if the column number was stored in a variable?

View Replies!   View Related
Selecting Range Up To Blank Row
I am having a few problems selecting a range. What I am trying to do is select a range where the number of columns stay the same, but the rows are variable; ranging from one row upwards. At the end of the rows, there is a blank row. I currently have;

View Replies!   View Related
Selecting A Range Using VBA Code
I have attached a spreadsheet...I'm trying to select Range A1:N16 using VBA code...

Range(activecell,activecell.end(xltoright)).select
Range(selection,selection.end(xldown)).select

When I use this code is only gets A1:N6...: is there anyway to get it to select the range all the way to the end of the data. I tried CurrentRegion and some other stuff but can't get it to work.: The range may go upto line 500 or 3 I just never know.

View Replies!   View Related
Selecting Generic Range
I'm trying to write a formatting macro that will insert a gray divider (row) into a blank line. I'm going to do this with a keyboard command, but I'm having a hard time selecting to column AJ. Obviously this command...

Range(Selection, Selection.End(xlToRight)).Select

...takes you to the end of the page, but I don't need to go that far, just to column AJ.

View Replies!   View Related
Selecting A Discontinuous Range
I'm trying to select a discontinous range, lets say B2:B30 & D2:D30 and this works fine using the code below:

Range("B2:B30,D2:D30").Select

However if I use the code below, B2:D30 is selected including column C.

Dim LastRow As Integer
LastRow = Range("a65536").End(xlUp).row
Range("B2:B" & LastRow, "D2:D" & LastRow).Select

View Replies!   View Related
Convert Formula To RC Notation
I have got a list of formulae in column A e.g.

'=SUM(E14:E24)
'=SUM(E14:I14)
'=SUM(J14:N14)
'=SUM(Q14:Q24)
'=SUM(V14:V24)
(formulae is stored as text, i.e. preceeding with an ')

and i am looking for a VBA macro to cycle through the list and give the relative notation in column B i.e.

'=SUM(R[-11]C:R[-1]C)
'=SUM(RC[-12]:RC[-8])
'=SUM(RC[-8]:RC[-4])
'=SUM(R[-11]C:R[-1]C)
'=SUM(R[-11]C:R[-1]C)



View Replies!   View Related
Square Brackets - Vba Notation
I often see square brackets around ranges in code. eg [A1]

if there are any advantages/disadvantages with using this notation, apart from the obvious its shorter?

View Replies!   View Related
Meaning Of Bracket Notation
The following code was posted in another thread:

Private Sub UserForm_Initialize()
Dim a, v, x, ws As Worksheet
Set ws = Sheets("Scheduled Courses")
a = ws. Range("a2", ws.[a65536].End(xlUp)).Value
With CreateObject("scripting.dictionary")
For Each v In a
If Not IsEmpty(v) And Not .exists(v) Then
.Add v, Nothing
End If
Next
x = .keys
End With
With Me.ComboBox1
.Clear ' clear the listbox content
.List = x
.ListIndex = 0 ' select the first item
End With
End Sub

How are the brackets used in the 4th line? I have not seen this notation before. Is this some kind of a shorthand for a Range("A65536") reference?

View Replies!   View Related
Selecting Cells
I have 2 Cells

Cells( 2,1) and Cells ( i , 1) with "i" a While Counter, and i need to Select All the Cells between that, including both cells.

I'm tring Cells(2,1):Cells(i,1) but obviously this didn't work


View Replies!   View Related
VBA Selecting Cells Only With A Specified Value
To make things simple, I have a range of A1:A10. Within this range I only need to select cells thats contain "Tax".

View Replies!   View Related
Selecting A Range In Excel Macro
I have created the below macro to show the user (when he / she clicks on a button) how many days are left till the deadline day (taken from MS Project).

Sub DaysLeft()
Dim rng As Range
Dim count As Integer
Set rng = Sheets("Programme Reporting").[E2:E200]
count = Application.Evaluate("NETWORKDAYS(Today(),E3)")
Range("L3:L200").Select
ActiveCell.Value = count
End Sub

The range I want to output the days to is L3:L200 that seems to be okay. I have an issue with trying and putting a range in the NETWORKDAYS area, if I try and put in E3:E200 (the range of dates) it fails, as does $E$3:$E$200. At the moment the above code does work but it only puts in the days for one of the entries (E3).

View Replies!   View Related
Selecting One Cell Copies Range
Question 1) Is there a way that whenever I select Cell B39 that it will copy cells B39:V39 ? From there I can manually paste that selection to where I need to.

If this is possible, it would save the effort of having to select B39:V39 manually and clicking CTRL-C. I am gathering data and the copying and pasting is killing me.

Question 2) Is it possible that when I click on Cell A9 that it can paste any data that I just copied from another worksheet to cells A9:D29? The size of the selection that I would copy is the same size as A9:D29 (4 columns & 29 rows).

I have searched for a solution, but I am not having much luck since I am anaware of the proper terminology for these types of actions.


View Replies!   View Related
Selecting Cell Range Of Colours
Is it possible to select only cells of a certain colour, then change these cells to a different colour? example

I want to make the dark grey columns a light green and the red columns a lets say yellow and the text black. Is this possible??

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
Selecting Only A Part Of A Merged Range
i have a merged cell which runs across a multiple columns (but a single row). i would like to insert a column in the middle of this merged cell, but whenever i click on the column header to insert a column, the entire merged range is selected.

is there a way to change the excel setting to avoid this?

View Replies!   View Related
Selecting Range With Variable Length
I require vba code which will allow me to select a range of variable size.

Starting from cell A2 use :

Range(Selection, Selection.End(xlDown)).Select

However then using the line below may not select the entire range as some columns may be empty

Range(Selection, Selection.End(xlToRight)).Select

View Replies!   View Related
Selecting Data Over A Date Range
I have 2 worksheets where I need to transfer a certain amount of data from one to another depending on dates.

Sheet A, is the main data source. Col A holds all 365 dates from 1 Jan to 31 Dec(starting at A2), and the subsequent data for each day is held in cols B to P.
Sheet B is the destination where the user will specify a key date in cell A3, then in A4 to A32, formulae populate the next 29 dates (ie so there is a continuous run of 30 days).

What I need to do, is to identify the same 30 day range from sheet B on sheet A, then to copy the data in Cols B to P for that range back onto sheet B (pasting in at D3)

View Replies!   View Related
Randomly Selecting A Cell From A Range
I need a cell to display the (text) content of 1 of 25 cells. example: I have text content in all cells ranging A1:A25. I want cell B1 to display the content of 1 of these at random. is there a function for this or do I need to use a macro?

View Replies!   View Related
Selecting The Whole Column As Range In Macro
I am working on a VBA macro, using the following

With wsSheet.Range("A:A")
ReDim MyArray(1 To .Rows.Count, 1 To .Columns.Count)
MyArray = wsSheet.Range("A:A")
For i= 1 To .Rows.Count
For j= 1 To .Columns.Count
wsSheet.Cells(i, j+ .Offset(0, 3).Column) = MyArray(i, j)
Next: Next

I want to select alla values in column A, but when I specify the range as "A:A" the code results in an infinite loop! How can I come around this?

View Replies!   View Related
Selecting A Group In A Moving Range
I have a spreadsheet where I need to perform a calculation on the lowest 10 of the 20 most recent entries. Currently I am just deleting the oldest entry and putting in the new data but I now desire to keep all my data.

How do I select the new range of 20 rows and then tell it to take the 10 lowest values to perform the calculation

View Replies!   View Related
Selecting Conditional Range To Copy
I have two columns of data (of varying length). The first column "A" has steadily increasing random values from about zero to 200. I would like to be able to automatically select/highlight the region with values between 50 and 100 as well the corresponding values in the adjacent "B" column.

View Replies!   View Related
Selecting A Range When Switching Between Workbooks
I select workbook 1, cell A4, then I select workbook 2, cell A4, but when I'm in workbook 2, it does not select cell A4. Am I doing something wrong here? See my code below.

Sub CopyData()
Dim i As Variant

Workbooks("1_2007 Forecast (Ancillary).xls").Worksheets(1).Activate
Cells.Range("a4").Activate

Workbooks("1_2007 Forecast (LGBU).xls").Worksheets(1).Activate

Cells.Range("a4").Activate
End Sub

View Replies!   View Related
Selecting A Variable Range And Then Subtotaling It
I am trying to enter code into a Macro that will select a range and then put a subtotal at the bottom of the range.

I have values in col D & E that I am multiplying together in column F. I then want the macro to select a range from F2 to the last Value in col F and then put a total in the next cell down (in col F). The range needs to be variable.

View Replies!   View Related
Selecting Range Based On Date
On sheet 1 in cell A2 I have a date (2/22/07). On sheet 2 I have 4 columns of data...column A has consecutive dates. I am looking for a code that will select the range that begins with the week beginning date of the date that was inputted on sheet 1 and the week ending date. For example, the code will select the range 2/19/07 to 2/25/07 (A20:D26).

View Replies!   View Related
Write Text Without Selecting Range
Range("A10").Select
ActiveCell.FormulaR1C1 = "write text"

How would the code be written so it does not actually select cell range A10? I have seen this somewhere where text is written but without actualy recording "clicking cell range".

View Replies!   View Related
Selecting A Variable Column Range
I have a workbook that generates sheets for each year based on selected criteria. It starts at Column H and goes too AH and beyond. When my loop reaches Z it errors out. I think this is happening because the code is referencing the column as ASCII. Here is the

Sub Test()
Dim d As Date
Dim yrint, i, num_years, fields, field_start As Integer
Dim yrstr, crit1, crit2, left_column_range_fixed, right_column_range_fixed, left_column_range_var, right_column_range_var, left_column_range, right_column_range, cost_column, cost_column_var, cost_column_fixed As String
left_column_range_fixed = "H"
right_column_range_fixed = ":AH"
cost_column_fixed = "2"
crit1 = "=x"
crit2 = ">0"
d = Date
yrint = Sheets("Overall"). Range("H2")
field_start = 9 'changed from 9....................

View Replies!   View Related
Automatically Formatted As Scientific Notation
I'm trying to filter a long list to look for specific employee numbers. The employee numbers are so long that they have to be formatted as text or they are automatically formatted as scientific notation (they are 18 digits long). For some reason this is causing the filter not to work properly.

Employee ID
006010000000000661
006015000000000082
006015000000000319
006015000000000319
006015000000000552
006010000000000370
006010000000000370
006015000000000921
006010000000000503
006015000000000835
006015000000000834
006015000000000689
006010000000000391
006010000000000391
006010000000000030
006010000000000030
006015000000000718
006015000000000202
006015000000000202
006015000000000202
006010000000000585
006015000000000712

View Replies!   View Related
Disable Scientific Notation - Permanently, How
AHH Scientific notation is annoying me, I don't want to see auction #'s in scientific notation. How can I disable the feature in excel 2007?

View Replies!   View Related
Data Converted To Scientific Notation
I work for a company that exports data from a website into Excel.

So here's my problem:

The data is usually Part Numbers using numbers and letters. When a Part Number is, for example, 248E12 we have this reaccurring issue. Importing it into Excel automatically converts it to scientific notation (ie. 248E+12 or 2.48E+14 to be exact). "Format Cell" doesn't convert it back because it was auto-converted when "exported". I need a way of fixing this problem without going line by line and doing the math to convert it myself.

I don't have time for that. Some of my files have thousands of lines.

View Replies!   View Related
Selecting Only Cells Of A Certain Colour (CF)
A macro that only selects cells which are coloured light green. I need this so that I can add it to an existing macro that I've written to remove conditional formatting from these cells only.


View Replies!   View Related
Selecting Visible Cells
someone the VBA code which only selects visible cells in a sheet.
i.e. if for example, range("a1:d5") contained data BUT

range("a6:d100") contained formulae but NO data

the macro would only highlight range("a1:d5")

However, when say range("a6:d10") contained data, then the macro would highlight range("a1:d10")

and so on.

I want the mcro to do this without me having to manually change the ranges when running the macro.

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