formular1c1

  1. A

    FormulaR1C1 not working when using UserInterFaceOnly:=True

    Hi I am trying to run some macros in a protected sheet. I believe using the UserInterFaceOnly:=True method will allow this and i have it in my Workbook_Open macro. But a simple operation of copying a relative formula does not work. Setup code: Private Sub Workbook_Open() Dim ws As Worksheet...
  2. A

    FormulaR1C1 + reference problem

    Hi guys! :) So, I've got this piece of code, which is working: ws.Range("A$" & rowCount & ":" & "AX1").FormulaR1C1 = "=[lol.xlsx]korr!RC" It simply copies A1 from one file to A1 of the other file, but with explicitly referencing the path (which is exactly what I need). However, as I have to...
  3. 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...
  4. 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
    • Hurkk
    • Thread
    • formular1c1 r1c1r10c2 sheet variable vba
    • Replies: 2
    • Forum: Excel Questions
  5. 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
  6. 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...
    • AlexB123
    • Thread
    • formula formular1c1 link between workbooks sumif vba
    • Replies: 0
    • Forum: Excel Questions
  7. 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...
  8. 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...
    • Mrock1
    • Thread
    • cell array formula excel formular1c1 vba workbook
    • Replies: 0
    • Forum: Excel Questions
  9. 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 =...
  10. 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...
  11. 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...
  12. 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...
  13. 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
  14. 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
  15. 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...
    • rascal9
    • Thread
    • concatenate column macro concatenaterange formular1c1
    • Replies: 5
    • Forum: Excel Questions
  16. 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...
  17. 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...
  18. 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...
  19. 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...
  20. 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")...
    • zeno
    • Thread
    • excel 07 vba code formular1c1 vba 2007
    • Replies: 6
    • Forum: Excel Questions

Watch MrExcel Video

First Look: Excel VBA Killer - TypeScript Debuts in Excel - Ep # 2322

This Week's Hot Topics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top