r1c1

Hi all, I am tyring to do a simple(atleast thats what I thought) code. I have a drop down which calculates the principal payments based on the cashflow period specified in the drop down. The drop down has 3 items, Monthly Quaterly and Yearly Here is my code Sub DropDown706_Change() '...

Hi, guys i have written the COUNTIF formula in A1 style, now i want to use it in macro but in R1C1 style. I am trying from last 3 hours but can't do it. here is the formula, Please help me to convert it into R1C1 reference style. "=COUNTIF(B1:P1,"">=""&Param!$B$1)" Thanks...

Hi Folks, I was wondering how I could reference a row and column using a function for each. e.g. If I want the cell to equal R1C1 then I enter =R1C1 into the forumla ... obviously but if I want this to be dynamic then how do I reference R(a function)C(another function) e.g...

Is there a formula, no VBA please, that can return the relative position of a worksheet in a workbook? For example, the formula would return a "2" when pointed at the second tab, and a 10 when pointed at the 10th? Thanks

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

Every time I copy and paste cells in a specific workbook it changes the reference style to R1C1. Setting it back in the options, saving the document, closing Excel then reopening brings it back as A1 referencing but copy and paste will again return it to R1C1. I've checked for VB code and...

Hi, I have the below formula [in Red] writen in VBA but unfortunatly it doesn't work, if I run the code it pastes the whole line as text within the cell, if I then click on the cell and press ctrl+shift+enter it works perfectly. One of the Mods on here said that it's because I need to use...

Help! The following statement works fine: lookupval = Application.WorksheetFunction.VLookup(Company, Workbooks(main).Worksheets("Comp").Range("A1:G890"), 1) However, this equivalent statement produces an error: lookupval = Application.WorksheetFunction.VLookup(Company...

I am using Excel 2007. Today when I opened up Excel, I noticed the formulas being automatically created from my actions were a little strange. Then I noticed the columns weren't labeled with letters instead each column was labeled with a number. So cell A1 is now R1C1. Not sure how this...

Hey! I have this code that is clear won't work the way I want it to without relative references (R1C1). The original is this: Cells(C.Row, "F").Formula = "=IF(E18=""Cost Per Stop"",ROUND(SUMIF(P:P,""sum1"",F:F)/SUMIF(S:S,""stop"",F:F),2),IF(E18=""Cost Per...

Basically in my VBA I am assigning a formula to a particular cell, and I need to declare an entire column. So my end goal, for example, is to have this formula in cell A1: <code>{=AVERAGE(IF(B:B="Yes",C:C))}</code> So in my VBA I successfully used this line: <code>[A1].FormulaArray =...

I have this code: While Not (IsEmpty(Selection)) pointer.Offset(0, 1).Formula = pointer.Offset(0, 1).Formula & "+sumif('" & sourcesheet & "'!" & chr(columnfinder)+64 & ":" & chr(columnfinder)+64 & "," & pointer.Value & ",'" & sourcesheet & "'!" & chr(Selection.Value)+64 & ":" &...

anyone tell me how ot reference a range using r1c1 and the sheetname, my sheet name is 0 I have "=ABS(Worksheets(0).Cells("R2509C3:R2717C41"))" it works fine as "=ABS("R2509C3:R2717C41")" but obviously references the current sheet, and not sheet "0"

I'm trying to set up a FOR NEXT loop with the variable X. (I know that you shouldn't set x as a variable, but it referrs to so many differnt things in the code that I couldn't think of a more descriptive name) The problem that I have is that I can't seem to set the variable in an R1C1...