I am looping through a set of data to find two specific points. I save the row# as integer variables x and z. I then want to calculate the standard deviation of a column if values begining with row x and ending with row z. I tried using the FormulaR1C1 as:

Range("J4").Select

ActiveCell.FormulaR1C1 = "=STDEV(R[x]C[36]:R[z]C[36])"

Where I am storing the result from the calculation in cell J4.

What am I doing wrong?

Thanks

Range("J4").Select

ActiveCell.FormulaR1C1 = "=STDEV(R[x]C[36]:R[z]C[36])"

Where I am storing the result from the calculation in cell J4.

What am I doing wrong?

Thanks

- Problems using FormulaR1C1
- Using .formular1c1 with the Range/Cells Method
- Using FormulaR1C1 conditionally, allowing for null values
- FormulaArray & FormulaR1C1
- Avoid harcoding numbers using FormulaR1C1
- FormulaR1C1 addressing problem
- Using FormulaR1C1 to input complex formula into cell
- Using FormulaR1C1 to find fields begining with spaces
- Linking to Files in other Folders using Excel VBA
- Lookup Data Where Column Offset Is Dynamic
- Add formula by vba
- No anchor when using FormulaR1C1
- Insert Relative & Nested Function Via Macro Code
- Automatically Change Formula Column Reference
- How can I phrase this formula for the correct results?
- FomulaR1C1 Question.
- Date Functions (VB/EXCEL)
- Vba vlookup & named range
- Problems using FormulaR1C1
- How to clear links to external workbook
- Any Help with a Simple Function?
- VBA ActiveCell.Formula issue
- Formula R1C1
- Syntax Formula help

because extra single quotes are appearing.

I use the following code:

Set wc = Worksheets("Cost")

startcol = 14

n = 0

wdarow = 24

For m = 1 To 80 Step 4

n = n + 1

wc.Range(Cells(8, startcol + m), Cells(8, startcol + m)).FormulaR1C1

= "=CB.GetForePercentFN('Timing Analysis'!AE" & wdarow & ",pValue)"

wdarow = wdarow + 7

If n = 2 Then

wdarow = wdarow + 9

n = 0

End If

Next m

and the result in the cell is:

=CB.GetForePercentFN('Timing Analysis'!'AE31',pValue)

If I remove the single quotes around AE31 it works fine but what I don't

understand is why these quotes are appearing in the first place.

Thanks

Neme

for example.....

What is the method to use the .formulaR1C1 method within VBA to assign the

Average formula to a cell, assuming that I want to define the formula from

within VBA using the Range/Cells method(s).

Example:

Macro recorder provides the following code to define a cell using the

Average function:

ActiveCell.FormulaR1C1 = "=AVERAGE(R[-4]C[-2]:R[12]C[-2])"

Can this be done via something like:

Dim Rindex as Integer

Dim Cindex as Integer

Rindex=3

Cindex=7

strArg= "=" & Range(Cells(Rowindex, Colindex),Cells(Rowindex+7,Colindex))

ActiveCell.FormulaR1C1 = "=" & strArg

and have it parse correctly....

i.e. I would like to be able to manipulate the .FormulaR1C1 line via

variables that get worked upon prior to setting the formula in the cell.

Thank you in advance for the assistance.

--

Frank Bachman

(Grumpy Aero Guy)

VB:So here is what I need to do. Consider that I am trying to copy the values in cells K39:AF39 into cells K7:AF7, where they are multiplied by the value in I39. I have some cells in the ranges K39:AF39 that are null, and when I copy those values up into the Range K7:AF7, and they are multiplied by the value in I39, they become 0. I need them to remain null.caculate_values () titleRowNum = Range("I1").Value analyteNum = Range("F1").Value ActiveCell.Offset(0, titleColNum).Range(Cells(1, 1), Cells(1, analyteNum)).Select Selection.FormulaR1C1 = "=R[3]C*R[3]C9" End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

So I need to do an "IF the value in K7 is not null, then execute the code above, else make the value in K7 null"...the problem is I'm having a difficult time understanding the Formula R1C1 syntax.

The ENTIRE sub is shown below:

