Excel 2000

Is it possible to use R1C1 notation in VBA? I'm using it in cell

formulae notation.

Range("A1").Select of course works.

However,

Range("R1C1").Select

bombs.

Thanks,

Fred Holmes

Is it possible to use R1C1 notation in VBA? I'm using it in cell

formulae notation.

Range("A1").Select of course works.

However,

Range("R1C1").Select

bombs.

Thanks,

Fred Holmes

- Using ranges in R1C1 notation in SUMIF formula
- Fixing cells in R1C1 formulas in VBA Query
- Stop Scientific Notation in VBA
- UserForm R1C1 problem
- Using R1C1 notation for Data Validation in VBA?
- R1C1 Formulas in VBA
- R1C1 notation
- Using R1C1 to represent a entire column in VBA formulaArray
- Combining R1C1 and absolute reference in SUMPRODUCT in VBA
- FormulaR1C1 returns R1C1 notation
- Summing in VBA
- Sum of variable range in VBA
- Cell references in VBA formulas
- Specifying Workbook names with blank spaces in VBA
- Absolute cell references with R1C1 notation
- Using a variable with R1C1 reference in vba
- Creating R1C1 formula in VBA that can return a text value
- Sum Columns:R1C1 notation
- Unique value count in VBA
- Selecting a range of cells with R1C1 notation
- R1C1 notation conversion
- Use Match or Lookup to create dynamic cell reference in VBA R1C1 formula
- Name a Range in VBA
- #name error in VBA function-

Right now, my code looks like this:

