r1c1

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: INDIRECT("Interface!R2C"&(Code!R[-1]C+1)&":R27C27") This resolves successfully to the following formula, as shown by...

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...

I'm trying to write a Visual Basic program. In a spreadsheet, I have a column of data, and I want to define a cell under the column that contains the average of the cells in the column. The trouble is that I don't know the exact position or length of the column. I have no trouble selecting...

Hello, I need the below code to count the number of columns in a worksheet and extend an array formula to look at all of those columns. I know there is a 255 character limit with arrays so I've tried to break it in to smaller chunks. I'm getting a 'Type mismatch' error and it's breaking at the...

Hi, I'm using "Name Manager" in Formulas Tab to define LIST. Exaclty the same I want to do using VBA. I have: =OFFSET('Country'!$E$9,0,0,COUNTA('Country lvl'!$E$9:$E$20),1) Into VB: rows_x = ws.Cells(Rows.Count, "A").End(xlUp).Row wb_1.Names.Add Name:="Country_list", RefersToR1C1:= _...

Hi, I am new to VB coding and deperately need help... I have racked my brain but cant find the cause of error 1004 with this particualr line (highlighted in Red). Dim X as Long Dim iRow, iCol, intMonths As Integer iRow = X * intMonths 'X is the number of rows in Sheet1 with data iCol =...

Hello I have this formula for conditional formatting to highlight a row: =AND(OR(COUNTIF($D$5:$F$8,$D5:$F5)>=2),$G5<>0) If any of the values on the row is reapeatead in the range and another condition is true then highlight the row, the thing is I only know where the range begins, the end...

Hi, I have a 100+ column spreadsheet. For each column, I have to insert a new column (starting at column "C" to the last column) and fill it with text from the first cell of the column to the right. I use this code to make it work, but I have to copy it 100+ times for each column insert. Is...

Hi, I have in worksheet, from row L142 until L237 function: L142= IFERROR((SUM(I142:K142))/(K$242),"") L143= IFERROR((SUM(I143:K143))/(K$242),"") . .. ... .... L237 = IFERROR((SUM(I237:K237))/(K$242),""). Now in VB, I have a simply for loop: For i = 142 to 237 ws.Range("L" & i ).Formula =...

Hey guys, I have created this huge R1C1 formula and now I need to create a variable for the sheets names (NO1) and for the love of me I just simply can't. It works for the first INDIRECT, but for e.g. MATCH or FIND, it doesn't. Any ideas? ActiveCell.FormulaR1C1 = "=SUM(INDIRECT(""" & basicsh &...

Hello everyone! I am quite new at VBA and my boss assigned me a project that requires me to calculate the average, Ann ROR, and standard deviation of various funds using a For Next loop. The spreadsheet given to me has 331 rows and 180 columns. My boss wants me to use Finalrow and FinalColumn...

I have two questions. Referencing the following code: ActiveCell.Offset(0, 1).Activate For k = 0 To 10 ActiveCell.Offset(0, k).FormulaR1C1 = "=RC[-39]+(RC[-39]/21)*VLOOKUP(RC[-42],DSOH" & aRegions(i, 1) & ",4,FALSE)-RC[11]" Next K 1. I am trying trying to get make the RC[-42] an absolute by...

Ok, I am trying to figure out how to increment a R1C1 reference. I am running this code through a loop so I have to move columns for each loop. Here is an example of my attempted code Dim ProductCol As Integer ProductCol = 1 ActiveCell.FormulaR1C1 = "=Original!R[ProductCol]C &...

Greetings all I am working on a project that pulls information from several sources and builds a report for the powers that be every week. I actually have it basically working using a pivot table elsewhere but I am not getting all of the records there for some reason and I also wanted to clean...

Hello All, The code below yields run-time error 1004 on the lines for AH15 and AH44, but works fine for AH19 and AH48. The only change to the formulas in AH15 and AH44 was "-R[-5]C[-20]-R[-5]C[-19]-R[-5]C[-11]" replaced "-R[-5]C[-21]-R[-5]C[-12]". The macro uses an input box for the week number...

Can anyone please tell me why the R1C1 formula line is giving me syntax error? I am using excel 2003. I have a dynamic range of data on report tab and trying to copy from ABC pivot table (on ABC pivot tab) to the report tab starting at the end of the dynamic range of data. I am trying to auto...

Hi, Been a while since I was here but my memory is of a very knowledgeable forum so hopefully someone can help! I have a workbook in which I need to create a set of range names that effectively repeat 100 times. By way of explanation, say: Cells A1:A4 need to be called "Item_001_Header" Cells...

I want to drag a formula to create a column, but I need some cell references to change as I drag , and some not to =IF(ISTEXT(A2),VLOOKUP(A2,'ingredients & components'!A2:E100,5,FALSE),""), the red A2 needs to increment , but the range a2:e100 needs to stay the same , I think I need to use $a$2...

Hello all, Long time reader, first time poster. I am trying to create a dynamic range using R1C1 that will let me loop through (I have this piece solved for) a number of matrices and copy from one sheet to another- basically, I want to increment the R1C1 values in my loop and step through the...