Free Microsoft Excel 2013 Quick Reference

use of morefunc functions in excel macros

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.


Post your answer or comment

comments powered by Disqus
How to use the LOOKUP function in Excel

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

How to use the LOOKUP function in Excel

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

How to Optimize the use of Query Table in Excel 2007 VBA?

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?

I've recently come across a problem using the IF function in Excel, but only on one computer. Normally I put something in like IF(A1 = 0, 0, B1+B2) or some other formula under the flase condition. Now, I can get it working on one computer, but not on another computer, which I need to use for other applications. The problem computer seems to be running the same version of excel, but likes to try and remove the commas. Can anybody offer any help?

Description of limitations of custom functions in Excel

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

Using the VLookup Function in Excel 2007

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

I am trying to use the Vlookup function in a macro but I can't figure out how to write it. I am using a controlled loop to cycle through my data. So for the first entry, my lookup data is in cell A2. The lookup table is located in a sheet called 'Trade Table' and the lookup field is in Column B and value needed is in column C. The results go in column G.

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


Hi, I'm having problems using the count function in excel, I have the
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)))}

I have a macro that takes external data, formats it and performs some calculations. I want to plot the data in series in a chart, but the number of rows for each series varies with each download. The number of rows to be used in the chart is stored in a cell in the worksheet after the data is downloaded. The macro for one of the series to be plotted takes the form:
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.

Hi all

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

When using the "mailto" function in a macro, it allows me enter the
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 can't use the functions in Excel. I can't even type a single letter?! An
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!

hi experts....

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

When using the "mailto" function in a macro, it allows me enter the
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

Hello everyone,

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!

When using the lookup function in Excel 2003, it would be extremely helpful
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.

Hi all,
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.

Hi All,

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!

I'm new to excel but reckon it can help me do a lot of the mundane tasks i've been landed with so - I'm attempting to work out overdue invoices using IF AND function in Excel. The worksheet is one I use for work so cannot be changed - I have to work with what's there. Attached - it's quite big so I've deleeted some of it ...

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!!

This is the first time I'm trying to use a user defined function in excel. I want to use it in the columns I & J. I already have the formulas in column I & J.

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.

Hi,

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?

I'm trying to use these worksheet functions in a macro and I've tried using:

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.

I am trying to use the lower function in VBA. I have tried using both codes below, but neither are working. Any assistance is appreciated. 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


Hi,

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.