1. E

    Unable to have VBA insert a relative reference then make part of it absolute.

    When I run this VBA code: ActiveCell.FormulaR1C1 = "=SUM(RC[-1]:R[14]C[-1])" It places the following formula in the active cell: =SUM(H19:H33) I need to modify that VBA code so that it whatever the active cell is it makes the first reference absolute. If I was clicked inside C19 it should...
  2. W

    Converting basic formula to R1C1 notation

    Hi there, Please can someone help convert =IF(F2=F1,"Y", "N") into R1C1 notation? Context I am inputting a formula in column AU which identifies duplicate values based on values in column F. In excel my formula in cell AU2 would be =IF(F2=F1,"Y", "N"). Cell AU3 would be then be...
  3. I

    FormulaR1C1 + Error 1004

    Hi, I have written some code that looks for column headings to locate the columns of data required in my formula, thus returning a column number. So logically the best method of creating my formula in the cell is to use the FormulaR1C1 function in vba, however I have been unsuccessful in getting...
  4. 0

    Loop & R1C1 Formula: variable reference problem

    Hello everyone, I need to fill a tables with certain formulas, and so far I used the following approach: Range("E6").FormulaR1C1 = _ "=FORECAST.LINEAR(RC[-2], OFFSET(INDIRECT(""[Flight_A.xlsx]""&R5C5&""!$N$2:$N$70""), MATCH(RC[-2],INDIRECT(""[Flight_A.xlsx]""&R5C5&""!$AL$2:$AL$70"")...
  5. R

    AutoFill Across Columns

    Hard to believe I am here for this, but.... here I am Just trying to AutoFill xlFillSeries from Column A to LastColumn Here is where I'm at (I think I'm close?) Application.ScreenUpdating = False With Sheet1 Lc = .Cells(1, .Columns.Count).End(xlToLeft).Column...
  6. J

    Unable to copy from a worksheet to paste NOT into A1 on another worksheet

    I've divvied up data sorted by a column, so it could be divided by rows, and assigned to several people to analyze. I put each person's data into their own worksheet. We used a shared Excel doc via Office online, and each worked in our indiv worksheets. I'd like to rejoin the data now. Yet...
  7. B

    Clear Cell using R1C1 After xxxx # of seconds

    Hi Comunity, I am using formula "Range("F6").FormulaR1C1 = "" " as part of a vba macro to clear values from a cell which is working perfectly. But i was curious if there is a way to delay the clearing to say maybe 15 seconds after the macro has ran? Any guidance?
  8. P

    Convert formula to Formula R1C1

    I have a dynamic formula and it is working just fine, however I want it to be converted to R1C1 formula. Can you please help me with this? Cells(5, 7).Formula = "=VLOOKUP(F5,'[" & Range("A1").Value & "]Job Tracker'!$E$1:$F$" & a & ",2,0)"
  9. G

    Merge Format from 2 excel sheets

    Hi All, I have 2 excel sheets which are totally same in terms of Rows/Columns or cell values. The only difference is cell color. e.g. My 1st workbook has 10 rows and 10 columns. In this workbook cell R1C1, R1C3 , R2C2, R2C5 are red in color. My second workbook is same as 1st workbook but in...
  10. M

    One click fix

    So I am using a third party add on for excel and it messing with my setting. I have set up a macro button to do this to change the R1C1 Sub r1c1() Application.ReferenceStyle = xlA1 End Sub But I am also wondering if there is a way to toggle the "Show paste option when content is pasted"...
  11. J

    VBA SUM Function with Dynamic Range R1C1 Formula

    I'm trying to sum up columns of data, but the numbers of rows will be different each depending on the inputs that the user puts in. Is there a way to have a adjustable R1C1 formula?
  12. B

    Expanding Ranges when using R1C1 Notation

    Hi there, I need to be able to using a dynamically expanding range and am having some trouble with a formula using R1C1 notation with a COUNTIF statement. Basically, I need to base my countif on the number of rows with content in column A. You can see that in the code below that the range is...
  13. S

    Date Format

    In the following code when setting the variable "Timeline", it's correct, for example "2017-03-10" but in R1C1 formula it's displayed as "2017-3-10". I changed Timeline in R1C1 formula to Format(Timeline,, "yyyy-mm-dd").No effect. Do you have an idea what I am doing wrong? Dim Timeline As...
  14. X

    VBA Sum R1C1 copy down

    Hi have the below code & want to manipulate it to copy down to the end of my data. Right now it goes down to Q3500 but I’d like it to be dynamic. Also if the structure of my code is funky m, mainly the last With statement I’m open to suggestions. Sub Test() Dim startRow As Long Dim lastRow As...
  15. S

    R1C1 Formula

    RUN-TIME ERROR '91' Object variable or With block variable not set Sub SummaryRow()Dim Wkb As Excel.WorkbookDim ws As Worksheets Dim ws_count As Integer Dim i As Integer Dim LastRow As Long Sheet4.Range("a183:M183").Copy Set Wkb = ThisWorkbook ws_count =...
  16. B

    VBA Compatibility Problem 2003 to 2016

    I have a macro that I’ve been using in Excel 2003 for years that works flawlessly. It creates a formula using R1C1 references and copies it then pastes it to other cells, etc. On a new computer with Excel 2016 the formula comes out wrong as it doesn’t seem to interpret the R1C1 the same way...
  17. R

    convert a formula R1C1 to formula of A1 format

    How can I convert a formula R1C1 to formula of A1 format. I have a formula as: Worksheets("View Data").Range("D2").FormulaR1C1 = "=MID(Data!RC[" & Var1 & "],(LEN(Data!RC[" & Var1 & "])-9),9)" to Worksheets("View Data").Range("D2").Formula = "=MID(Data!F2,(LEN(Data!F2)-9),9)" the column F...
  18. P

    How can I dynamic give a name range address by R1C1 Format?

    If I get a name range address such as :C3:C64 , transfer to R1C1 is R3C3:R64C3 Suppose I want to change 3 -> top_i 64 -> btm_i How could I do for this case? e.g. XXX.XXX."R" & top_i & "C3:R" & btm_i & "C3".XXX...
  19. C

    VBA: Fill formula down column

    Hi I have a data set where i need to filter on 2 criteria and then perform a simple calculation for the remaining records to show the numberof days between 2 dates. I had previously been looping through cells with an if statement to do this but it seems to slow down the code so I'd like to try...
  20. Z

    Syntax Error Not R1C1 Problem. Manual formula work fine

    Hi all, I cannot fathom why this returns syntax error. I've done all the fiddles and bits I know and it still gives it me. The formula works fine when i type it in but when i do it in a macro. Nope. Ive typed the macro, I've recorded the macro. Nothing. Its driving me up the wall !!! Sub...

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