'other calculations c_shift = num_SKUs * 2 Dim entry_col As Integer For total_row = 6 To 99 For column_ = 1 To num_SKUs entry_col = (column_ * 2) + 13 cells(total_row, entry_col).formulaR1C1 = _ SUMIF(R6C13:R99C13, RC[- " & entry_col & "+1, R6C[-1]:R99C[-1]) Next column_ Cells(total_row, 14 + c_shift).FormulaR1C1 = _ "=SUMIF(RC[-" & c_shift & "], " & "Before" & ", RC[-1])" Cells(total_row, 15 + c_shift).FormulaR1C1 = _ "=SUMIF(RC[-" & c_shift & "], " & "After" & ", RC[-1])" Next total_rowOf course, it gives me a error in the bolded line, saying that there is a "Application-defined or object-defined error". I don't know the right way to specify a range using R1C1 notation (except for doing Range(Cell(), Cell()... but I don't think I can do that in a formula)

What should I do?

Thanks for all the help

I wonder if anyone could help me please as I am completely stumped!

I have a an R1C1 formula I have written in my macro and I copy the formula into several cells. However I would like to "fix" the column. Whereas in a normal excel formula I would just put the dollar sign in front of the column, how do I do the equivalent in VBA?

As an example, please see formula below:

Cells(3,4).FormulaR1C1 = "=R[1]C[-1]-""0:0:1"""Many thanks for your help.

I am trying to get VBA to stop changing a large(double) number into scientific notation. The reason I need this is because I need the exact number to do a web query and when VBA changes the number to scientific notation the query goes to an error page on the website. The number I have is 634703040000000000, however when I put this into a module in VBA it changes to 6.3470304E+17. Is there any way to stop this?

Thanks

The problem occurs when the macro saves the formulas to their designated cells. Each and every one of them comes up with the #NAME? error, even though the syntax of the formula in the Formula Bar is perfect. I can fix this by clicking into the Formula Bar as if I am going to edit the formula, and immediately pressing Enter. The formula does not change, but suddenly it works. What can I change in my code in order to get this to work?

Private Sub cmdOK_Click() With Worksheets("Data") FinalRow = .Cells(Rows.Count, "A").End(xlUp).Row .Range("A" & FinalRow + 1).Value = txtCharacterName.Value .Range("B" & FinalRow + 1).Value = txtInitiative.Value .Range("C" & FinalRow + 1).FormulaR1C1 = "=20-(RC[-1])" .Range("D" & FinalRow + 1).Value = txtFort.Value .Range("E" & FinalRow + 1).Value = txtReflex.Value .Range("F" & FinalRow + 1).Value = txtWill.Value .Range("G" & FinalRow + 1).Value = txtListen.Value .Range("H" & FinalRow + 1).Value = txtSearch.Value .Range("I" & FinalRow + 1).Value = txtSpot.Value .Range("J" & FinalRow + 1).Value = txtSTR.Value .Range("K" & FinalRow + 1).FormulaR1C1 = "=VLOOKUP(RC[-1],'Abiity Scores'!A:B,2,FALSE)" .Range("L" & FinalRow + 1).Value = txtDEX.Value .Range("M" & FinalRow + 1).FormulaR1C1 = "=VLOOKUP(RC[-1],'Abiity Scores'!A:B,2,FALSE)" .Range("N" & FinalRow + 1).Value = txtCON.Value .Range("O" & FinalRow + 1).FormulaR1C1 = "=VLOOKUP(RC[-1],'Abiity Scores'!A:B,2,FALSE)" .Range("P" & FinalRow + 1).Value = txtINT.Value .Range("Q" & FinalRow + 1).FormulaR1C1 = "=VLOOKUP(RC[-1],'Abiity Scores'!A:B,2,FALSE)" .Range("R" & FinalRow + 1).Value = txtWIS.Value .Range("S" & FinalRow + 1).FormulaR1C1 = "=VLOOKUP(RC[-1],'Abiity Scores'!A:B,2,FALSE)" .Range("T" & FinalRow + 1).Value = txtCHA.Value .Range("U" & FinalRow + 1).FormulaR1C1 = "=VLOOKUP(RC[-1],'Abiity Scores'!A:B,2,FALSE)" .Range("V" & FinalRow + 1).FormulaR1C1 = "=RC[-9]+RC[3]+RC[9]+RC[15]+RC[21]+RC[27]+RC[33]+RC[39]+RC[45]+RC[51]+RC[57]+RC[59]+RC[60]" .Range("W" & FinalRow + 1).FormulaR1C1 = "=RC[-9]+RC[38]+RC[44]+RC[50]+RC[56]+RC[58]+RC[59]" .Range("X" & FinalRow + 1).FormulaR1C1 = "=RC[-2]-RC[-11]" .Range("Y" & FinalRow + 1).FormulaR1C1 = "=MAX(RC[1]:RC[5])" .Range("Z" & FinalRow + 1).Value = txtArmor.Value .Range("AE" & FinalRow + 1).FormulaR1C1 = "=MAX(RC[1]:RC[5])" .Range("AF" & FinalRow + 1).Value = txtArmorEnhance.Value .Range("AK" & FinalRow + 1).FormulaR1C1 = "=MAX(RC[1]:RC[5])" .Range("AL" & FinalRow + 1).Value = txtShield.Value .Range("AQ" & FinalRow + 1).FormulaR1C1 = "=MAX(RC[1]:RC[5])" .Range("AR" & FinalRow + 1).Value = txtShieldEnhance.Value .Range("AW" & FinalRow + 1).FormulaR1C1 = "=MAX(RC[1]:RC[5])" .Range("AX" & FinalRow + 1).Value = txtNatural.Value .Range("BC" & FinalRow + 1).FormulaR1C1 = "=MAX(RC[1]:RC[5])" .Range("BD" & FinalRow + 1).Value = txtNaturalEnhance.Value .Range("BI" & FinalRow + 1).FormulaR1C1 = "=MAX(RC[1]:RC[5])" .Range("BJ" & FinalRow + 1).Value = txtDeflection.Value .Range("BO" & FinalRow + 1).FormulaR1C1 = "=SUM(RC[1]:RC[5])" .Range("BP" & FinalRow + 1).Value = txtDodge.Value .Range("BU" & FinalRow + 1).FormulaR1C1 = "=IF(ISBLANK(RC[3])=FALSE,RC[4],RC[2])" .Range("BV" & FinalRow + 1).Value = cboSize.Value .Range("BW" & FinalRow + 1).FormulaR1C1 = "=VLOOKUP(RC[-1],Tables!A2:C10,2,FALSE)" .Range("BZ" & FinalRow + 1).Value = txtPrestige1Name.Value .Range("CA" & FinalRow + 1).Value = txtPrestige1.Value .Range("CB" & FinalRow + 1).Value = txtPrestige2Name.Value .Range("CC" & FinalRow + 1).Value = txtPrestige2.Value If chkWisdom = True Then .Range("CD" & FinalRow + 1).FormulaR1C1 = "=RC[-63]" .Range("CE" & FinalRow + 1).Value = cboType.Value .Range("CF" & FinalRow + 1).FormulaR1C1 = "=VLOOKUP(RC[-1],Tables!E2:F4,2,FALSE)" .Cells.Sort Key1:=Range("CF2"), Order1:=xlAscending, Key2:=Range("A2") _ , Order2:=xlAscending, Header:=xlYes End With Unload Me End SubAlso, I am confused as to why the sort command at the very bottom gives me an error as well.

Thanks,

Scott

VB:My problem is that I can't get it to work for all the rows when it runs in the macro, because I don't think I can use R1C1 notation with data validation? I have been searching around for a while and can't find anything. This is what my code currently says...my issue is with that $H$6 term, I want it instead to be something like RC[-3] (which would refer to that row's column H), but it gives me an error when I do that.=INDIRECT("nr_"&SUBSTITUTE(H6," ","_"))If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

VB:Any ideas?kRng.Item(hx).Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, FormulaR1C1:="=INDIRECT(""nr_""&SUBSTITUTE($H$6,"" "",""_""))" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End WithIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I want VBA macro in Excel to write a formula in R1C1 in a specific cell, e.g.

VB:However, for specifying cell location I would like to use variables defined previously in the macro. The example above refers to a cell 15 rows above and in the same column as the active cell (R[-15]C). Say I want to let the location depend on a variable 'counter'=-15. I would like the formula to look like this:ActiveCell.FormulaR1C1 = "=IF(R[-15]C="""",0,1)"If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

VB:In this format however it does not work (it seems to require a number in the []). Enyone knows how to fix this?ActiveCell.FormulaR1C1 = "=IF(R[counter]C="""",0,1)"If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Many thanks for help!

Jiri

I have just started to learn VBA and am a little confused by the code of the macro that I have just recorded. I just wrote the formula : =left(A1,3) - to return the first three letters of cell A1. The code that came up was :

VB:The second row of the code : "=LEFT(RC[-1],3)" .Range("B1").Select ActiveCell.FormulaR1C1 = [color=blue] "=LEFT(RC[-1],3)" [/color] Range("B2").Select End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Is this R1C1 notation. I didnt expect the code to look like this.

Thanks a lot guys.

marcus.

I'm trying to figure out how to use R1C1 formula style to represent the entire column for a calculation.

How do I turn the following array cell formula into VBA with R1C1?

=MEDIAN(IF((A!A:A=Test!B2)*(A!C:C="Yes"),A!B:B))I guess in VBA, it should be something like...

Worksheets("test").Range("F2").FormulaArray = _ "=MEDIAN()"Thanks

that multiples an array of cells which I would like to reference in relative

R1C1 terms by an array of cells that I would like to reference in absolute

terms on another sheet. The first part of my formula is fine, but I want the

cells from the DI_NAM sheet to always refer to rows 5 to 9. The column must

be the same as the first array. If there is a way I could put a variable

into the reference, that would work, but I can't figure out how to do this.

I need to replicate this formula as part of a block of data that gets created

in a do loop.

Selection.FormulaR1C1 = "=SUMPRODUCT(R[-7]C:R[-3]C,DI_NAM!R[-17]C:R[-13]C)"

This formula gets input across 36 columns as I am working with 36 months of

data.

I am a novice at VBA, so if anyone knows how to do this, I would really

appreciate the help!

Once the formula is input a large range is filled for the formulas

Here is the code

VB:and Here is what it returns incorrectlyRange("A1").Select Range("I2").Select 'Quantity ActiveCell.FormulaR1C1 = "=RC[-1]" Selection.NumberFormat = "0" Range("I3").Select ActiveCell.FormulaR1C1 = "=R[-1]C[-1]*(-1)" Range("I3").Select Selection.NumberFormat = "#,##0" Range("I4").Select ActiveCell.FormulaR1C1 = "=R[-2]C[-4]*(-1)" Range("I4").Select Selection.NumberFormat = "General"If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Last column has the formulas(though I cant' get to to view correctly)

