r1c1

  1. 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"")...
  2. 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...
  3. 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...
  4. 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?
  5. 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)"
  6. 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...
  7. 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"...
  8. 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?
  9. 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...
  10. 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...
  11. 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...
  12. 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 =...
  13. 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...
  14. 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...
  15. 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...
  16. 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...
  17. 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...
  18. L

    Dynamic Formula

    I have a data set with a dynamic range and can write a formula to fill down. but o would like the r1c1 callouts to use a dynamic range instead of a preset. Help please!! here is my current formula (10 columns of data) normally I would: Dim endRow As Long endRow = Cells(Rows.Count...
  19. Felix_Dragonhammer

    Index Function Error, R1C1 References

    Hello! I'm having a spot of difficulty, and after time spent researching the issue on the web and forum I've come up with no answers. I have an INDIRECT function as follows: INDIRECT("Interface!R2C"&(Code!R[-1]C+1)&":R27C27") This resolves successfully to the following formula, as shown by...
  20. Felix_Dragonhammer

    Index Function Error, R1C1 References

    Hello! I'm having a spot of difficulty, and after time spent researching the issue on the web and forum I've come up with no answers. I have an INDIRECT function as follows: INDIRECT("Interface!R2C"&(Code!R[-1]C+1)&":R27C27") This resolves successfully to the following formula, as shown by...

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top