Function To Mirroring Cell Relative To Function's Location
I have created a List in excel of various tasks. (See attached example.) Each row contains one cell with a function which equals the cell above it and to the right. So the function for cell C3 would be D2. The only importance is that the cell mirrored is always the cell one above and to the right. However, now I would like to be able to rearrange this List, but the “one up and to the right” function will now be all over the place.
Is someone aware of a function which will target a relative location instead of an exact cell?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Force Function To Use Row Relative To Cell Housing Function
I have been looking around have this much code from this site, modified. What I am trying to do is calculate numbers by a position with cells D through K having numeric values. I have 'hardcoded' the cells (D2, E2, etc in the code below), but in reality I only want the current row (so if the formula is on the 2nd row, I want D2, if it is on the 3rd row, I want D3). My problem is obviously the formula isn't working because I am not correctly tying back to the spreadsheet (Positioncalc.xls). When I put the formula in the spreadsheet it works, but in my script I get 0 everytime. My Script: Function Position(rCell As Range, Optional RightPosition As Boolean) Dim vResult Select Case rCell.Text Case "QB" vResult = (2*D2) + (2*E2) + (2*F2) + (4*G2) + (2*H2) + (1*I2) + (4*J2) + (3*K2) Case Else vResult = "Invalid Position" End Select If RightPosition = True Then Position = vResult Else Position = "Position not valid" End If End Function So, when I put =Position(A2,True) I expect to see the formula results of those cells calculated based on the position (QB, HB, etc with their unique formulas). My next challenge after this is to highlight certain cells based on the Position. So if A2 = QB, I want cell D2 boldface and Red, etc. I have seen some scripts on colors and such here, so I might be able to figure it out.
View Replies!
View Related
Insert Relative Function That Uses Variables Into Cell
I am using VB to insert a function into a cell on an excell worksheet. The relative references work fine. For the absolute reference, I want to use a named range (LowTotal). If I simply put the name into the function, then I just get the name as text in the function. The second insert formula is were the named range is intended to go. Here is the code so far... I need to know how to use the varriable name in the .formulaR1C1, so that VB knows it is a variable and not just text....
View Replies!
View Related
Converting The Output From A Match Function Into A Location
I would like to know if it is possible for the output from a match function to be correlated to a location? The output I receive is just a number. I would like to use this number as the row, but I would like to hyperlink it into a formula so that I can create a macro for this process. In essence I would like for a number in one cell to become a location in another cell.
View Replies!
View Related
Input Box Location As Function Of Target
Trying to set this code to position the input box over the target cell so a user has to fill in the box before proceeding. Is this possible? ElseIf Target.Value = "Plate" Then Add = InputBox("Please Enter # of sides to be Painted. If Not Required Enter 1", "Paint Sq.Ft.", "1", "c", "Target") If Add = "2" Then Target.Offset(, 19).FormulaR1C1 = "=RC[-9]*2" Else Target.Offset(, 19).FormulaR1C1 = "=RC[-9]" End If I Know input box movement is set as twips but can i just somehow specify the intersection between row c and target as it's position?
View Replies!
View Related
Run Macro Function To Create A Relative Reference
I'm a definite newbie when it comes to macros but can figure out formulas ok. So I wrote a formula to break out a sort string and it got pretty complicated so I’m trying to create a macro so I don't have to write out the formula all the time. However the sort tag can be in different columns when I need this formula so I tried the "run macro" function to create a relative reference. So it changed my formula from this: PHP =IF(ISERR(FIND("("&(COLUMN()-COLUMN(EM2))&")",EM2)),"",IF(ISERR(FIND("("&(COLUMN()-COLUMN(EM2)+1)&")",EM2)),MID(EM2,FIND("("&(COLUMN()-COLUMN(EM2))&")",EM2)+3,100),MID(EM2,FIND("("&(COLUMN()-COLUMN(EM2))&")",EM2)+3,FIND("("&(COLUMN()-COLUMN(EM2))+1&")",EM2)-FIND("("&(COLUMN()-COLUMN(EM2))&")",EM2)-3))) PHP "=IF(ISERR(FIND(""(""&(COLUMN()-COLUMN(RC[-1]))&"")"",RC[-1])),"""",IF(ISERR(FIND(""(""&(COLUMN()-COLUMN(RC[-1])+1)&"")"",RC[-1])),MID(RC[-1],FIND(""(""&(COLUMN()-COLUMN(RC[-1]))&"")"",RC[-1])+3,100),MID(RC[-1],FIND(""(""&(COLUMN()-COLUMN(RC[-1]))&"")"",RC[-1])+3,FIND(""(""&(COLUMN()-COLUMN(RC[-1]))+1&"")"",RC[-1])-FIND(""(""&(COLUMN()-COLUMN(RC[-1]))&"")"",RC[-1])-3)))" Now my problem is in the first formula I need "$EM2" not "EM2". I tried the obvious to me fix of R$C[-1] which did not work... And when trying to re-run the macro it puts RC143 which will not work because the sort string is not always in column EM.
View Replies!
View Related
Function Argument's Cell Adress
I have an attached file and I am trying to build a VBA function to calculate total values. First row is "RollingTime" and for example if I am trying to calculate the "Total" value. For "RollingTime" = 2 it should be RollingTime(2)*Percentage(2)+RollingTime (1)*(1-Percentage(1))*Percentage(2)+RollingTime(0)*(1-Percentage(0)*(1-Percentage(1))*Percentage(2) Which is 109732508*0,3 + 1017508995*(1-0,2)*0,3+1587172158*(1-0,1)*(1-0,2)*0,3 And here is the code I have tried to produce:
View Replies!
View Related
Passing Cell References From A Cell's Text To LINEST Function And A Graph
I'm using Excel 2003 and I'm trying to make my LINEST function and the source cells for a scatter graph use the cell range specified in another cell in the document. I figured out that it was simple to do the following: I250 contains: 10, shows: 10 I251 contains: ="I"&I250, shows I10 But I can't figure out how to do this in a larger function. I've tried a few things but none of them work. Here is an example where I want it to do a LINEST with y values in cells Ja - Jb and x values in cells Ia - Ib (where a and b are integers specified in cells I250 and J250 respectively):
View Replies!
View Related
Method 'location' Of Object '_Chart' Failed
I'm currently working on a fairly complex VBA project in Excel 2007. Basically though, I'm downloading information from a site, massaging it, creating a lot of graphs, saving them locally, deleting the chart from excel. I then save a copy of the workbook to a local file, clear the Spreadsheet, and then Do the same for information from another site. When I had all of the code in one Module and ran it as a macro it ran flawlessly. Now i've put the information into a Class. And am pretty much running the same process via a UserForm. The problem I have though is that at any point after I execute the (and please assume that all variables have been declared, because they have)
View Replies!
View Related
Passing Worksheet Name To Function (Run-time Error '42')
I have a function which i am using as part of a macro. The macro itself works fine and locates a search string i type into an input box across ALL worksheets in an excel doc. However, i have a function which takes the results and prints it to the front page, but when i try to use the worksheet name which has been passed to the function, i get the error: Run-time error '42': object require. this happens when i get to a line of code which says
View Replies!
View Related
IF Function: Range That Should Display 'Heavy', Medium Is Displayed Instead
=IF(('MAIN 3'!N54<=H52),"Light",IF(('MAIN 3'!N54>H52,N54<H54),"Medium",IF(('MAIN 3'!N54>=H54),"Heavy",""))). The first two parts work fine: =IF(('MAIN 3'!N54<=H52),"Light", IF(('MAIN 3'!N54>H52,N54<H54),"Medium", Light and Medium is displayed when I enter the appropriate range but when I enter a range that should display 'Heavy', Medium is displayed instead.
View Replies!
View Related
Type Mismatch '13' On Passing Function Result To Variable
I have the following codes in my spreadsheet, they are pretty much the same and both used to work perfectly. However, now the first code returns a Type Mismatch '13' on line beginning mynum= and the second one works perfectly. Sub addtasks() Application.DisplayAlerts = False myrow = Cells. Find(" Total P&C Estimate").Row - 3 mycell = Cells(myrow, 2) mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1 With Range(Cells(myrow, 2), Cells(myrow + 2, 2)) .EntireRow.Copy .EntireRow.insert Shift:=xlDown End With Application.CutCopyMode = False Cells(myrow + 3, 2) = "Task#" & mynum Application.DisplayAlerts = True End Sub
View Replies!
View Related
'C:Documents...' Inside Worksheet Function Call
I have a spreadsheet that uses a function defined in VBA code that is housed in an add-in which is stored in the default location (C:Documents And Settings{user_name}Application DataMicrosoftAdd-Ins). To share this spreadsheet, I first install the add-in on the other person's computer. Then, when the other person opens the spreadsheet, the function doesn't work because the function call on the worksheet contains the whole path of the add-in (and the path contains my {user_name} rather than the other person's). So, the immediate solution is to just delete the path of the add-in from the function call (so that only the name of the function remains, plus the arguments of course). But this is quite inconvenient given the number of times the function appears in the worksheet, and it's not practical as we continue to share files like this. My question is: is there a way to make sure that the 'C:Documents...' never gets added to the function call in the first place? I mean, why doesn't Excel just try to run the function locally before it adds that path that refers to a separate computer? I do realize that there are other methods of sharing VBA code. For example, I know that I could include the code in the specific spreadsheet that I'm trying to share, and this would work. But, that's not the answer I'm looking for (for one, the function refers to a very large amount of data stored in the add-in, and there are other reasons that make the aforementioned method impractical). I'm wondering if I'm missing something, and if there is a way to stop Excel from tacking on the path string to the function call, so that the function would just work on any computer as long as they have the add-in installed?
View Replies!
View Related
Using 'left' Function To Take Out Number Upto A Certain Character
Pretty sure this has been asked but have searched the forum to no avail, but I need to extract the numbers from a value which has a letter on the end. eg. 1000x I need to get out the 1000 or 2p I need the 2 I have sooooo many values ranging in numbers of digits, so just basic left(A1,2) won't work, I'm sure I've seen a search or find function but don't know how to use them!
View Replies!
View Related
Avoid 'function Is Not Available On A Protected Worksheet' Message
I am using the before double click event on a protected sheet. When the event fires the first thing the procedure does is unprotect the sheet, does stuff and then it applies protection again at the very end. Regardless I still get the 'Function is not available on a protected worksheet' message. How can I stop the message? Any ideas? If no standard approach is available does anyone know how I can hook this using API?
View Replies!
View Related
Error '13' Type Mismatch In Function Code
while the code works fine by itself in the sample file, when inserted into my other file which contains another set of algorithms, it generates a run time error '13' type mismatch whenever the 'Hide' button is clicked. this line of the following function: b = x / tpi Function range(x) tpi = 2 * 3.1415927 b = x / tpi a = tpi * (b - Int(b)) If (a < 0) Then a = tpi + a range = a End Function
View Replies!
View Related
Format (highlight) Cells With 'If' Function With 2007
I have a workbook that I am working with and I need to formatting the cells so that they will have multiple formulas and I am not sure how to make it work. Sheet1 Column A is titled 'accounts'. There are 2700 rows under Column A that are being used. Column A is already formatted with a Vlookup formula so I can't add another formula in these cells. What I need to do is highlight many ranges of these 'accounts' cells depending on their account numbers. I need to input this formula in a separate location on the spreadsheet. For example; If 'accounts' is >=110031100000 AND <=110031100099 then highlight those accounts in 'RED'. and If 'accounts' is >=1200454000000 AND <=120045400099 then highlight those accounts in 'green' and so on...I am not sure how to word the if statement. The formula must reside in column D or greater and not in A. Am I going about this the wrong way. What I am trying to do is isolate the cells (accounts) that I have referenced on another worksheet so that I can visually notice any accounts that may have been excluded.
View Replies!
View Related
Change Macro From Static To Relative Location
I have a file that has 114 rows of data. One for each office I'm working with. I need to insert 6 rows in between each of those rows and type text into some of the fields. I recorded a macro (since I don't know how to write one), but it uses a static location for the rows and data, as opposed to a relative location. So, what happens is, I get the same data entered over and over again. Also, even if I get this to work right with a relative location, I still would have to run it 114 times. I'd like to create one macro that would do the entire document. Here's what I've recorded: .....
View Replies!
View Related
Selection Of Range For The 'slope' Function
Here is what I'm trying to do: 1) I have column labels, and row labels on one worksheet which I input into a function. 2) These inputs should be matched with column and row labels, and then input into the index function to get a certain cell - let's call it the event cell. I'm ok with this part. Then: 3) I would like to collect a one column array. The number of cells in this array should depend on an input from a cell in the worksheet, and the final cell in the array should be the column label of the column where I am inputing the formula. 4) The array from 3) should go into the slope function. 5) Then I want to get the second array for the slope function from a fixed column, but the same rows as in the variable first array, and this should input into the function.
View Replies!
View Related
'indexing' Of Files (not Index Function)
I have a folder with about a thousand files in it, each one holds one worksheet. What i need is a new file which lists all these file names in column A and, in column b, the value from cell A1 in each file against the file name, i.e. book1 Apple book2 Banana I can list the files in the folder using dos, but cant rip the a1 cell values out, so I need to open each workbook in turn, record the file name, and then record the value in cell A1 against it.
View Replies!
View Related
How To Use 'Insert Copied Cells' Function In VB
I've been trying to solve this for days. In Excel 2007 there is a 'Insert copied cells' function after copying cells and right-clicking on a cell which basically just inserts all copied cells to your desired location. My question is, how do you call this function in VB, I've used F1 extensively and searched the net and haven't come up with an answer.
View Replies!
View Related
Return Active Cell's Location/row
I'm having trouble identifing a way to return a location for the position of the active cell. I've searched Excel help with "Position, location, return, activecell, etc." and I can't seem to figure this out. I know that it's possible, so that's why I'm on here! ... Ok, say the active cell is currently "F1", and I need the location "F1" to identify the ROW to be used in a formula later, how would I go about that? The current contents of cell "F1"' will be "REPLACE", but I need to change the words "REPLACE" in "F1" and other cells labeled "REPLACE" in column F to the following formula (where the "1" in "A1" is is the current row):
View Replies!
View Related
DATE Function & IF Function (return A TRUE Or FALSE Based On A Date In A Cell)
I am trying to return a TRUE or FALSE based on a date in a cell. for example: if cell A1 = 07/11/2009 I want A2 to show TRUE or FALSE if A1 is 14 days or more behind todays date. I have tried stuff like: =IF(A1=TODAY()-14,"True","False") but it just always says false. EDIT: I have just noticed that if I change the date in A1 to exactly 14 days behind todays, it returns TRUE. So, it does work. Do I need to add a GREATER THAN in there?
View Replies!
View Related
Is There A Don't Count Function?
Right now I have a column of about 300 rows where I want to count how many actual names are in the column. I thought that COUNTA(c2:c313) would work, but it returned 312, while Im expecting it to return about 10. I think that the reason is because all of the cells in this column are pulling data from another sheet using this formula: =IF(Clients!I299="","",Clients!I299). Most of the data are blank cells Since of the rows in this column are empty, so I was wondering if I perhaps there was a function such as "DONTCOUNTIF" which would count the cells which didn't have a certain value, in this case "".
View Replies!
View Related
Function Won't Return Value
I'm trying to get a "simple" function to return a number and not the #NAME?error! I'm writing the following vba code. It's located in the sheet1 vba code area. On the spreadsheet itself, on sheet1, column C, there is data from row 1 down to row 55. I have tried formatting the Column as General, text,etc. Function Asset() As String Asset = Range("C1").End(xlDown).Row End Function When I type into the spreadsheet =Asset() , I get the #NAME? instead of the number 55. When I step through in debug mode, Asset shows Asset="55" , but my function doesn't return it!
View Replies!
View Related
You've Entered Too Many Arguments For This Function
I have this formula in cell Q34; .......... "You've entered too many arguments for this function" Logically I can follow this message but I was under the impression that you could get a maximum of 64 arguments in an "IF" condition. The actual value of Cell P34 is DKK1938.22 so I expect it to return £203.68 in cell Q34.
View Replies!
View Related
Looking Up Data That A Lookup Function Won't!
I have store numbers and store volume groups (one letter indicating their sales volume). This is sorted in somewhat of a table - the volume groups are in row 4 going horizontal, the store numbers are under their respective volume groups going vertical. I have all store numbers listed vertically on another sheet, starting in A2. I want to search for the store number in A2 within the volume group data and return the appropriate group header when it's found.
View Replies!
View Related
Runtime Error '91" Using Find Function
I have a spreadsheet with a large amount of data, and one thing I need to do is Find the maximum value in a column and then select it. I was originally using an If loop to find it, but I would like to make the code more efficient by using this method. My code is as follows: Function FindCells() Dim FindData As Long Dim Row_Number As Integer Range("L5:L2000").Activate FindData = ActiveSheet.Cells(6, 15).Value Range("L5:L2000").Find(What:=FindData, After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Row_Number = ActiveCell.Row ActiveSheet.Cells(2, 14).Value = Row_Number End Function This string of code works fine, but it doesn't find the right value. Cell (6, 15) contains the maximum value but excel treats it as a whole number. If I try to change the data type of FindData to double or to string I get a Runtime 91 error. If I change LookAt to xlWhole I get the same error.
View Replies!
View Related
Color Function: Can't Find Project Or Library
I am trying to utilise the color function code i have previously asked for help with on this site in a new spreadsheet i have included the code below for your reference. I am utilising the following formula to total the numbers in the cells coloured =colorfunction(J1,E1:E26,True) Excel keeps returning the following error 'cant find project or library' Option Explicit Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean) Dim rCell As Range Dim lCol As Long Dim vResult lCol = rColor.Interior.ColorIndex If SUM = True Then For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = WorksheetFunction.SUM(rCell, vResult) End If Next rCell Else For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = 1 + vResult End If Next rCell End If ColorFunction = vResult End Function
View Replies!
View Related
Match Function Doesn't Find Values
I've been using a Index and Match Functions to locate values, What I'm trying to do is as follows: Column A contains x values and Column B contains another value A length is entered at the top of the worksheet, when the x value is greater than half the length the values will be mirrored about the centre point. To do this i'm doing x-(length/2) to give the corresponding value of x on the other side of the length I can do the If functions etc to sort all this out, but the match function isnt working, see attached, it isn't finding values which are in the list, when i use 0 for exact match, i've tried using 1 but it still isn't finding the correct values.
View Replies!
View Related
Analyzer That Lotus 123 Had With It's Function Called Backsolver
I'm trying to replicate the same functionality using Excel Analyzer that Lotus 123 had with it's function called Backsolver. Below is a simple matirx of expenses, that shows a sum total of 3297. I want to change the number 3297 to 5000 and I want the rows and columns to add up to that total based on the same % relationship that existed when this matrix added to 3297 This technique is useful when you are trying to spread changes in a plan and you need to develop placeholders that add up and accross to the new target i.e, 5000 based on the previous targets % relationships that existed when the row and columns added up to 3297 I can use the analyzer, but when it finds its solution it returns negative values as part of the solution. I'm missing something in trying to set the parameters so that it won't return me negative #'s.
View Replies!
View Related
UDF Working When Called From Sub, Doesn't Work As Worksheet Function
I have written a user-defined function that searches for a small range within a larger range. The function requires two input parameters: the range you are looking for, and the range you want to look within. For example, I might look for the string of values in cells A1:D1 in a larger range E1:H20. The function returns the row number in the larger range where the smaller range is found. My problem is this: The function is working fine when I call it from another sub procedure. However, when I try to run it as a worksheet function, I get a "#VALUE!" error. The function pops up in the "insert function" menu, and it prompts for the two input parameters.
View Replies!
View Related
|
TRACKER
