# Custom Function Returns Range And Comment

Jun 20, 2006

I am trying to write a UDF that will calculate a value and return it to the cell, and also create a comment in the cell showing the formula used. Here is an example of the "=calculate(A2,B2)" placed in cell A1

Function calculate(ByRef X As Range,ByRef Y As Range)
calculate=x*y
calculate.addcomment "calculate = " & X & " * " & Y
End Function

the problem commes from the add comment field. I can add a comment to any cell but the cell the formula is called from.

## Setting Range.Formula With Custom Function

May 18, 2009

I recently set up some functions based on Chip Pearson's tutorial for referencing worksheets from Formulas. (http://www.cpearson.com/excel/sheetref.htm)

The problem I'm now having is that I can't use VBA to set these functions in place; it returns a syntax error.

For example, I want I37 on most sheets to have the same formula. So I have the following:

## VBA Custom Function To Sort And Average Two Numbers Of A Range

May 7, 2013

I need to create a custom function that can do the following without modifying the selected range. If I have data in a row, I need a function that can give me the following result:

Original unsorted data:

79
45
23
56
1

The custom formula will not modify the selected range, and after saving it or copying somewhere and sorting it, it will provide the following result: if N or count(range)=5 , and p=1, average(second and third smallest values of the range)=34; and if N=4 (lets say that we errase the number 1 data point in the range), average(third and fourth smallest values of the range)=67.5 So it is basically a sort and then getting the averages of specified row numbers depending on N or count(range) Here is what I have.

I'm missing how to create a variable with the sorted range and how to use an index to refer to the rows I want to average:

Function CSORT(r, p)
Application.Volatile
'rs or something similar that will contain the sorted range r
Dim rs As Range
'Sort rs (The following line one doesn't work, so I need one that does)
rs = Application.Range(r).Sort
Dim p As Integer

[code]....

## Passing A Range To Be Read As An Array To A Custom Function

Jul 25, 2006

code to pass a range say (A1:A3) as an arguement among other arguements to a custom function and then reading this as an array inside the custom funtion

## Return Cell Address That Falls Within A Range Of A Custom Lookup Function

Jul 3, 2009

I am performing a lookup using the populare user defined function nlookup, which does not take a lookuparray argument to find lookup values. But I only want to return lookup values that fall into a certain array. To accomplish this, I figured out that I need a way to specify a condition of the type "if cell address of lookup value falls within range"

It should be possible if I find a way to return the cell address of the nlookup value, but as nlookup is not limited by a lookuparray argument, so I was not able to use the address/index/match that come up in similar questions.

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

## Insert Comment Function???

Feb 11, 2009

I'm wondering if it's possible to use a lookup formula to populate cell comments? In other words, I'd like to put a lookup table into a cell, and if the value is not FALSE, have that Lookup table then pull in certain information into the cell's comments.

## Add Cell Comment Through Macro Or Function

Mar 19, 2007

I need a quick function or a Macro which can add cell comment based on selected cell.
let me explain in detail
Column

a1 Mr A wrote
a2 Mr B Checked
a3 Mr C authorised

etc etc.
now i want the same in Cell B as its cell comments
so after b1 comment will be Mr. A wrote & so on

## If One Function Returns Error Then Use Second Function

Feb 18, 2013

I want a UDF; = OK (f1,f2) each argument being an alternative function. If f1 returns an error message then use f2. Should be easy but I cannot get it to work.

## MIN Function Returns Same Value For Every Row?

Dec 26, 2013

In column D I have the time of the end of my shift. When I try to input =MIN(1;\$D:\$D) in column E, i get the same answer (0,625) for any row, while =\$D:\$D gives correct values. So I guess it has something to do with the MIN function

## SUM Function Returns #VALUE!

Aug 1, 2008

Have a spreadsheet prepared by someone else on older Excel version exported from database. The sum and count functions work fine both vertically and horizontally, but trying to add selected cells returns #VALUE! Attached is small range of 50-col x 2500 row spreadsheet. Original content is highlighted in yellow. See cell E2. I assume the forumula is seeing number content as text. How do I convert the entire spreadsheet to numbers.

## Function That Returns The First Value In An Array

Jan 24, 2007

I am working on a Monthly Vehicle Spread Sheet. One of the outputs I am trying to achieve is an automatic calculation of Mile Per Gallon. To do this, I need to know if there is a function that will return the value of the first entry of a group of cells.

In calculating the miles per gallon, I need to subtract the first gallon amount entry of the total gallons in the month, then divide that number into the difference of the mileage in the month recorded when the vehicles fueled up.

## Hello World Function Returns #NAME?

Nov 17, 2009

I'm a newbie who has a simple "Area()" function that multiplies two numbers. I copied it off the Net as a programming test. I added to a Module of my Workbook using Tools>Macro>Visual Basic Editor. I put it in "Modules>Module1". Problem is, it doesn't seem to be recognized in my Workbook. When I enter "=Area(A1,A2)" into a cell (without the quotes) I get "#NAME?". What am I doing wrong? Here's the function:

## Mid Function Returns Odd Results

Jun 1, 2006

I'm using the following phrase out of a much longer expression to
demonstrate that the return value of the MID function seems to be nothing I
can understand.

This expression:

=MID("6/10x",1,FIND("x","6/10x)-1))

returns 6/10 as text which makes sense. However I was under the impression
that when text in Excel contains numeric characters, it can be interpreted
as a number. So then how does the following expression,

=MID("6/10x",1,FIND("x","6/10x)-1)*1

return the result 38878? Does it still think it's text?

## Lookup Function Returns #N/A Or #REF

Apr 23, 2007

Im trying to do a lookup but when I drag the formula down it returns the wrong values and also returns #N/A or #REF (which I think means it's refering to a wrong range and cant find the data???). How can I do a look up function by dragging the formula down a long column of data eg. 65,000 rows in excel, that returns the correct text. I have attached a simple example- see attached.

## Instr Function Returns Zero

Jun 25, 2007

i have a very basic function using instr as follows:

ins = 0
Dim x As Integer
Dim y As Integer

findst = "["
finden = "]"

x = InStr(ins, oldstrgFd, findst, vbTextCompare)
y = InStr(ins, oldstrgFd, finden, vbTextCompare)

Debug.Print x,y

oldstrgFd in the example is populated with

[ClientName] Planning Workshop
[FeedbackLocation], [FeedbackDate]
[FeedbackPresenters]

I am expecting this the first positions of the square brackets, but x and y both return 0.

## Format Function Returns . (Dot) Instead Of Zero

Sep 18, 2007

when ltoj is less than zero the following code enters a period in the cell instead of zero.

If ltoj < 0 Then ltoj = 0
Cells(r, 10) = Format(ltoj * 24, "##.##") 'writes data in correct format

## Function Returns #NUM! Error

Sep 30, 2007

Is there a way in which you can create an if function or similar to give a reason for why the cell of data has an error.

Basically i have some data in a table, and when a #num! error pops up id like to in the cell next to it give a sentence stating why this has happened.

E.G.

Cell A1 Cell B1
#Num! Number error due to 'x' being too small

Not sure how i can get this to work, i have tried a basic if function but that hasnt worked.

## WEEKNUM Function Returns #NAME?

Jan 17, 2008

I am trying to get the week from a given data (from a cell that has been correctly formatted with date). =YEAR works, =MONTH works, =WEEKNUM does not work. I am using Excel 2003 and I have tried in both my workplace and at home...

I.e., I have a date in C2 (18-05-08), and =YEAR(C2) returns 2008, =MONTH(C2) returns 5, while =WEEKNUM(C2) returns #NAME, and =WEEKNUM(C2,2) does not work at all...

## Max Function That Returns A Column Heading

Mar 27, 2014

Let's say I have data that looks like this grid below. Each color represents a column heading and each number is a data point within that column.

Blue Red Green Yellow
18 27 15 36
56 41 3 22

Can I write a formula that would do the following 2 things with the data in this format:

1. Find the max of the data...simple =Max(...) formula
2. Use the max to return the column heading. In this case the max is 56 so the value I want to return is "Blue"

## Function That Returns MIN (or MAX) Cell Reference

Apr 20, 2014

something that looks like =CELL.MIN(reference)

## Function That Returns Multiple Values?

Oct 14, 2009

I've never quite mastered the idea of passing arguments between subs and functions. I have a project right now where I am writing this same bit of code over and over, for different parts of the macro:

## IF Function Returns False Positive

May 7, 2009

I have calculated the t values then i have three tables for 3 confidence levels, 0.01, 0.05,0.1 in which I compare the t value with the value from the
t-test table. If the t value is larger it returns a 1, if not a 0. This works fine.

To summarise the information I have a final table which I wish to return the highest significance of the given t value.

I am using this
=IF((G53=1),0.01,(IF(G44=1,0.05,(IF(G35=10,0.05,"none")))))

the problem I have is it always returns 0.01 even if g53=0. If i remove the formula from g53 and replace it with a 0, it returns 0.05 instead (even if g44 is retruning a 0).

I have considered copy paste values and doing this but that rather removes the automation aspect of the spreadsheet if I need to make adjustments.

## Function To Returns Biggest Change (+ Or -)

Feb 1, 2010

I have some survey data for 2008 & 2009. I have a column calculating the difference on each question between the two years. I need to show the 20 biggest changes, positive or negative. Which function can I use for this?

## AND Function Returns Zero For FALSE & One For TRUE

Oct 18, 2007

I've been asked to adjust some thing with a worksheet someone else created. On the sheet labeled "Criteria" there is a list of dates and some formulas that use these dates. I can't figure out why the result of the formula is a number, i.e "0"; I thought it is supposed to return "TRUE" or "FALSE". If I insert a new sheet, within the same workbook, and insert the same formula, I get "TRUE" or "FALSE". For some reason, the "Criteria" worksheet doesn't return "TRUE" or "FALSE", it returns numbers. I just need to know why this is happening so I can replicate it in the new workbook I'm creating.

## MATCH Function Returns Unexpected Result

Dec 3, 2013

In the attached file I used =MATCH(TRUE,INDEX(\$B\$2:\$B\$10="",0)) to retrieve the location of the first empty cell.

When using the "Evaluate Formula" tool, it is clear that the position in the array created by the INDEX function is the 6th. Nevertheless, the final outcome is 9, being the last cell in range.

## Function To Indicate Whether An Expression Returns A Non-zero Result (2007)

Jan 28, 2010

I have this COUNTIFS expression buried in a larger formula:

COUNTIFS(Table[C7],Table[C7]&"",Table[C21],">=1")

and it always returns a value of zero or some positive integer.

I'm now looking for a little "function" that I can wrap around this expression that will:

A) indicate any non-zero result as 1 or TRUE

and

B) indicate any zero result as 0 or FALSE

Something like...

AREYOUNONZERO( (COUNTIFS(Table[C7],Table[C7]&"",Table[C21],">=1") )

I'm sure there's a fairly efficient way to do this... but I'm totally stumped at the moment!

## Lookup Function Returns Type Mismatch?

Feb 23, 2014

I get a Type mismatch from this line:

x = Application.WorksheetFunction.Lookup(2, 1 / (Range("G7:P7") = "A"), Range("G7:P7"))

## Find Function In VBA Returns A Date That Is Almost Correct

Feb 22, 2013

Is it possible to see why this code returns a date that is almost correct? Searching for 01.01.2005 I get 01.11.2005 as result. I would like the code to only show the correct date, and if it doesn't exist it should end up showing "nothing".

Code:
Set fstdate = wsOBX.Range("B1:B" & lr1).Find(what:=startdate, _
LookIn:=xlFormulas, lookat:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

## Copy Contents Of Multiple Comment Boxes And Paste In Single Comment Box

Mar 13, 2014

how to copy the contents of multiple comment boxes and paste in a single comment box.

The big picture is that I have a number of cells with numerical values in and text in comment boxes. I want to be able to click a button to copy the contents of the comment boxes and paste them, along with the numerical value from the cell, into a single comment box, ordered by highest to lowest value within the comment box, then delete the original cells and comments.

I am quite new to VBA but have been coping quite well so far with information of the web and analysing recorded macros.