Calling Function That Acts On Matrix Range Within Another Function
Jul 23, 2013
I have a function
VB:
Function f1(Matrix As Range)
'Does something and returns f1 = a double
End Function
And a second function which defines and constructs a matrix of doubles to use as an argument in f1 to return a double:
VB:
Function f2(dD As Double)
Dim MatrixRed() As Double
Redim MatrixRed(1 To dD, 1 To 10)
For i = 1 To dD
For j = 1 To 10
MatrixRed(i, j) = i * j
Next
Next
f2 = f1(MatrixRed)
End Function
I get an output error (#VALUE). I think it has something to do with MatrixRed not being a range anymore?
I have a User Defined Function (for finding the next minimum value in a range) that is not working for nonconcurrent ranges when called from within a worksheet. It returns #VALUE!
This works: =nextmin(A11:A17) This does not work: =nextmin(D13,E13,F13,F16)
However calling the function from within vba works:
I guess I just don't understand the basics of calling a function. I have the following function in a standard module:
Function CleanUp(ws As Worksheets) ws. Range("A2:P100").ClearContents End Function
I have this code in a worksheet module:
Sub Trying_to_call_a_function() CleanUp (ThisWorkbook.Worksheets("Month End")) End Sub
But when I run the routine that calls the "CleanUp" function, I get the message: "Object doesn't support this property or method." I have tried changing the Function to this: CleanUp (ws as Worksheet) [instead of (ws as WorksheetS)]. Yet, this does not change anything.
I need to extract the filename “My Excel File” from Worksheets(“Sheet1”).Range(“A1”), whose value = C:Documents and Settingsuser1DesktopMy Excel File
I found on Chip Pearson’s site a Function TrimToChar which, using SearchFromRight and a TrimChar of “” will trim OFF the filename “My Excel File”, leaving the Path. But, I think I could use his function to tell me the number of characters in the Path and then use that to extract the remaining characters from the total character length (Mid Function)
Problem is, I don’t have a clue how to call a Function in VBA to work on Worksheets(“Sheet1”).Range(“A1”). How do you set the InputText , TrimChar, & SearchFromRight?????
(eventually, this will be in a loop, where I extract the filename from a Dynamic Named Range (list) in Column A)
I have created a custom Function in excel. I would like to create a custom button so I can run this funtion with a Click of a button unstead of typing it in or clicking on the insert function button. I am not sure if this is possible to have VB call a custom function. Below is my VB for the Custom Function. I basically want the custom button to open the formula in excel.
Public Function CreateFlexstring(Company As String, Cost_Center As String, _ Division As String, Geography As String)
I have an add-in that has one function in it. We plan on adding more, but not until we figure out what's happening. The one function is a Select- Case statement...send the function an alpha code and it returns a numeric account number based on the alpha. About 20 "cases" in total. The add-in loads fine and the function works fine from a spreadsheet cell. However, we're getting differing results when we try to access the function from VBA code. I've been able to duplicate this several times on three different computers on our network.
The VBA code will work fine, several times and then on the 4th or 5th attempt it will cause Excel to crash ==> "Microsoft Office Excel has encountered a problem and needs to close. We are sorry for the inconvenience." If I " recover" the crashed file, comment out the line to call the function, run the program again it works. Then I can un-comment the line, re-run the program and it works fine again for 4 or 5 more attempts. I've tried running the Repair tool already. I've also copied the code out of the spreadsheet and started a new file. The same errors occur...works 4 or 5 times and then crashes the program. The add-in and the spreadsheet exists on networked drives.
the macro works fine until it executes the paste values. At that point, the macro jumps to the "CountThem" function which is located in another workbook. The data that I am copy/pasting is in no way connected to any cells that are using that function. Although, other values in the workbook are passed down from data that uses that function.
I am still in the dark ages using Excel 2000.
This is the code for my macro.
Code: Sub Current_to_Raw() ' ' Current_to_Raw Macro ' Macro recorded 2/12/2014 by ' ' Range("N14").Select
Is it possible to use the Worksheet_Change command to monitor 2 different cells in the same worksheet and call a different procedure depending on which cell is changed. i.e. if cell A1 is changed do this, if cell a2 is changed do that
Code: A B C 20 100monkeys 1 1 10avenue 0 1 10avenue 0 2 10avenue 0 1 10avenue 0 1 10avenue 0
[code]....
I need to write a function, that outputs "1" in C for only biggest number in A, if it is above or equal 20 for each list of values in B. Is this posseble with Matrix-Functions or should I look into VBA?
In Mr Excel's Pod Cast on April 12th, he showed how to use the OFFSET function to define a range inside a SUM function. Then he had Conditional Formatting that would highlight the range that was being summed. Can anyone tell me what the formula would be inside the Conditional Formatting dialog box to get the OFFSET range to have a certain format?
I am having a bit of a problem creating a formula for this report that I have to update every week. I need to be able to find the room nights and net room revenue values for the specific rate code in the "MATRIX" workbook.
On "CURRENT REPORT," I need D4 to pull up the value on "MATRIX" that equals "Room Nights" in column H and "CONABC" in column J. The same thing needs to happen for F4 but with "Net Room Revenue" in column H. The full report has about 500 of these codes in column J, and I need a formula that I can copy easily and will not be affected if codes are added or removed. This is super last minute - I need to finish this report by tomorrow morning, so take a look at the attachments.
I'm having trouble using the worksheet copy command in a VBA subroutine. I have the following line in my code:
[Code] ........
When I step through my code and execute this line, the sheet is copied as expected and put in the correct place, but then instead of the next line of code being highlighted, the pointer jumps to the first line of a function (in a different module) in my code.
let me start by saying that I know an example workbook would be useful here, but the part I'm struggling with is the [managementroster.xlsm] file, and there is A. no way I can release it to the internets and B. its so huge/complicated I couldn't even begin to reproduce a portion of it, scrubbed of data, and hope to maintain its functionality in a meaningful manner.
[Code]....
This formula checks a staff number on this spreadsheet, and then goes and looks at the staff number on the roster. Once found, it returns that staff members roster, but changes any manager codes in the MRC list to Mgr, and changes all other roster codes to Free.
I now need this formula, before altering roster codes to Mgr or Free, to only return codes that are a match for another table (or after really. I don't particularly care, so long as only codes are shown that match data from another table). I think an index/match function would do the trick, but this forumula is already at the edge of my excel ability, nesting another function within it is completey beyond me. The relevant cells for the index/match function would be:
This first Match function targets the column. $E3 is the date required, $BA$1:$DN$1 is the range the dates are entered in Match: Lookup value = $E3 Lookup array = '[ManagementRoster.xlsm]Vacancies!'$BA$1:$DN$1 match type = 0
This second Match function targets the row. $A$4 is the department name, $B$434:$B$452 is the range where all departments are entered
Match: Lookup value = $A$4 Lookup Array = '[ManagementRoster.xlsm]Vacancies!'$B$434:$B$452 match type = 0
Index: array = $BA$434:$DN$452
So I think my final function is
[Code] .....
But I have absolutely NO idea where it would fit within my first formula, or how to code it so that my original formula only reproduces results that are found in both sheets, or anything.
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?
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.
I was just recently forced to create my first UDF and after how well it worked I now am very interested in learning more. I am trying to create a function to sort a range by the values in a specific column and return the range. I know this should be really simple but for some reason my code dies whenever it gets to my inner-most loop. I need to use this in a larger function but for now this is my only question. I did find that Excel 2007 has built in Functions for this but my company still uses 2003.
My Public Function SortRange(rngToSort As Range, valCol As Integer) Dim Swapper As Variant Dim i As Integer, _ j As Integer, _ k As Integer
For i = 1 To rngToSort.Rows.Count For j = 1 To rngToSort.Rows.Count - i If rngToSort(j + 1, valCol) < rngToSort(j, valCol) Then For k = 1 To rngToSort.Columns.Count Swapper = rngToSort(j, k) rngToSort(j, k) = rngToSort(j + 1, k) rngToSort(j + 1, k) = Swapper Next k End If Next j Next i SortRange = rngToSort End Function
I have a range of cells, for this example I will use 2.
Cell E17 = 77/170 Cell E18 = 8/9
Using the following formula: =SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18)))
This bring back an #VALUE! Error as the second part of the formula keeps picking up "/9" however the first part works fine, displaying "170"
Now if I use: =SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18)-1)) It all works. The problem is that I need this to be automatic using the above way means having to add a "-1" to every formula for a cell with only 1 char to be added.
Using the formula: =SUM(RIGHT(E17,FIND("/",E17)-1))+SUM(RIGHT(E18,FIND("/",E18)-1)).....
I have a tracking sheet (attached) that has many functions, but I'm having trouble with two of them. First of all, I have a Worksheet_Change event set that when a cell is cleared, it fills the cell with a formula to use the record above it as a default value if another cell is equivalent. This worked when I first wrote it, but now it seems to break every other time I use it. When assigning the formula it returns a Method Default/FormulaR1C1/Offset failed error message. It seems to be different for each one, each time and I can't figure out what the problem is.
The other problem I'm having is that I wrote a BeforeDoubleClick event to expand or contract any given record, or series of records. this too worked when I initially wrote it, but now is only hiding one row when it should be hiding eight or more. I'm not terribly versed in VBA and totally baffled as to why I'm having these problems. As I side note, any ideas to clean up any of the code and make this run smoother (as ther will be many more series added when complete).
A most of time I'm using VLOOKUP function. I want to call specially this function by pressing custom button (w/o pressing "Insert Function" and choosing VLOOKUP)
when you type in a builtin function in the formula bar such as =DAY(), a small callout box appears below telling you the syntax - in this case "DAY(serial_number)". Is it possible to achieve this for a user defined function?
I have a nubmer of days that needs to fall into a certain week number for pricing calculations. If the number of days is 3:8 then it is 1 week; 9:15 is 2 weeks; and 16:21 is 3 weeks. Right now the function is able to correctly calculate week 1 and week 2. If I try to insert the week 3 function, I get an error that there are too many arguments. "N5" is the cell with the number of days. I'm not very advanced with excel (it took me all morning to get this far).
I am still trying to skin that cat with my range problem. Not knowing VB, I can not write a macro (script) to do this, so, so far, I am stuck with trying to to this with a function.