VB:here it is when it is correct6,600.00 330.00 414.12 12,000 =RC[-1] =R[-1]C[-1]*(-1) =R[-2]C[-4]*(-1)If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

VB:Thanks Again,6,600.00 330.00 414.12 12,000 12000 -12,000 -6600If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

RoBtS

Sheets(1).Cells(4, 100).Select

ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"

But I would like to sum the sheets like this:

Sheets(1).Cells(5, 100) = sum(Sheets(1).Cells(5, 96), Sheets(1).Cells(5, 99))

But I get an error message saying: "wrong number of arguements or invalid property assignments."

how can I use the sum function in VBA with a simialr notation?

Thank you in advance,

Eddie

I have defined coli as the count of columns in the range I need to sum. This range will change over time.

I have populated the columns with a formula, and I want the rightmost column to contain a sum.

I tried doing something as simple as trying to use coli as the # of columns to move in R1C1 notation, but that doesn't work.

Any assistance is greatly appreciated.

VB:rngTotals .Clear .FormulaR1C1 = "=SUM(RC[-coli]:RC[-1])" rngTotals = rngTotals.Value .Style = "Currency" End WithIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I'm frustrated.

I get that there are many ways to reference cell addresses(A1, RC, offset from an active cell, etc.). But when to use which

Putting one of these into a VBA formula I don't get. I'm almost ebarassed to even broach the subject to such knowledgable people

