Hello all!

I'm writing a macro and I'd like to use one of the functions included in

Longre's free morefunc.xll add-in from http://xcell05.free.fr/

I'm interested in using UniqueValues but I don't manage it to work.

For example, an very simple code like this:

Sub ExtractItems()

Dim i As Integer

Dim list As Variant

Dim DataRange As Range

Set DataRange = Worksheets("Hoja1").Range("$A$1:$B$4")

list = uniquevalues(DataRange)

End Sub

doesn't work at all (plus, I get an error).

Sorry, I forgot to mention that I've already checked that the add-in is

properly loaded.

Can you help me with this?

Many thanks in advance.

I'm writing a macro and I'd like to use one of the functions included in

Longre's free morefunc.xll add-in from http://xcell05.free.fr/

I'm interested in using UniqueValues but I don't manage it to work.

For example, an very simple code like this:

Sub ExtractItems()

Dim i As Integer

Dim list As Variant

Dim DataRange As Range

Set DataRange = Worksheets("Hoja1").Range("$A$1:$B$4")

list = uniquevalues(DataRange)

End Sub

doesn't work at all (plus, I get an error).

Sorry, I forgot to mention that I've already checked that the add-in is

properly loaded.

Can you help me with this?

Many thanks in advance.

- How to use the LOOKUP function in Excel
- How to use the LOOKUP function in Excel
- How to Optimize the use of Query Table in Excel 2007 VBA?
- Problems using the IF function in Excel
- Description of limitations of custom functions in Excel
- Using the VLookup Function in Excel 2007
- Using a vlookup function in a Macro
- Having problems using the Count function in Excel
- Charts: use of Indirect function in macro
- Excel 2002 (10.2) - Using the LEFT function in a macro
- Using "mailto" function in a macro
- I cant use all the functions in Excel,so the program's useless
- use of PROPER function in Macros
- Using "mailto" function in a macro
- RATE Function in Excel - Need Urgent Help!
- Lookup function in Excel
- Bond Yield to Maturity using the Yield function in Excel
- Using the IF equation in Excel for multiple variables
- workaround for exceeding 7 nested functions in Excel?
- How to use User Defined Functions in excel 2007?
- Functions in excel
- Year, Month, Day, Weeknum functions in a macro
- Use of "lower" function in VBA code.
- SUMIFS function in Excel 2007

... searches for in the first vector. Lookup_value can be a number, text, a logical value ... The array form of LOOKUP is similar to the HLOOKUP and VLOOKUP functions. The difference is ...

The array form of LOOKUP is similar to the HLOOKUP and VLOOKUP functions. The difference is that HLOOKUP searches for lookup_value in the first row, VLOOKUP searches in the first ...

Hi,

I have an exisiting excel vba that uses QueryTable. The Macro runs fast before in excel 2003, but when we migrated to Excel2007 the macro's performance became very slow. Especilly when calling QueryTable.Add that uses an external url to get its connection. This line is beingÂ use repeatedly as it pulls information in the url with different inputs.

Is there anything I can use of other than QueryTable to pull records from a website?

Functions can perform a calculation that returns either a value or text to the cell that they are entered in. Any environmental changes should be made through the use of ...

</img></img>

Returns the relative position of an item in an array that matches a specified value in a specified order. Use MATCH instead of one of the LOOKUP functions when you need the position ...

The excel formula is as follows:

I have the macro code started, but can't figure out how to finish it (at least I think it is started correctly)

For i = 1 To n Range("G" & i + 1).Value = Application.WorksheetFunction.VLookup(.Range("A & i + 1"), Next i

following formula which works to look up and sum cells in column D that

are on the same row as entries in column A, which meet a date criteria

set in cells f24 and E24. The problem is that I want to make a

separate formula that counts the number of entries that have occurred

in column D. However, when I try to change the Sum to a COUNT I do not

get the correct number of rows. Any suggestions? Thanks

{=SUM(IF(A$8:$A$1000<=F$24,IF($A$8:$A$1000>E$24, D$8:$D$1000)))}

ActiveChart.SeriesCollection(2).Values = "='133-2 form' !R4C13:R409C13"

In this case, the range of rows for this series would be from 5 to 421. In order to make this vary with the amount of data in the download, I have attempted to use the Indirect function in the statement:

ActiveChart.SeriesCollection(2).Values = "='133-2 form'! R4C13:Indirect(R3C13,FALSE)"

Where r3c13 contains the reference to the last cell in the range. However, this gives an error. Any suggestions? Thanks.

I've been tinkering with ways to include the LEFT function in a macro, but without success.

I'm trying to isolate the first 2 characters in an a cell, and then use that to go to a separate worksheet with the same 2 character name. For instance:

Cell A1 is "AD1234". I want the macro to recognise the "AD" part and go to a worksheet called "AD" and do some stuff (lookups etc).

I thought it would be quite easy but I'm absolutely stumped. I'm going through Excel VBA For Dummies now, but I get the feeling I'm a level below dummy cos I'm just not getting it. Is what I'm trying to do possible?

Any pointers much appreciated, as always.

Thanks

Darren

receipient and subject, but I'm not sure of the coding used to enter text

into the body of the email, in short I need to email the link for the

location of the spreadsheet for the user to click on in the email so it

automatically opens.

cheers,

Mike

information appears stating the command is currently disabled, and that I

need to purchase a licensed copy of Microsoft office? How this happened? my

laptop is brandnew and so as the softwares that are included.I went to

Microsoft help but failed to resolve my problem.Can someone help me sort this

thing out?many thanks!

i just want to ask....

how can i use the PROPER function in macros?....because i want to automatically change those text not in PROPER case as soon as i click anywhere on the worksheet...only columns C and D will be affected...thanks a lot...

receipient and subject, but I'm not sure of the coding used to enter text

into the body of the email, in short I need to email the link for the

location of the spreadsheet for the user to click on in the email so it

automatically opens.

cheers,

Mike

I am building a financial analysis report with a tool called Impromptu Cognos. It can only do simple math (+,-,x,/).

However to calculate our Annual Blended Yield, it requires either a financial calculator using the cashflow function or using the RATE function in Excel. I found this article on Microsoft.com:

http://support.microsoft.com/default...b;en-us;110854

What I am trying to figure out is this:

I have my Nper (36 month terms),

I have my pmt (5202.92)

I have my Pv (-167703.10)

I am able to calculate a monthly rate of 0.61% and multiply that by *12 to get an annual blended yield of 7.32% which is correct.

I need to know how to write this RATE function in simple mathematics in Cognos or Excel. I won't be able to use the FUNCTION itself. Is there a way to decode the function in VB to find out what it's doing. I can write if/then statements in Cognos along with a mathematical equation but I have no clue where to start. Any help would be appreciated.

Thanks!

to have an error appear if the looked up item does not appear in the list.

Example: If I have a small list of students in list A and I'm trying to look

up the corresponding teacher in list B, it returns a teacher for every

student in list A, even if that student is not in list B, and therefore, it

is the wrong information. I would rather have it display an error message,

so that I know that something is missing.

The yield function in excel does not give the right value if I play around with the redemption value(% of par). I have checked this with other available online bond yield calculators such as

http://www.moneychimp.com/calculator...e=calc_bondytm

If I change the redemption value to 50 and the bond price to $50, ideally the coupon rate and the yield should be equal for any time period. While this happens in the calculator mentioned above, Excel gives me a different number. Is this actually a bug or am I missing the point?

Test this in Excel...

=YIELD("6/30/2009","6/30/2022",0.0958,50,50,2,0)

Also test different redemption and price values and compare them with the calculator above.

I have a question regarding how to use the IF function in excel. I have attached a spreadsheet that I have been messing with to understand how best to use the IF function. The test sheet I have attached is very small compared to what I will actually have to do. The idea is that when customers place an order, there are 3 volumes: $1,200 order $2,400 order and $4,200 order. at each volume, the price of the products being sold decreases (the more you buy the cheaper it is). I am trying to use the IF function to calculate the total order sales based on the volume price. Theoretically it would read, IF the total sales of Products A-C is >= 1,200 then use those prices (Vol 1 Prices), IF the total sales of Products A-C is >=2,400 then use the Vol 2 Prices, IF the total sales of Products A-C is >= 4,200, then use the Vol 3 Prices. I am having trouble writing it in a way though that depicts the range between 1,200-2400 & 2,400-4,200. Like >= 2,400 and <=4,200. If you have any ideas or even an easier or better way to do it please let me know! Below is the formula I have but I am not very good at this.

=IF((F9*C15)+(C16*F10)+(F11*C17)<=C4,(F9*C15)+(C16*F10)+(F11*C17),IF((F9*C15)+(C16*F10)+(F11*C17)>=C 4,(F9*C15)+(C16*F10)+(F11*C17),IF((F9*C15)+(F10*C16)+(F11*C17)>=C5,(F9*D15)+(F10*D16)+(F11*D17),IF(( F9*C15)+(F10*C16)+(F11*C17)>=C6,(F9*E15)+(F10*E16)+(F11*E17)))))

Thanks!

In column AA I've created a formula that looks at the current month entered in month in AA2 and then checks to see if invoices are overdue for the preceding months - trouble is there are 12 months that I need to check and the function only allows me to check 7.

Formula:

=(IF((AND(AO3>0,current_month="April")),"Due",IF((AND(AO3>0,current_month="May")),"overdue",IF((AND( AQ3+AO3>0,current_month="June")),"overdue",IF((AND(AS3+AQ3+AO3>0,current_month="July")),"overdue",IF ((AND(AU3+AS3+AQ3+AO3>0,current_month="August")),"overdue",IF((AND(AW3+AU3+AS3+AQ3+AO3>0,current_mon th="September")),"overdue",IF((AND(AY3+AW3+AU3+AS3+AQ3+AO3>0,current_month="October")),"overdue","OK "))))))))

anyone got any work around or can suggest how I can do this more easily using another function? - bear in mind I have no power to change how the file is laid out!!

In Column I :

=((H3/20)/G3)

In Column J:

=IF(A4="","",IF(SUMPRODUCT(--($A$2:$A4&$F$2:$F4=A4&F4))=1,(20-H4)/20,(20-SUMIF($F$2:$F4,F4,$H$2:$H4))/20))

Now here's the user defined function which I've got from my friend which I can use.

Public Function SplitNames(NamesCell As Range)

Dim vecNames As Variant

Dim cell As Variant

Dim yourPcge As String

Dim tmp As String

vecNames = Split(NamesCell.Value, ",")

For Each cell In vecNames

yourPcge = "xx" ' replace with code to calculate percentage

tmp = tmp & cell & " : " & yourPcge & " ; "

Next cell

SplitNames = Left$(tmp, Len(tmp) - 3)

End Function How do I use the formula I've in column I & J in this user defined function in the code-line in italics? Also, how do I use this in the excel I've created.

I have a large dataset with multiple worksheets linked together.Th edat is updated dailt weekly and months,at the moment i am using sumproduct and vlook up to calculate the data .th problem is it is taking so long to calculate which is not what i was expecting.

So, is there anyone who knows of other functions in excel that can be used instead of sumproduct and vlookup to make it run faster?

Dim Year as Integer Year = Application.WorksheetFunction.Year(Range("S3"))where S3 contains the date such as 6/28/2010.

However, this gives me a object does not support this method error. It does not work for any of the other listed functions either although it does work for the Weekday function... Anyone know how I might be able to use these functions in a macro?

Thanks.

If j = 9 Then .Cells(i, j).Value = Application.WorksheetFunction.Lower(Range(FieldOrder(j) & i).Value) Else

If j = 9 Then .Cells(i, j).Value = Lower(Range(FieldOrder(j) & i).Value) Else

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?

Thanks!

No luck finding an answer? You could always try Google.