VB:HRC3_caL2_amount_snippet() Dim myStr, anyStr Dim RSDNum, titleColNum, titleRowNum, convertConst, myCase As Integer Dim countPblk, smpNoCol, analyteNum, smpNum As Integer Call smpType myCase = Range("B1").Value countPblk = Range("C1").Value smpNoCol = Range("D1").Value analyteNum = Range("F1").Value smpNum = Range("G1").Value RSDNum = Range("H1").Value titleRowNum = Range("I1").Value titleColNum = Range("J1").Value Range("A1").Select Cells(titleRowNum + 1, 1).Select ActiveCell.EntireRow.Insert Shift:=xlDown ActiveCell.Value = "Measured Concentration in Solution" ActiveCell.Offset(0, titleColNum).Range(Cells(1, 1), Cells(1, analyteNum)).Value = MeasConc ActiveCell.EntireRow.Select Selection.Insert Shift:=xlDown ActiveCell.Range(Cells(1, 1), Cells(1, (titleColNum + analyteNum + RSDNum + 1))).Select Selection.RowHeight = 6 With Selection.Interior .ColorIndex = 40 .Pattern = xlSolid End With Cells(titleRowNum + 1, 1).Select ActiveCell.EntireRow.Select Selection.Insert Shift:=xlDown ActiveCell.Range(Cells(1, 1), Cells(1, titleColNum)).FormulaR1C1 = "=R[3]C" ActiveCell.Offset(0, titleColNum).Range(Cells(1, 1), Cells(1, analyteNum)).Select Selection.FormulaR1C1 = "=R[3]C*R[3]C9" 'amount unit in "ng" but in "ug" for bricks End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

If there isent a second instance i want "" to appear.

VB:When i run this code i get a #VALUE! errorDim last_row As Long last_row = Range("H65536").End(xlUp).Row For c = last_row To 2 Step -1 If ActiveSheet.Cells(c, 8).Value = "" Then ActiveSheet.Cells(c, 11).FormulaR1C1 = "=INDEX(Sheet2!R2C2:R1063C2,SMALL(IF(Sheet2!R2C1:R1063C1=main!RC[-11],ROW(Sheet2!R2C2:R1063C2)-ROW(Sheet2!R2C2)+1,""""),2))" Else ActiveSheet.Cells(c + 1, 11).EntireRow.Insert ActiveSheet.Cells(c + 1, 11).FormulaR1C1 = "=INDEX(Sheet2!R2C2:R1063C2,SMALL(IF(Sheet2!R2C1:R1063C1=main!RC[-11],ROW(Sheet2!R2C2:R1063C2)-ROW(Sheet2!R2C2)+1,""""),2))" End If NextIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

