formular1c1

  1. J

    Write a Jagged array containing value and FormulaR1C1 to excel.

    I have a jagged array containing normal values and formulas in R1C1 notation and I want to write it into excel. I used following code for the same dataRange.Value2 = jaggedArray; But it produced unexpected results in case of column anchored formulas like '=$A1'. I got it fixed by rewriting...
  2. H

    VBA - Sheet name variable for property .FormulaR1C1

    Hi, How can I insert a variable for the sheet name. Example: I want sum cells A1:B10 in sheet1i=1 Range("A1").FormulaR1C1 = " =sum('sheet & i & '!R1C1:R10C2)" --> I know this is the wrong way to do it... Thanks in advance! Hurkk
  3. E

    How can I easily blend text and formulas with FormulaR1C1?

    Hello How can I easily blend text and formulas with FormulaR1C1? Ideally I don't want to have to double quote everything or escape numerous characters etc. Is there a neat way? Thanks
  4. A

    Inserting Time and Sumif Formulas with VBA

    Hi all, I have a legacy macro that is used to read data from an excel file to a linked file to get info in to an Access database. The source files are protected, and I don't know where to find the passwords, but I need to get more of the information out of the source file and in to my database...
  5. J

    variables in .formula vs .formular1c1

    Trying to sub a variable into vlookup for the table array. This works: Sub vl() ' temporary fix for summer teach Dim vl1 As Range Dim vl2 As Range Dim vl1a As Range Set vl1 = Range("A:A").Find("C.I.") Set vl2 = Range("B:B").Find("17200") Set vl1a = Range(vl1, vl2) MsgBox...
  6. M

    VBA FormulaR1C1 function errors array formula in cells of another workbook

    Has anyone seen this and knows what's causing it and/or has a cure or workaround? I have a workbook that pops up a user form that base on the selection will update the associated workbook sheets. I have another workbook open at the same time that has several array formula in table that refer to...
  7. M

    How to reference an active table in a FormulaR1C1

    I have a workbook that has multiple tables on each worksheet. Here is the formula that I have: Sub Test() Const WHAT_TO_FIND As String = "Application" Dim ws As Excel.Worksheet Dim FoundCell As Excel.Range Dim myTable As ListObject Set ws = ActiveSheet Set FoundCell =...
  8. C

    Use FormulaR1C1 to populate formula if cell contains specified value

    Hello, I need help with inserting a multiplication formula that is using two cell values in the formula IF another cell contains a specific value. If cell G1 = "1", then I want to insert a formula in cell H1 as follows G1*D1, and so on throughout the table of data. The table of data will...
  9. G

    Help entering R1C1 formula using VBA

    Using Excel 2007 and VBA. I need to enter formulas into multiple cells of a worksheet referencing cells on another worksheet in the same workbook. The cells in the source worksheet are not contiguous, so copy/paste an existing formula on the destination worksheet will not work. I recorded a...
  10. S

    FormulaR1C1 in combination with constants

    Hi all, I have a rather simple code where I want to split a row in two (say a bill). Some of the cells will contain the same information (e.g. date), which is just copied from the row I am splitting. When the row is split, I want some cells in the new row to be the residuals of the total amount...
  11. C

    Using a single variable in a FormulaR1C1

    This is a simplification of my problem. A1 has a number and B1 is selected. When I run this code it results in an error, why? Sub test() Dim x As Single x = 0.5 ActiveCell.FormulaR1C1 = "=RC[-1]*" & x End Sub
  12. C

    Use a Range variable in FormulaR1C1

    Why does the following code results in a error? (The code is a simplified version of my code, in reality I want a much bigger formula.) Sub test() Dim a As Range Set a = Selection Range("A1").Select ActiveCell.FormulaR1C1 = "=MAX(" & a & ")" End Sub
  13. R

    Concatenate

    I have read many discussions on how to CONCATENATE or not to. Using public functions or entering the data in active cell. Right now I want it to select range D2 and combine all data in column D (not including blanks or duplicates) into Cell D2 with a delimiter (/) Range("D2").Select...
  14. P

    VBA Macro, copy and paste between cells

    I'm trying to copy a value and paste it using R1C1 function. Most of my macro looks like this (which works): Range("A2").Select ActiveCell.FormulaR1C1 = "=IF(R[-1]C[-10]=""value"",R[1]C[1],"""")" The above pattern is getting all of the values that I want into appropriate columns. Where I'm...
  15. E

    Formula with relative references without selecting cell

    Hi, I'm trying to use VBA to put a formula in a cell which will average the values in the last 6 columns. The following works OK: TableSheet.Cells(WorkingRow, LastColumn + 1).Select ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-6]:RC[-1])" (WorkingRow and LastColumn are integers) but I'd like to...
  16. S

    Hardcoding works but Variable doesnt in SUMIF

    This looks like a fairly stupid question to ask and i've tried my way around the forum but couldn't find an answer that worked for me. I am passing using FormulaR1C1 with a SUMIF. When I pass a hardcoded value into the Criteria, it seems to be working fine. But when I try to pass a variable...
  17. L

    Vlookup issues VBA

    Good Morning All Any help with this issue would be greatly appreciated, i cannot seem to figure out why this wont work..? Dim refcell As String Dim Location As String Dim excelname As String Dim ext As String Dim r As String Dim lookuprange As String Dim cs As Integer Dim Boo As Boolean Dim...
  18. Z

    Excel VBA: copying a cell with FormulaR1C1

    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. Range("M3").FormulaR1C1 = "=RC[-2]" Range("M3").AutoFill Destination:=Range("M3")...
  19. M

    FormulaR1C1 Issue

    If I enter this in VBA code, it works fine: Cells(X, Y).FormulaR1C1 = "=ROUND(RC[-12]*2.31556463,2)-401.59" But if I enter this: Dim TheFormula As String Let TheFormula="=ROUND(RC[-12]*2.31556463,2)-401.59" cells(x,y).formular1c1= TheFormula This time I get the error message "Runtime Error...
  20. jecruz

    Getting the value of .FormulaR1C1 help please

    Hi Everyone, My question is about this: ActiveSheet.Cells(i, 106).FormulaR1C1 = _ "=VLOOKUP(RC[-4],'[Lookup Reference.xls]Sheet1'!R2C1:R11C2,2,FALSE)" The output in excel is the formula itself. How can it be just the value retrieved from the lookup? I guess what I need is the...

Some videos you may like

This Week's Hot Topics

Top