Okay, after looking through an awful lot of posts, what does the "!" mean in the following: !R1C1:R44C5 Is it an absolute vs relative indicator?

I'm working on a SUM(range) formula but although Excel macro words can be used in VBA, I can't find an explaination in VBA Help. Its index stops at "Submit". The VBA help lists it as a worksheet function but not how to use in a VBA module.

I NEED TO READ - Please point me in some direction (online hopefully).

I note the "Cells" property and it has the column, a comma, and the row. That would seem ideal to use (a variable for the row))

This is what I'm attempting in a very crude manner

Qty=sum(cell($c, $row) to (cell($c, row variable)))

I've dim'd the variables. Running it, the "sum" is highlighted and the error is "Sub or Fuction not defined"

While I'd like the answer, I'd also like to be pointed to a source for putting things together.

Hope my rambling has at least lead to a and that I have not taken up too much forum space and your time. Please don't kick me out. I'm trying! At 58 some of the brain functions may be gone.

John

With Range("['Country Specific Parameters'.xls]Demographic! K6")

.FormulaR1C1 = "=VLOOKUP(R6C11,[MasterTable.xls]Sheet1! R1C1:R300C10,10,FALSE)"

.Value = .Value

End With

What I need this part of my code to do is in essence =LOOKUP(RC[-1],$A$1:$AA$1)... however I know I cannot mix R1C1 and A1 notations. The LOOKUP is cross referencing a date versus another table to find what policy term the date falls under. Here is the code I have been able to figure out that will at least work for the first cell.

Code:

However, I need to autofill the formula down. When I do that, my reference range goes completely out of whack. Is it possible to keep the reference range in-tact while autofilling?

Here is my entire code.

Code:Sub PolicyFill() Dim LR As Integer LR = Cells(Rows.Count, ActiveCell.Offset(0, -1).column).End(xlUp).row ActiveCell.FormulaR1C1 = "=LOOKUP(RC[-1],R[" & (-1) * (ActiveCell.row - 1) & "]C[" & (-1) * (ActiveCell.column - 1) & "]:R[" & (-1) * (ActiveCell.row - 1) & "]C[" & (-1) * (ActiveCell.column - 1) + 26 & "])" ActiveCell.Offset(0, 1).FormulaR1C1 = "=YEAR(RC[-1]) & "" - "" & YEAR(RC[-1])+1" ActiveCell.AutoFill Destination:=Range(ActiveCell, Cells(LR, ActiveCell.column)) ActiveCell.Offset(0, 1).Select ActiveCell.AutoFill Destination:=Range(ActiveCell, Cells(LR, ActiveCell.column)) End Sub

ActiveCell.FormulaR1C1 = "=MIN(R" & intRightR & "C" & intRightC & ":R" & intBottomR & "C" & intBottomC & ")"

and depending on an evaluation of two numbers, the variables will change and the loop will start over. The only problem is that this line of code is not working. I saw Derk's post of trying something similar to:

Range("A1").formulaR1C1="=SUM(R" & row1 & "C" & col1 & ":R" & row2 & "C" & col2 & ")"

Could anyone help me out with this. Thanks in advance!

Thank you,

Tony R

the bottom of columns C through E. I want to put the sum there, not a

formula. I thought some kind of R1C1 notation would be necessary, but

I got this method from help. The notation seems odd but it works well.

Is this the best way to do it?

TIA, James

Sub SumCols()

Dim k As Integer, LastRow As Long, myRg As Range

For k = 3 To 5

LastRow = Cells(65536, k).End(xlUp).Row

Set myRg = Range(Cells(2, k), Cells(LastRow, k))

Cells(LastRow + 1, k) = Application.WorksheetFunction.Sum(myRg)

Next k

End Sub

I have this formula that works perfectly fine for what I am trying to do (from another post) but I am wanting to transfer this into VBA. It is an array formula so I'm not sure if this can be done.

How can I convert this, into R1C1 code or any way possible. I would put it within a WITH RANGE statement.

Here is the formula:

And here is what I have so far in VBA:It seems like the column B reference is not changing and is staying at B4.

With Range("F4:F50") .FormulaArray = "=COUNT(1/FREQUENCY(IF('NZ Container Stock'!R2C7:R100C7=RC[-4],'NZ Container Stock'!R2C1:R100C1),'NZ Container Stock'!R2C1:R100C1))" End With

Any help is appreciated.

Cheers

Benno

Thanks.

=IF(A2="",1,0)

However, I can't seem to express this in my macro in R1C1. The logic behind the test is if A2 is blank report a 1, if not then report a zero. Any help would be greatly appreciated.

I have a fairly simple nested IF function judging a couple of ISBLANKs as well as some LEN and RIGHT. The problem is that I'd like to put it in VBA. Can do that easily. Here's the catch: I'd like to make the column references dynamic in case the columns get re-arranged. So if "Title 1" gets moved to column C, I'd like the formula to use column C. Does that make sense? Here's the formula in a regular cell.

=IF(ISBLANK(A3),"",IF(ISBLANK(C3),IF(LEN(A3)=13,RIGHT(A3,11),A3),IF(LEN(A3)=13,RIGHT(A3,11)&"-"&C3,A3&"-"&C3)))

The code is easy, but making it so the column can shift is a bit harder. I was trying to include Match("Title 1",$A$1:$Z$1,0) inside, but that doesn't return letters and it gets too complicated for the macro to record.

Any help?

Thank you so much. I appreciate the help.

I would like to use Excel recorder to name a range in VBA.

The table may have different number of rows every day in a file. It can start at Row 13 one day, maybe starts as Row 17 the other day.

Table column starts with "Age Bucket" in Column A.

Using recorder, it has to define a fixed address which may be true for one day, but wrong for next day.

In this case, I was forced to set the range and name it as "DATA"

"=Sheet1!R13C1:R18C8"

Is it possible to select the data in table after column header "Age Bucket" and name it as "Data" in VBA?

Thanks for help.

Sub Macro1()

Application.Goto Reference:="R1C1"

Cells.Find(What:="Age Bucket", After:=ActiveCell, LookIn:=xlFormulas, _

LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

MatchCase:=False, MatchByte:=False, SearchFormat:=False).Activate

ActiveCell.Range("A1:A28").Select

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

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

ActiveWorkbook.Names.Add Name:="Data", RefersToR1C1:= _

"=Sheet1!R13C1:R18C8"

ActiveWorkbook.Names("Data").Comment = ""

End Sub

I have several functions I think i may have a problem with most of them. but they are written in such a way that

they depend on each other so that if the first returns an error the rest wont show anything.

The first is for calculating correction factor fc taken from Serth see code. I am stuck, see code.

VB:Any body with any idea.'Correction Factor calculations for the LMTD 'source Robert R Serth, heat Transfer principles and applications. 'For any number of shell side passes and any even number of tube side passes as follows 'LMTD is calculated as ' N is the nymber of shell side passes Function Calcfc(Tis As Double, Tos As Double, Tit As Double, Tot As Double, N1 As Double, N2 As Double) As Double 'Correction Factor calculations for the LMTD 'source Robert R Serth, heat Transfer principles and applications. 'For any number of shell side passes and any even number of tube side passes as follows ' N is the nymber of shell side passes 'R P and alpha are ratios used to calculate the correction factor Dim R As Double, P As Double, S As Double, alpha As Double, n As Double Dim num As Double, den As Double, F As Double 'the numerators and denominators of the functions to ease writing the functions 'Where Ta (Tis)= inlet temperature of the shell-side fluid 'Tb (Tos) = outlet temperature of the shell-fluid 'ta (Tit= inlet temperature of the tube side fluid 'tb(Tot) = oulet temperature of the tube side fluid R = (Tis - Tos) / (Tot - Tit) P = (Tot - Tit) / (Tis - Tit) n = N1 * N2 alpha = ((1 - R * P) / (1 - P)) ^ (1 / n) If R = 1 Then S = P / (n - (n - 1) * P) num = (S * 2 ^ 0.5) den = (1 - S) * WorksheetFunction.Ln((2 - S * (2 - 2 ^ 0.5)) / (2 - S * (2 + 2 ^ 0.5))) F = num / den Else alpha = ((1 - R * P) / (1 - P)) ^ (1 / n) S = (alpha - 1) / (alpha - R) num = ((R ^ 2 + 1) ^ 0.5) * WorksheetFunction.Ln((1 - S) / (1 - R * S)) den = (R - 1) * WorksheetFunction.Ln((2 - S * (R + 1 - (R ^ 2 + 1) ^ (1 / 2))) / (2 - S * (R + 1 + (R ^ 2 + 1) ^ (1 / 2)))) F = num / den End If Calcfc = F End FunctionIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks