r1c1 notation

Hello, I am trying to combine several columns into one column, pasted on another sheet. I am getting an error of "Error 1004 Method 'Range' of object '_Worksheet' failed" when running my code. I am trying to use range.cells() so that I can reference columns in a for loop. At the top of each...

Hello, does anyone know how to insert an array formula using R1C1 style? I am attempting to convert the array formula (with absolute/relative references as shown below). Sub Insert_Rows() Dim Sh As Worksheet Dim End_Row As Long Dim N As Long Dim Ins As Long For...

Hi Everyone! As my username suggests, I am very new at VBA with Excel; however, I am really trying to create macros that can be utilized whenever. The one I am working on is one for my data. Its purpose is to sum Row 5 cell all the way down to the last cell row of data in the specified column...

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

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

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

Hi, I'm trying to specify a range for count if based on the value of a cell - if this is possible (i cant work it out using either type of cell reference), if not i'm looking for a way of doing this? ie. cell E7 currently has =IF(COUNTIF(E3:E6,"No")>0,"No","Yes") but i would like the range...

Hi all, I'm trying to go through a list on sheet3 which is populated with only true or false, then using this I want to hide or unhide the corresponding columns using R1C1 notation in sheet1. I'm fairly new to vba and not sure what is wrong with my code. I get an "application-defined or...

Hi All, Here is the code I am using to find duplicates amongst certain cell values ( max 12 here). I have been able to implement it but by using a variable in the R1C1 style formula. Here is the code: dd = -1 * (12 - Frozen) ActiveCell.FormulaR1C1 = "=IF(SUM(IF(FREQUENCY(MATCH(RC" & dd...

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

Hi Everyone. I am a novice and this is my first post, but this site has solved many issue for me in the past. Anyway, onto my issue. I have a data file with 3 sections, and I'm doing calculations on the middle section. For the data that I want counted, I've been using code like this: With...

Hello all, I am writing a macro to automate bubble charts. I've made one that pulls in the value itself into the chart, but if you go back and change the data, the chart does not update. Here is an example: ActiveChart.SeriesCollection(iCounter).XValues = ActiveSheet.Cells(iRowNum, iColNum +...

Is there a way to get the column letter (in A1 notation) of the active cell, as opposed to its number (from R1C1 notation). =ADDRESS(5,10,4 ) returns row and col in A1 notation, but getting just col is a parse job My sources of column number (R1C1) Formula: =Column() Code: MsgBOx("active...