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?

View 2 Replies


ADVERTISEMENT

Calling Function From Worksheet With Nonconcurrent Range

Sep 26, 2007

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:

View 9 Replies View Related

Calling A Function

May 1, 2007

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.

View 3 Replies View Related

Calling A Function In A Procedure

Feb 2, 2010

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)

View 3 Replies View Related

Calling A Custom Function Within VBA

Sep 9, 2009

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)

CreateFlexstring = Company & "-" & Cost_Center & "-" & Division & "-" & Geography

End Function

View 9 Replies View Related

Calling Function From Add-in Causes Crash

May 19, 2009

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.

View 3 Replies View Related

Calling Value From Sheet And Send It Back In Concatenate Function

Jul 23, 2014

how to properly write this kind of commands!

v_1 = Sheets("WB_Input").Cells(i, 2) 'It's gonna be a numeric value like 10
v_2 = "E"
v_3 = Sheets("WB_Input").Cells(i, j) 'It's gonna be a numeric value like 12
Sheets("WB_Output").Cells(i, j) = "=CONCATENATE(" & v_1 & "," & v_2 & "," & v_3 & ")"

Thing is once I look at the WB_Output Sheet what I see is an error and the cell shows this: =Concatenate(10,E,12)

So logically the problem is that I need to make it show up as =Concatenate("10","E","12")

Btw, I can't just put v_1 & v_2 & v_3, cause Excel understands "E" as exponential!

View 1 Replies View Related

Excel 2000 :: Macro Calling Unrelated Function?

Jun 9, 2014

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

[code]....

View 2 Replies View Related

Worksheet Change Monitoring 2 Cells & Calling 2 Function

Apr 30, 2007

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

View 9 Replies View Related

Matrix Function For Multiple Value List

Nov 22, 2011

I was not able to accomplish this task:

I have a list of ~19.000 rows with 3 columns:

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?

View 5 Replies View Related

Conditional Formatting: OFFSET Function To Define A Range Inside A SUM Function

Apr 13, 2007

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?

View 9 Replies View Related

Lookup Or If Function: Find The Room Nights And Net Room Revenue Values For The Specific Rate Code In The "MATRIX" Workbook

Apr 7, 2009

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.

View 2 Replies View Related

Excel 2010 :: VBA Sheets Copy Function Works But Then Jumps To Beginning Of Function

Apr 8, 2014

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.

View 14 Replies View Related

Inserting An INDEX,MATCH Function Into A HYPERLINK Function Instead Of Cell Reference

Mar 20, 2009

Another interesting dilemma to solve. Using this formula:

View 2 Replies View Related

Function Returns Value To Cell As Single - Function Is Defined To Return Double

May 14, 2013

Function Haversine has correct value in debugger but in cell it has the same value as Haversine2. Is this a known bug?

Public Function Haversine(lat1 As Double, long1 As Double, lat2 As Double, long2 As Double) As Double
Dim temp As Double

[Code]....

View 9 Replies View Related

Nesting Index / Match Function Within Vlookup Function?

Dec 3, 2013

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.

View 2 Replies View Related

Function To Mirroring Cell Relative To Function's Location

Jul 17, 2008

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 13 Replies View Related

Nested IF Function Error (entered Too Many Arguments For This Function)

Nov 25, 2008

I keep getting the "You've entered too many arguments for this function" error.

Here is the formula:

=IF(B15=D40,E40,"",IF(B15=D41,E41,"",IF(B15=D42,E42,"","Invalid Shipping option")))

View 6 Replies View Related

Nesting Ceiling Function In IF Function - Conditional Formatting?

May 13, 2013

I am trying to nest an IF function with a CEILING function. If C10 is < 3.5, make it 3.5, however, if C10 > 3.5, CEILING (C10, 5)

right now it looks like:

If (C10

View 1 Replies View Related

Force Function To Use Row Relative To Cell Housing Function

Mar 2, 2008

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 9 Replies View Related

SUMIFS Function In 2007 (range To Sum, Range To Evaluate, Evaluation Criterion)

Sep 8, 2009

I’m trying to use the SUMIFS function in Excel 2007 to evaluate the following formula:

{=SUMIFS(range to sum, range to evaluate, evaluation criterion)}

The range to sum is A1:A10, the range (dates) to evaluate are in B1:B10, and the evaluation criterion is that the date is before 31/10/1999.

So my formula looks like this:

{=SUMIFS(A1:A10,B1:B10,”<31/10/1999”)}

This works fine, but how do I refer the 3rd argument in the function to a date in a particular cell rather than typing in the date specifically?

View 2 Replies View Related

Function To Sort A Range By The Values In A Specific Column And Return The Range

Mar 25, 2009

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

View 9 Replies View Related

2007 Right Function With Embeded Find Function

Mar 26, 2009

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

View 2 Replies View Related

Concatif Function Breaks Beforedoubleclick Function Just By Being There?

Nov 19, 2009

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

View 5 Replies View Related

Scoping Function As Public - But Not Available As Worksheet Function

Sep 24, 2012

I have a user defined function in one VBA module that I'd like to call from another module, so I would normaly scope it as "Public".

However, as I don't want it to be available to Excel as a worksheet function, I would also normally scope it as "Private"

Is there a way to have a function that is available to other VBA modules in the same project, but not to Excel as a worksheet function?

View 5 Replies View Related

Show Insert Function Dialog Pre Set To Function

Sep 14, 2006

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)

View 4 Replies View Related

User Defined Function Function Tooltip

Oct 9, 2007

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?

View 9 Replies View Related

Use Of Range In An If Function

Jun 28, 2006

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

=IF(N5<9,IF(N5>2,1,0),IF(N5<16,IF(N5>8,2,0),0))

View 9 Replies View Related

Run A Function From A Range

Jul 28, 2009

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.

Is this possible: .....

View 9 Replies View Related

Sum Function For Range

Jun 26, 2007

ActiveCell.Formula = "=- SUM(" & MyRange.Columns(1).Address(False, False) & ")" & "+R[1]C[1]"

Why isn't is working ?

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved