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...
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"")...
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
Lc = .Cells(1, .Columns.Count).End(xlToLeft).Column...
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...
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?
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)"
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...
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
Application.ReferenceStyle = xlA1
But I am also wondering if there is a way to toggle the "Show paste option when content is pasted"...
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...
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...
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.
Dim startRow As Long
Dim lastRow As...
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
Set Wkb = ThisWorkbook
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...
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)"
Worksheets("View Data").Range("D2").Formula = "=MID(Data!F2,(LEN(Data!F2)-9),9)"
the column F...
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?
XXX.XXX."R" & top_i & "C3:R" & btm_i & "C3".XXX...
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...
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 !!!
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...
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:
This resolves successfully to the following formula, as shown by...