VB:But when i run this code, it works perfectly.Range("L4").FormulaArray = "=INDEX(Sheet2!$B$2:$B$1063,SMALL(IF(Sheet2!$A$2:$A$1063=main!A4,ROW(Sheet2!$B$2:$B$1063)-ROW(Sheet2!$B$2)+1,""""),2))" Range("L4:L464").FillDownIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Can u use FormulaR1C1 with a FormulaArray?

Suppose I have defined cell K3 as a variable and suppose this cell currently has a value of 10. When I code the variable in the FormulaR1C1, it takes the hardcoded number 10 instead of the cell reference K3. I want to create a vba code such that if K3 were to change, the formula would be referencing Cell K3 and not hardcoded number in K3.

Apologies if my question is unclear. Thanks!!

VB:Single variable = Range("K3") ActiveCell.FormulaR1C1 = "=product(R[0]C[-2]," & variable & ")"If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I have a sheet where i reference to data on other sheets, every time i add a new sheet to the workbook i name the sheet and add

a new row to the main sheet and reference the data in the row via FormulaR1C1, this work fine as long as I use a text variable to reference the sheet name.

The sheet names are all to numbers separated with a dot, for example(1.1, 1.2, 2.12, 22.11, ….)

My regional settings are Danish, when I change the separator to a comma I can sort my list as text, but my code slams to a hold.

If I manually writes the code in each cell I can use the sheet names with the comma separator, but when I use FormulaR1C1, I get nowhere.

Here is the code I use to fill the cells at the main sheet:

VB:When I set vPos As string with dot separator no problem, but I change it to a comma separator it fails.qws .Cells(StopRow, 1).value = vPos .Cells(StopRow, 2).FormulaR1C1 = "=" & vPos & "!R1C2" ‘ here the code stops .Cells(StopRow, 3).FormulaR1C1 = "=" & vPos & "!R1C1" End WithIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Please anyone with a hint ?

I need to create formulas based on the contents of the above cell.

An example:

The dates, however, are not going to be static. The date range must always be for eight years, but it will be eight years back from the previous month end.

I think I can figure out how to change dates around so that it's always 8 years back from the current date, but I don't know how to handle inputting variables into that formula. There are too many quotation marks.

Anyway, I'm kind of confused, any help appreciated, thanks.

I'm writing a Macro that must find all fields that start with a space and move them to another column depending on the number of spaces.

Eg.from this

Carrots 6 Onions 7 Veggies 13 Bananas 5 Oranges 9 Apples 2 Fruits 16 Fridge 29To this:

Carrots 6 Veggies 13 Fridge 29 Onions 7 Veggies 13 Fridge 29 Bananas 5 Fruits 16 Fridge 29 Oranges 9 Fruits 16 Fridge 29 Apples 2 Fruits 16 Fridge 29I'm thinking of something like this in terms of code:

Sheets("Sheet1").Select ' Start moving fields ActiveWorkbook.Names.Add Name:="Start", RefersTo:=Range("A6") Range("Start").Select Do If ActiveCell.FormulaR1C1 = " " & (Whatever is contained in the field) Then Range(ActiveCell, ActiveCell.End(xlToRight)).Select Selection.Cut End If ActiveCell.Offset(1, 0).Activate Loop Until ActiveCell.Value = ""Thanks for your help:

I want to create a macro in a workbook that automatically links to

other worksheets. For instance, if i run the following code below, cell

E12 will automatically link to cell E12 in sheet "Accounting Data" of

file "6460.xls" in folder "C:My DocumentsAccounts

Range("E12").FormulaR1C1 = "='C:My

DocumentsAccounts[6460.xls]Accounting Data'!RC"

I could repeat this code over and over again in reference to other cell

ranges, but I'd like to clean it up. I've attempted to do so, with the

following code:

'------------------------------

Sub GetDataFromFolder()

Dim myFolder As String

Dim myFile As String

Dim mySheet As String

'Range("E12").FormulaR1C1 = "='C:My

DocumentsAccounts[6460.xls]Accounting Data'!RC"

myFolder = "C:My DocumentsAccounts"

myFile = "[6460.xls]"

mySheet = "Accounting Data'!RC"

Range("E12").FormulaR1C1 = "=&myFolder & myFile & mySheet"

End Sub

'-----------------------------

BUT - i get the following error

Run-time Error 1004

Application - Defined or Object Error

can some let me know how to fix this? I'm not sure if i need to use

"formulaR1C1" references or not. Thanks in advance

We use an engineering application that creates reports of pipeline data in excel. We then have to use part of this data to complete the list using some calculations and by looking up standard tables in another excel file or sheet. I have been given the task of improving the macros.

1. Most of the calculations have been done using absolute ForumlaR1C1, and by referencing other columns with offsets. Trouble is, when the column arrangement of the exported data is changed, the code has to be changed too. How could I avoid this? Would it be better to use find?

2. Is it faster to use FormulaR1C1 rather than calculating or doing a VLOOKUP in the VBA code itself and just filling the table with resulting data?

VB:I attach a portion of the data and some of the code, hope that helps clarify my question.ActiveSheet.Cells(Fila, 19).FormulaR1C1 = "=RC[2]*1.5" ActiveSheet.Cells(Fila, 20).FormulaR1C1 = "=VLOOKUP(RC[-13],'Equivalencias diámetros'!R2C1:R31C2,2,FALSE)" ActiveSheet.Cells(Fila, 21).FormulaR1C1 = "=RC[-4]" ActiveSheet.Cells(Fila, 22).FormulaR1C1 = "=RC[-1]*RC[-2]"If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks in advance!

Sang

how can i write a macro to add sum formula in certen row to make sum for certen number of rows (these rows are variable each time )without mention the name or number of cell

example

if i have value in cell a1 & a2 & a3 & a4

i need to have macro to add sum(a1:a4) in cell a5 without mention a1 & a4

in the macro because i need to use it any where in sheet not sut for column a

i try to use formular1c1

first_row = selection.row

( the row no where i want to start sum )

application.formular1c1=" =sum(r[-first_row]c:r[-1]c"

but it give me error

thank u in adv. for help

VB:lastrow is the lastrow in the range. After running the macro the formula gives me SUM(H$2:H235) which provides me the correct amount but I need to unachor the H$2. Does anyone know if there is a way to unanchor?Range("H" & lastrow + 1).Formula = "=SUM(R2C:R[-1]C)"If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

VB:ActiveCell.FormulaR1C1 = _ "=IF(INDEX(R[-1]C[-13]:R[-1]C[-3],1,MATCH( " & LowTotal.Address(ReferenceStyle:=xlR1C1) & " ," & Totals.Address(ReferenceStyle:=xlR1C1) & ",0))

C14 = Sheet2!D2

C15 = Sheet2!D3

C16 = Sheet2!D5

What i want to do is to change "D" to "E". Next time i run it, it will change from "E" to "G".

I want a macro to do it, so that I don't have to change it manually one by one. For some reason, when i try to use .formulaR1C1, the reference change, and I couldn't get it to work.

someone please help. Thanks in advance.

I have put together the following code to copy a manually entered spreadsheet, and then change the values of a range of cells with a formula.

The first sheet is a log to track invoice installments. Column M in the first sheet lists a discount rate. The discounted billing amount is entered in the appropriate cells in columns P through the last used column for each row.

This macro is to create a duplicate for reporting to another department (I believe to track commissions) but needs to report the amounts in Columns P through the last used columns at the non-discounted gross rate. I know what formula I need to use,

.FormulaR1C1 = "='Master file'!RC/(1-'Master file'!RC[-3])"

My problem is that while "'Master file'!RC" is correct, the next reference should only be incremented for Row changes, it should always reference Column M. I have no idea how to phrase this. Any ideas?

Code:

Sub FillSales() ' ' FillSales Macro ' Macro recorded 7/29/2008 by Conde Nast Publications ' Sheets("Sales Master File").Cells.ClearContents Sheets("Master file").Select Cells.Select Selection.Copy Sheets("Sales Master File").Select Range("A1").Select ActiveSheet.Paste lastRow = Worksheets("Sales Master File").Range("A" & Rows.Count).End(xlUp).Row lastColumn = Range("IV11").End(xlToLeft).Column For rwIndex = 2 To lastRow For colIndex = 16 To lastColumn With Worksheets("Sales Master File").Cells(rwIndex, colIndex) If .Value "" Then .FormulaR1C1 = "='Master file'!RC/(1-'Master file'!RC[-3])" End With Next colIndex Next rwIndex End Sub

Code:

Sub MyTest() With Range("c6") Range("c6") = .Offset(1, 0).FormulaR1C1 End With End Sub

I am trying to make the below code snippet work to return a date if all arguments are positive. Unfortunately, instead of returning the date, this function returns "". Is there something small I have missed out here?

Should I rather use .FormulaR1C1 = "=TODAY()"? Or is this because I have tried to do the Date function within a WITH statement.Interior?

'If rejected and resolved return todays date

If Left(Cells(ifindrow, iColNum(8) - 1).Value, 3) = "Rej" And Cells(ifindrow, iColNum(8)).Value = "Yes" Then

With Cells(ifindrow, iColNum(8) + 1).Interior

.ColorIndex = xlNone

.Value = Date

End With

End If

Many thanks for your consideration.

Kind Regards,

Stefan

HTML Code:

Dim O_Rng as range set O_Rng = Range("org_tbl!$A$2:$H$59") sheets("Sheet3").cells(2,9).select activecell.offset(4,0).FormulaR1C1= "=vlookup(RC[-7],org_tbl!O_Rng,2,false)I get #Name error.

I have tried changing the lookup table value to

HTML Code:

activecell.offset(4,0).formulaR1C1= "=vlookup(RC[-7],Worksheets(""org_tbl"").range(""$A$2:$H$59""),2,false)with no luck, yet I can use

HTML Code:

Activecell.offset(4,0) = application.worksheetfunction _ .vlookup(Activecell.offset(4,-7),worksheets("org_tbl").range("$A$2:$H$59"),2,false)and it gives me the correct result.

Any suggestions, thanks in advance!

mRow = 2

mCol = 3

Application.ReferenceStyle = xlR1C1

Worksheets(1).Range("R" & mRow & "C" & mCol).Value = "Test"

Does that clear the link and if not how can that be done in VBA? The file needs to become smaller somehow...

I am having the hardest time coming up with an answer to what is

seemingly the easiest function of Excel. I have Loop Until Statement that

gets me the absolute value of a column and lists it into the next column

over. I have it looped because there is no definitive ending point of how

many rows there may be, one day 5, the next day 205. I can get that done,

but what seems the most simple, I just can't figure out. How do I code it

to Sum the total of the 2nd column? Here is the code I have:

Sub Count_Cases()

Range("M5").Select

Do

ActiveCell.Value = Abs(ActiveCell.Offset(0, -1))

ActiveCell.Offset(1, 0).Select

Loop Until IsEmpty(ActiveCell.Offset(0, -1))

<<<Here is where I need it to Total all of the values in this column

starting with "M5" through: ActiveCell.Offset (-1,0){This is where I get

confused, I can't define the Active Cell and use FormulaR1C1, or can I?}

End Sub

Any ideas would be greatly appreciated.

Jacob

Cells(1, 2) = "Deleted Account Names" For Row = 1 To NumRows Cells(Row + 1, 2).Select ActiveCell.Formula = "=IF(LEFT(A2,4)='RSOF',VLOOKUP(A7,'Summary Total'!A12:B131,2,FALSE),VLOOKUP(A2,Composites!$A$12:$B$132,2,FALSE))" Columns.AutoFit Next Row

Thanks

Hello

I am tired of reading the cell reference using FormulaR1C1 method in macros. like

Range("L4").Select ActiveCell.FormulaR1C1 = "=RC[-4]-RC[-2]"Is there any way it can show the normal cell reference

like

Thank u

Toms

WRange.Value = "=" & "Ipbmatch(rc[-7]," & (LURange.Address) & ")"

Wrange is a predefined range of cells in one column

IPBMatch is UDF = Public Function IPBMatch(CellRef As Range, SrchVals As Range) As String

LURange is, again, a predefined range.

I'm sure this is straightforward for somebody that knows what they are doing but, unfortunately, clearly I don't!

Any help appreciated.

Regards,

Adrian