How To Use Variable In R1C1 Statement

May 29, 2013

I need to know how to use a variable in an R1C1 statement. The statement is as follows:

Set rng = Sheet1.Range("A2:QFinalRowII").CurrentRegion

Ideally the statement would read something like,

Set rng = Sheet1.Range("A2:Q10").CurrentRegion

How would I use a variable name like "FinalRowII" instead of a number?

View 8 Replies


ADVERTISEMENT

Formula R1C1 With Variable

Nov 8, 2011

It's probably O so simple but: Using a variable in R1C1, here's the attempt:

-----------------------------
Dim RS_Period As Double
-----------------------------
RS_Period = Sheets("Sheet1").Range("B17") - 1
-----------------------------
Range("E2:E" & LastRow).FormulaR1C1 = "=(RC[-2]-OFFSET(RC[-2],9,0))/OFFSET(RC[-2],9,0)"
-----------------------------

I lieu of the "9" I need to use the "RS_Period" which is an offset value.

View 4 Replies View Related

Replace Constant Value With Variable In R1C1 VBA

Mar 15, 2013

My current dataset goes to row 256, when I use the Macro Recorder it produces the following "static" code.

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"claim_export!R1C1:R256C23", Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:="Sheet5!R3C1", TableName:="PivotTable1" _
, DefaultVersion:=xlPivotTableVersion12

QUESTION/PROBLEM:

Each month the amount of rows could be different (columns should be the same)... I have tried (3) different ways to replace the 256 with my variable name called "numbers"

SourceData:="claim_export!R1C1:R " & numbers & "C23" OR
SourceData:="claim_export!R1C1:R[" & numbers & "]C[23]" OR
SourceData:="claim_export!R1C1:R[" & numbers & "]C23

They all produce the same resulting error:

Run-time error '5': Invalid procedure call or argument.

Note* I am sure my variable is working, because when I "step into" (F8) my code and hover over my variable I can see it showing the number I expect.

View 1 Replies View Related

Variable Code In R1c1 Formula

May 19, 2007

I am trying to figure out how to use a variable as a row number for use in a range name and/or a pivot table range. Right now I have a range of R571C17, but the row number will change with each use of the pivot table formation macro.

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

Declaring Variable In Sub Statement?

Nov 23, 2011

I am trying to make sense of quite an elaborate macro built by someone else. This has the following line of code

Code:

Public Sub readOperation(rowNumber As Integer)
Dim rowOff As Integer
rowOff = rowNumber - 1
'other arguments and conditions
End Sub

What I am buggered about is that I cannot see the the variable rowNumber being declared anywhere but it seems to have a value of 1. I should mention that Operation is a class and I am very new to classes.

View 8 Replies View Related

Object Name As A Variable In A With Statement

Jan 16, 2007

Does the object in a 'With' have to be an object or can it be a variable containing the name of an object.

I want to carry out the same series of settings on a number of objects
and propose to define a Sub and pass the name of the objects as a parameter.
I then want to use a 'With' to set that object.
i.e. as shown below - though I cant see how it would work.

' combobox objects called Staff, Maker, etc

Call Setuplist("Staff")
Call Setuplist("Maker")
...

Private Sub Setuplist(whatlist As String)
'some complex filtering and sorting goes here
...
Set rnData = ActiveSheet.Range(whatlist+"List")
vaData = rnData.Value
With Me+whatlist ' this is the bit I cant fathom
.
.
End With
End Sub

View 9 Replies View Related

Evaluate A Count Statement With A Variable?

Jul 14, 2013

I am trying to perform a count statement in VBA as below, with the 1st Evaluate I get the correct response, however with the second option it shows a 0. The variable is obtaining the correct values as per the example of 6017Complete, this is proven with the MsgBox

VB:
Dim TillNo As String
TillNo = Target.Offset(, -1) & Target.Offset(, 0) & "Complete"
MsgBox "Till Number is" & TillNo
Target.Offset(, 10) = Evaluate("COUNTIF(C3:C123,""6017Complete"")")
Target.Offset(, 11) = Evaluate("COUNTIF(C3:C123,TillNo)")

View 6 Replies View Related

Insert Variable From Sheet Cell Into SQL Statement In VBA Code

Oct 29, 2013

I have the following code to execute a connection to a DB and return the result of the SQL statement. I have a variable in the statement that I want to be able to change from user input in a single cell. Unfortunately I am unable to get this to work.

Here is my code

cell_value = Sheets("some sheet").Range("P8")
Application.ScreenUpdating = False
Sheets("some sheet").Cells.Clear

Set rs = New ADODB.Recordset

[Code] ........

This fails and gives me a debug error, however if I declare the condition I want the code executes fine. This would mean having to update the code every time the condition changed or putting the whole query in a cell on the sheet which I do not want to do.

View 1 Replies View Related

Use R1c1 In Formula

Mar 24, 2007

=IF(CEILING(B9/0.5,1)>40,MAX(CEILING(B9/0.55,1),40),MAX(CEILING(B9/0.5,1),36))-0.01

Edit B9 to the R1C1 style of cell reference, how to do that?

View 3 Replies View Related

R1C1 Reference Style

Dec 30, 2006

I have tried to reset the cell reference style back to A1 from R1C1 (Tools-->Options-->General tab-->uncheck R1C1 reference style), but when I close Excel and reopen it, the reference style changes back to R1C1 style. When I make the change I described above using the Options feature, it changes it for the current file and saves it with the file. When creating a new file in the same session, it keeps the A1 reference style, but when I close Excel and reopen it, it returns to the R1C1 style. Any ideas about how to return to the default A1 reference style for the next Excel session?

View 9 Replies View Related

Script Error Using R1C1

Apr 30, 2009

I get an error at the line in red, I'm sure I messed up the R1C1 Part. The first formula will be put in HZ5:HZ100 Those cells need to reference another sheet in the workbook call Varsity A3:A98. The Second formula will be IA5:IA100 Will ref Varsity B3:B98

So whenever someone enters a name in the varsity sheet the name will appear in the other sheet. This is a master workbook That will be copied many times that is why I am putting the formula in after I create the copy because the links would look at the original Varsity sheet otherwise then the user get the update links messages.

View 5 Replies View Related

Adding A Formula With R1C1

Nov 29, 2009

I am trying to insert a formula into a VBA Code but I get an error msg.

The formula is;

View 5 Replies View Related

R1C1 Formula/Loop

Nov 1, 2007

I want to subtract one date from another to find out the total waiting time, and repeat this action for multiple rows.

I'm trying to use an R1C1 formula within all of this.

Dim TotalTimes As Long
Dim iTime As Integer

TotalTimes = Cells(Rows.Count, 4).End(xlUp).Row

For iTime = 1 To TotalTimes

Cells.Find("Waiting Time").Offset(iTime, 0).FormulaR1C1 = [R[iTime]C[-1]-R[iTime]C[-2]]

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

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

Set Source Data For Chart Using R1C1

Mar 9, 2014

Everything else in my macro is using R1C1 format for ease of automation. However, I cannot get this to resolve. I am setting the source data for a line chart.

However, if I use A1 format it works just fine.

I am using R1C1 for automation purposes. A1 format will not easily work. I have the interpolated statement in there because the range contains some empty cells. I want excel to fill the line in the chart using interpolation. All the data in the columns(N through R or 14 through 18 in R1C1 format) is contiguous. I have not tried Union, but it seems I should just use this range like I do in the A1 format.

View 6 Replies View Related

Zeros As Text In VBA R1C1 Formula

Dec 31, 2009

I am writing a macro that populates some columns from other worksheets, and populates other columns with formulas. One of these formulas is a text string that includes a three digit number, with leading zeros if needed. The following formula works perfectly when typed directly into the cells:

=IF(AND(RC[15]<>"",RC[4]<>""),CONCATENATE(RC[14],"-",IF(RC[15]<10,"00",IF(10<RC[15]<100,"0",)),RC[15]),IF(AND(RC[14]="",RC[4]<>""),R[-1]C,""))

But this formula triggers a error message "Compile error: Expected: end of statement" when inserted in my sub. The "00" is highlited when the error message appears.

View 4 Replies View Related

R1C1 Reference Style Won't Stay Gone

Dec 12, 2011

Every time I copy and paste cells in a specific workbook it changes the reference style to R1C1. Setting it back in the options, saving the document, closing Excel then reopening brings it back as A1 referencing but copy and paste will again return it to R1C1.

I've checked for VB code and there's nothing there either.

View 1 Replies View Related

Can Use Wildcard For R1C1 (lookup Value) In VLookup

Aug 21, 2013

know if I can use a wildcard on R1C1 notation for my "lookup value" in my Vlookup, so that the Vlookup searches for a not exact match, but on the whole contents of the cell, not just the first several letters? I am using VBA, and the vlookup is in each cell of a for next loop, so I cannot use an actual cell reference which is where I've usually seen, and used wildcards. I've tried changing the Vlookup to have the lookup value be "Cells(n,2)" instead of "RC[-2]" to no avail. I need to make the vlookup evaluate all the words in cell "RC[-2]" prior to returning the value I specified, because otherwise it returns the wrong value even though I sorted my lookup table Column A A to Z.

Start Date
End Date
Event ID
"Trans-Pacific Melodies" : an East-meets-West concert presented by Carolina International Orchestra and the China National Orchestra
10/06/13
10/06/13
22297

[code]....

View 1 Replies View Related

Workbooks Default To R1C1 Reference

Jul 4, 2007

Currently, I have switched my referencing style back to the default mode; however, every time I restart Excel, it switches me back to the R1C1 Reference Style. Is there a method to prevent this, instead of unchecking the option every time?

View 8 Replies View Related

Conditional Formatting With R1C1 References

Aug 19, 2007

Does anyone know if it's possible to use R1C1 style references in conditional formatting formulas?

Eg., =R[-1]C > 1

I tried the above and I keep getting an error.

View 9 Replies View Related

Add R1C1 Formula With Code & Variables

Sep 4, 2007

I am using follwoing vb code to enter formula to sum a range. However it is giving sum of different range.

Cells(Row, Column).FormulaR1C1 = "=SUM(R[" & a & "]C[" & b & "]:R[" & x & "]C[" & y & "])"

Where a, b , x and y are variables containg starting row, starting column, last row and last column value like a=19; b=3; x=24 and y=3. When i check the formula in that cell, it appears as Sum(F25:F30)

View 3 Replies View Related

R1C1 Reference To Start At Zero (R0C0)

May 29, 2008

I need the column numbers to start with 0 instead of 1, I'm using the R1C1 Reference Style. In other words, I want the first cell to be "R1C0" (or "R0C0") not "R1C1".

View 3 Replies View Related

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.

View 2 Replies View Related

Excel VBA - Copying Cell With Formula R1C1

Apr 13, 2012

I receive an run-time error message 1004 "autofill method of range class failed" in the following setting, in the 2nd line.

In this instance, I want to copy 1 cell from the cell 2 columns before.

Code:
Range("M3").FormulaR1C1 = "=RC[-2]"
Range("M3").AutoFill Destination:=Range("M3"), Type:=xlFillDefault

View 6 Replies View Related

Formula Shows In Cell As R1C1 Reference

Jun 20, 2008

When I first started using excell I was relatively good at it, however a few changes were made. The main one being for excell 2002 was the collumns were not by letter. A minor problem that I managed to get past. However the next was a function issue. Before I remembered a way to total rows with a simple function like =b3*c3, which worked for the first one. Then, from that point, I could copy that formula and paste it to all of the cells in the collum in which the forumula changed for individual cells to =b4*c4, =b5*c5, etc.

My questions are simple. Is there a way that I can do this on Excell 2002 without having to do it on a cell by cell basis, and is there a way to change collumns back to letters.

I've attached the spreadsheet in which I want to figure this out on. The goal, is to total price and quantities sold into the totals section without doing it on a cell by cell basis.

View 5 Replies View Related

Does A Keyboard Shortcut Exist To Toggle The R1C1 Function

Oct 4, 2005

Does a keyboard shortcut exist to toggle the R1C1 function?

View 4 Replies View Related

Insert Relative R1C1 SUM Formula Into Cell Via Macro Code

Apr 24, 2008

Hello I'm looking for some clarification about how to use "= SUM(RC:RC)". I don't understand what the RC:RC represents. I thought that this was the directions for where to "position" the formula, but I think I'm missing something.

I was able to include the correct cells that I need summarized, but the answer is not right; it's zero.

Public Sub SubColumn(CritStr As String) ...

View 8 Replies View Related

VBA Max Statement For A Range Where "from" Is A Variable

Jan 25, 2010

I need to calculate the max over a range of figures in a macro.

The "from" is a variable, call it X
The "to" is my counter in a loop so (Cells(-(Counter - 1), 5)

What I'm trying to do is to tell Excel that I want the max from X to the current counter cell.

e.g. Application.Max(Application.Index(X, 1):Cells(-(Counter - 1), 5))

but this give me an error.

View 9 Replies View Related







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