Selecting A Range Of Cells With R1C1 Notation

Oct 31, 2008

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.



ADVERTISEMENT

Referring To Range() Using Shortcut Notation

Sep 23, 2008

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 9 Replies View Related

Notation Of Formula With Range (small Question)

Jul 28, 2009

Ok, I want to use a dynamic range in my Vlookup formula.

I want to use this formula: ....

View 11 Replies View Related

Referencing Sheet Using Cell Notation In Range

Aug 12, 2012

repTot = Application.WorksheetFunction.Sum(Worksheets("Revenue").Range(Cells(xx, 65), Cells(xx, 65 + moNum)))

I am trying to debug the above line of code (moNum is just a variable the tells me how many months to extend the range to the right inorder to get year to date sums).

I have gotten to the point that it will run error free if "Revenue" is the Active worksheet when I run the line, but if I am on any other sheet it throws an "Run=time '1004' Application-defined or object-defined error" error. And if I change the range from cell notation to Column-Row, the issue goes away as well.

View 3 Replies View Related

Can I Sum A Range Using R1C1 Format

Apr 15, 2008

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 9 Replies View Related

Selecting Range Of Cells

Jan 31, 2009

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 5 Replies View Related

Creating Border For A Range Using R1C1

Mar 6, 2013

I'm trying to create a border for a range using R1C1 and get an error.

VB: Sheet11.Range(Cells(y, 1), Cells(y, 5)).BorderAround Weight:=xlThin

If I do not use R1C1 it works...

VB: Sheet11.Range("A18:E18").BorderAround Weight:=xlThin

View 2 Replies View Related

Selecting Non Sequenced Cells In A Range

Mar 1, 2010

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 9 Replies View Related

Ctrl-A Selecting Limited Range Of Cells

Apr 8, 2009

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 3 Replies View Related

Selecting A Range Of Cells Relative To The Current Cell

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

Excel 2007 :: Automatically Selecting Cells - Highlight As Range

Jul 29, 2008

I'm new to Excel 2007... whilst I'm managing to find most things I'm used to using in 2003, I have an issue in one workbook that I can't figure out.

When I click in some single cells (not all), that cell and several below it automatically highlight as a range... left-clicking initially highlights the cell, but as you release the mouse button, the others below it also highlight.

View 14 Replies View Related

Updating Named Range When New Contact Is Added Using R1C1 Format With A Variable

Feb 1, 2010

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 2 Replies View Related

Copy/Paste Range(s) Without Activating/selecting Range(s)

Oct 2, 2008

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 5 Replies View Related

Subscript Out Of Range When Selecting Range In Closed Worksheet

Apr 28, 2006

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 8 Replies View Related

Selecting A Range Based On Date Within That Range

Sep 26, 2007

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 9 Replies View Related

RC Notation

Jun 5, 2009

I dont know why this wont work
it falls over on the ".Columns(6).FormulaR1C1=" line

View 14 Replies View Related

Sigma Notation Using VBA?

Mar 21, 2014

Similar to this thread: [URL]

I'm trying to come up with a VBA solution to.... Sigma from j=0 to i of: L((i-j)/i)*b*t.

View 7 Replies View Related

Convert Formula To RC Notation

Feb 22, 2008

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 9 Replies View Related

Meaning Of Bracket Notation

Feb 9, 2007

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 4 Replies View Related

Square Brackets - Vba Notation

Mar 22, 2007

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 9 Replies View Related

Selecting From One Set Range To Another

Jul 11, 2014

So in my code I have two set ranges and want to select from one set range to another. The code I have now does not work because I cannot select Range("POBRF:PO"). This is the code.

[CODE]
ARow = Range("A" & Rows.Count).End(xlUp).Row
Set POBRF = Range("A1:A" & ARow)
BRow = Range("I" & Rows.Count).End(xlUp).Row
Set PO = Range("I1:I" & BRow)
Range("POBRF:PO").Select
[CODE/]

View 1 Replies View Related

Selecting A Range Using VBA

May 28, 2009

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 9 Replies View Related

Selecting A Certain Range That Changes

Jan 17, 2007

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 2 Replies View Related

Data Converted To Scientific Notation

Nov 3, 2008

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 14 Replies View Related

Automatically Formatted As Scientific Notation

Dec 27, 2006

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 10 Replies View Related

Disable Scientific Notation - Permanently, How

Mar 30, 2007

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 9 Replies View Related

Selecting A Range And Then Summing That Range

Jul 19, 2006

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 4 Replies View Related

Selecting Specific Value Within A Range

Nov 12, 2008

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 6 Replies View Related

Selecting Range Up To Blank Row

Feb 8, 2009

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 2 Replies View Related

Selecting Range With Variable Row?

Apr 4, 2013

I want to copy a range where the row is variable (i in the Loop I'm doing) but the columns stay the same. What am I doing wrong?

Workbooks("Labor Info").Sheets("Transfer").Range("A" & i & ":E" & i).Select

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved