Free Microsoft Excel 2013 Quick Reference

Hello,

I am trying to display the result of a custom VBA function as a label on a userform. The function is called and takes arguments from three text boxes on the userform. I attached it to the exit parameter. (This may be part of the problem; I'm not sure).

There seem to be two problems, first all the text boxes don't always contain data, but because the arguments are not optional, I get a type mismatch error when the code runs (I tried switching some of them to optional, but it didn't help as you can see from my 'commented code).

The other problem is that even when all the arguments seem to be met (i.e. the text boxes all have data), I still get a compile error that the argument is not optional. This only happens if I try and pass the result to some aspect of the userform. As you can see from the last coded line in the custom function, if I assign it to a range on the worksheet, it functions fine.

Below are the codes. Any help you can provide would be appreciated. I use excel 03 and windows XP.

Note the exit procedure also contains a code that formats the text box to a specified date format, and this does work.

Code:

Code:

Thanks for your help

I am trying to display the result of a custom VBA function as a label on a userform. The function is called and takes arguments from three text boxes on the userform. I attached it to the exit parameter. (This may be part of the problem; I'm not sure).

There seem to be two problems, first all the text boxes don't always contain data, but because the arguments are not optional, I get a type mismatch error when the code runs (I tried switching some of them to optional, but it didn't help as you can see from my 'commented code).

The other problem is that even when all the arguments seem to be met (i.e. the text boxes all have data), I still get a compile error that the argument is not optional. This only happens if I try and pass the result to some aspect of the userform. As you can see from the last coded line in the custom function, if I assign it to a range on the worksheet, it functions fine.

Below are the codes. Any help you can provide would be appreciated. I use excel 03 and windows XP.

Note the exit procedure also contains a code that formats the text box to a specified date format, and this does work.

Code:

Private Sub txtStartDate_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim StartDate As Date Dim EndDate As Date If IsDate(Me.txtStartDate.Value) Then StartDate = Me.txtStartDate.Value Me.txtStartDate.Value = Format(StartDate, "m/d/yyyy") Else: MsgBox "Please enter a date" End If Call CalcDate(txtStartDate, txtYears, txtMonths, txtDays) EndDate = CalcDate lblProjectPeriod.Caption = EndDate End Suband the custom function:

Code:

Function CalcDate(StartDate As Date, Optional Years As Variant, Optional Months As Variant, _ Optional Days As Variant) As Date 'If IsMissing(Years) Then Years = False ' If Years = False Then Years = 0 ' End If 'End If 'If IsMissing(Months) Then Months = False ' If Months = False Then Years = 0 ' End If ' End If ' If IsMissing(Days) Then Days = False ' If Days = False Then Years = 0 ' End If 'End If CalcDate = DateAdd("yyyy", Years, StartDate) CalcDate = DateAdd("m", Months, CalcDate) CalcDate = DateAdd("d", Days, CalcDate) CalcDate = DateAdd("d", -1, CalcDate) 'Range("L7") = CalcDate

Thanks for your help

- Display Result Of Date Function In Label
- Need help creating custom VBA function in excel 2007
- Formulas into custom vba functions
- In excel how to display results of a function and text in the sam.
- Custom VBA Function for Geometric mean
- Variable as result of Divide (Sum) Function
- Is it possible to use custom vba functions in pivot tables calculated fields formulas
- VBA Function Malfunctioning !!!
- Need to know what cell called the custom VBA function?
- Results of my equation and function don't match
- Result Of Address Function In Formula
- How can I go through all the results of a find function?
- Order of Calculation for Functions in Automation Add-In
- Help needed to display permuatation of numbers and automatically doing web site data import
- assign the result of countif to a variable in VBA
- Macro to display result of a calculation in excel userform
- Round the result of IFERROR / VLOOK function
- Problem Copying Results from Kickbutt VBA Function to Different Sheet
- VBA Function Help
- Add a column that has the result of nested "IF" function
- Format lookup result to same currency symbol as source
- Returning the result of a worksheet function to a variable
- Visualizing cells as result of OFFSET function?
- Cell range as a variable in arguments of a VBA function call

I am trying to display the result of a custom VBA function as a label on a userform. The function is called and takes arguments from three text boxes on the userform. I attached it to the exit parameter. (This may be part of the problem; I'm not sure).

There seem to be two problems, first all the text boxes don't always contain data, but because the arguments are not optional, I get a type mismatch error when the code runs (I tried switching some of them to optional, but it didn't help as you can see from my 'commented code).

The other problem is that even when all the arguments seem to be met (i.e. the text boxes all have data), I still get a compile error that the argument is not optional. This only happens if I try and pass the result to some aspect of the userform. As you can see from the last coded line in the custom function, if I assign it to a range on the worksheet, it functions fine.

Below are the codes. Any help you can provide would be appreciated. I use excel 03 and windows XP.

Note the exit procedure also contains a code that formats the text box to a specified date format, and this does work.

VB:and the custom function:MSForms.ReturnBoolean) Dim StartDate As Date Dim EndDate As Date If IsDate(Me.txtStartDate.Value) Then StartDate = Me.txtStartDate.Value Me.txtStartDate.Value = Format(StartDate, "m/d/yyyy") Else: MsgBox "Please enter a date" End If Call CalcDate(txtStartDate, txtYears, txtMonths, txtDays) EndDate = CalcDate lblProjectPeriod.Caption = EndDate End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

VB:, _ Optional Days As Variant) As Date 'If IsMissing(Years) Then Years = False ' If Years = False Then Years = 0 ' End If 'End If 'If IsMissing(Months) Then Months = False ' If Months = False Then Years = 0 ' End If ' End If ' If IsMissing(Days) Then Days = False ' If Days = False Then Years = 0 ' End If 'End If CalcDate = DateAdd("yyyy", Years, StartDate) CalcDate = DateAdd("m", Months, CalcDate) CalcDate = DateAdd("d", Days, CalcDate) CalcDate = DateAdd("d", -1, CalcDate) 'Range("L7") = CalcDateIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks for your help

Write a custom function to calculate the geometric average of the returns listed in cells B6:B15.

Below is the formula for a geometric average:

[(1 + R1) * (1 + R2) * ... * (1 + Rt)]^(1/t) - 1

There are three requirements for your function:

i. Your function should have a single input – the range of cells that contain the return series.

ii. Use a For/Next loop to cycle through the returns in the range provided by the user.

iii. Use an If/Then statement to determine if the returns are entered in decimal or percentage point form. You may assume that values above 1 are in percentage

---------------------------------------------------------------------------------------------------------------

This is my function so far and I keep getting a #NAME error. ANY HELP WOULD BE GREATLY APPRECIATED

Function MyGEO(Returns As Range)

Dim Varcount As Integer

Dim t As Integer

Dim mean As Double

Dim geomean As Double

If Returns >= 1 Then Returns = Returns / 100

Varcount = Returns.Count

mean = 1

For t = 1 To Varcount

mean = mean * (1 + Returns(t))

Next t

geomean = mean ^ (1 / Varcount) - 1

MyGEO = geomean

End Function

Here goes..

Variables that are taken from cells

Dir1

Dir2

Spd1

Spd2

Other variables:

Sin1, Sin2, Cos1, Cos2, Sum of Sines, Sum of Cosines, Avg of Sines, Avg of Cosines, RWD, WD

Formulas:

Sin1 = Spd1*(SIN(RADIANS(CONCATENATE(Dir1, 0))))

Sin2 = Spd2*(SIN(RADIANS(CONCATENATE(Dir2, 0))))

Cos1 = Spd1*(COS(RADIANS(CONCATENATE(Dir1, 0))))

Cos2 = Spd2*(COS(RADIANS(CONCATENATE(Dir2, 0))))

Sum of Sines = Sin1 + Sin2

Sum of Cosines = Cos1 + Cos2

Avg of Sines = (Sin1 + Sin2)/2

Avg of Cosines = (Cos1 + Cos2)/2

RWD = ATAN2(Avg of Sines, Avg of Cosines)

WD = MROUND(DEGREES(RWD), 10) Would like to Calculate WD with cell values

Stipulations:

If Sum of Sines and Sum of Cosines both = 0 Then

WD = 0

If RWD

in excel how to display results of a function and text in the same cell, for

example:

The total is

example:

The total is

Write a custom function to calculate the geometric average of the returns listed in cells B6:B15. Below is the formula for a geometric average:

[(1+R1 ) * (1+R2) *...*(1+Rt)]^(1/t)-1

There are three requirements for your function:

i. Your function should have a single input – the range of cells that contain the return series.

ii. Use a For/Next loop to cycle through the returns in the range provided by the user.

iii. Use an If/Then statement to determine if the returns are entered in decimal or percentage point form. You may assume that values above 1 are in percentage point form.

ANY HELP PLEASE!!!

============================================================================================================================ Function MyGEO(Returns As Range) Dim Varcount As Integer Dim t As Integer Dim mean As Double Dim geomean As Double If Returns >= 1 Then Returns = Returns / 100 Varcount = Returns.Count mean = 1 For t = 1 To Varcount mean = mean * (1 + Returns(t)) Next t geomean = mean ^ (1 / Varcount) - 1 MyGEO = geomean End Function

posts the values to a consolidated spreadsheet.

I need a value that is the result of a sum function (would look like this if

in a cell ***(c106/c92). I have to perform the function and then assign it to

a variable named iValueCMGM. The value of this variable is then pasted into

the consolidated workbook.

I tried this but it didn't work:

iValueCMGM = .Range("C106" / "C92")

Can anyone help?

Thanks!

Thanks in advance

The code of function is as follows.

Function Due(Flatcost) As Single

Const Stage1 As Double = 0.1

Const Stage2 As Double = 0.46

Const Stage3 As Double = 0.69

Const Stage4 As Double = 0.87

Const Stage5 As Double = 1

Select Case Range("A1").Value

Case Is = "PLINTH"

Due = (Flatcost * Stage1)

Case Is = "RCC"

Due = (Flatcost * Stage2)

Case Is = "MESONARY"

Due = (Flatcost * Stage3)

Case Is = "FINISHING"

Due = (Flatcost * Stage4)

Case Is = "POSSESSION"

Due = (Flatcost * Stage5)

Case Else****

MsgBox "No valid stage"

End Select

End Function

I used to write the FLATCOST in cell â€śB4â€ť

And enter the Formula =Due(B4) in cell â€śC4â€ť

It works well, but as I change the Value of cell â€śA1â€ť, the Value of cell â€śC4â€ť containing the Formula doesnâ€™t change!....... To get the change (Refresh) I have to edit it (pressing F2 or Double Click the cell â€śC4â€ť)

What is the matter ?

In option the Calculation is set to Automatic.!

Please Help.

add

=Foo() . When Foo begins to run, I want to know what is the activeCell.

My equation: =(E8/((1/CD8)*(CI8-(D8^2)*CF8)^0.5))

My function, where:

B = CD8

P = D8

D = E8

BSE2 = CI8

ASE2 = CF8

Function Zscore (B, P, D, BSE2, ASE2)

Zscore = D / ((1 / B) * (BSE2 - (P ^ 2) * ASE2) ^ 0.5)

End Function

How do you use the result of the "Address" function as ranges for other formulas?

I have 2 cells A1 and A2, each containing an "Address" function to find the start cell and end cell of a range.

A1 shows the result "$J$6" and A2 "$AB$6". These are working fine:

A1: =ADDRESS(ROW($A6),MATCH(HLOOKUP('cleaned up'!$I$2,pivot03!$2:$2,1,FALSE),$2:$2,0),1)

A2: =ADDRESS(ROW($A6),MATCH(HLOOKUP('cleaned up'!$I$5,pivot03!$2:$2,1,FALSE),$2:$2,0),1)

I want to get the average of this range using cells A1 and A2, but Excel treats the formula as an error:

=Average(A1:A2) shows the error #DIV/0!

Going back one step and putting the formulas within cells A1 and A2 into the average formula doesn't work at all with a colon:

=Average(ADDRESS(ROW($A6),MATCH(HLOOKUP('cleaned up'!$I$2,pivot03!$2:$2,1,FALSE),$2:$2,0),1):ADDRESS(ROW($A6),MATCH(HLOOKUP('cleaned up'!$I$5,pivot03!$2:$2,1,FALSE),$2:$2,0),1)

and with a comma shows the error #VALUE!:

=Average(ADDRESS(ROW($A6),MATCH(HLOOKUP('cleaned up'!$I$2,pivot03!$2:$2,1,FALSE),$2:$2,0),1),ADDRESS(ROW($A6),MATCH(HLOOKUP('cleaned up'!$I$5,pivot03!$2:$2,1,FALSE),$2:$2,0),1)

Any ideas?

Thanks

Can someone explain me how can I go through all the results of a find function (by code, in a cycle) if they are not in the same column or in the same row? If I have a lot of tables with a diferent number of rows placed side by side, how would I check all the results of a find function in this sheet?

Regards,

have the result of one volatile function as one of the input params for

another volatile function (i.e. MyFunc1 is in cell B2, and B2 is an

input param to MyFunc2). The behavior I have noticed is that when I hit

F9, The 2nd Function (MyFunc2) tries to execute first and fails since

the 1st function (MyFunc1) has not yet been calced and only then does

Excel calc the 1st function followed by the 2nd function giving the

correct result. This is dissapointing since Excel knows that one is fed

to the other and should know to calc that one first. The consequence is

that Excel makes an expensive call into the Add-In. It begins to

evaluate the second function and only when it hits a line of code in C#

which tests if the input is valid does it throw a .NET exception which

is expensive. Is there some way to teach Excel the priority, or to

force it to be smarter?

I am new to this forum and I need some help. I appreciate if someone would render me some help regarding my excel application. I need help in 2 areas:

1) I had an Excel Sheet 1 with cell A1 containing 4 number eg. 1234. And In sheet 2 in the same workbook I have a column of 23 datas (they are 4 numbers data (5678,4532,...) in each cell from A1,A2,A3 till A23).

- I need an application to compare sheet1 cell A1 data (1234) with the data in sheet 2. If 1234 or 1234 permutation (eg 3421) is found, display the result (3421) in sheet 1 cell B1.

-If no permutation of 1234 is found, search the 23 datas again for results matching any 3digits (for eg if the list contains 2435 (3 digit the same,1 different then this should also be displayed in the next available row, in cell C1 of sheet 1 and so on)) ( eg if lists have 9423 then 9423 should also be displayed on the next available cell, D1,..)

2) Secondly, I would need help to copy some external data from websites eg www.abc.com/3011. I would need to create a textbox (for user to type the web address) and 2 buttons (1 for copying the data in the website listed on the chatbox and another to copy the next data) The web address (eg www.abc.com/3011) is to be keyed into the text box and when i click on button 1, the data from the web www.abc.com/3011 will be copied into the excel sheet and then when i click on the 2nd button, www.abc.com/3012 (previously is /3011 and when i click on button 2 will copy /3012 and click again will copy /3013, always incremental of 1 when i click button 2)will be copied into my excel program.

Any kind soul please help. If you had a better idea to implement what i am doing please also share with me please. Thank you for reading!

I need some help with excel 2007 please.

How do I assign the result of the countif function to a variable in VBA instead of showing the result in a cell?

something like

loopcounter = "=COUNTIF(C[-6],""?*"")"

cheers

Question: i am designing a model that can be used to calculate payment/usage patterns of customer.

Each customer has a unigue ID and has access to credit facility which runs a monthly cycle.

Payment: If the value on the preceding cell is less than the current cell then a payment has been made.

Usage: If the value in the preceding cell is greater than current cell then usage has occurred.

Please values in a cell could be negative or positive.

Negative implies customer is using credit facility while positive means using his own resource(payment made is more than credit owed).

I need a macro/formula that can be used to calculate both payment and usage over a monthly cycle for each unigue customer.

Here is the formula i used =IF(AND((Data!B20)),Data!C2-Data!B2,0) I need a macro script that can be used to design an interface/form that will link with specific sheets in the workbook.

I need a macro script that can be used to display the result in a userform such that the unigue ID can be picked and the start and end days can be specified from a dropdown menu or any other form feature after which the usage or payment is calculated and displayed.

Thanks

I need help rounding the result of my function. I'd like to round to the nearest 0.1. The cell that I'm entering the function into is E16. Can anyone please help? Here is my current function:

=IFERROR(VLOOKUP(B16,PriceListMM,6,FALSE)*D16,"")

Thank you!! I'm desperate!

Below is the code I have used. The results of the function call return an unknown amount of rows with 10 columns.

VB:As you can see the destination I use is only one cell so it should paste with no problem but it doesn't. The code runs fine when I paste to any row in column A but once I paste to a different column it bugs out. I don't understand why it is creating these errors.mon = month(DateValue("1-" & Target.Value & "-2000")) Call Find_Range(mon, Sheets(6).Columns("A")).EntireRow.Copy(Sheets(3).Range("F10")) Else: Exit Sub End IfIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I'm trying to simplify some of my VBA function codes. For example:

Function func1(x,y) zzz = x + y func1 = zzz * 2 End Function Function func2(x,y) zzz = x+y func2 = zzz * 3 End FunctionIs it possible to not keep typing in the "zzz = x+y" line in each function that needs the "zzz" value?

Thanks,

Jay

I have attempted multiple functions trying to sum column B. From research it appears the result is a number stored as a text. The formula in column B is: =IF(F9

What I need to do:

I want to display a number, with it's appropriate currency symbol, from a table into a different cell on a different sheet.

My problem:

a =vlookup function only returns the number and it loses any currency formatting.

What I need help with: Either of the following.

1. A away to make =vlookup return the value as well as the currency formatting, or

2. A way to extract the currency symbol (which is part of the format) into a new cell so I can then combine it with the values of the cells and esentially turn

$105.22 (with a number Value of 105.22) into

$105.22 (with a text Value of $105.22).

Any help would be greatly appreciated as I am completely stummped.

Thank you.

I want to read the result of COUNTA(A:A) into a variable (Say MyCounta). At the moment, I'm making the computation on the sheet itself. That is, Cell B5 contains the formula =COUNTA(A:A). I then assign the value of cell B5 to the variable by setting

MyCounta = Range("B5").Value

Is there a way to avoid cell B5 and make the assignment more directly?

(I'm interested in a general method for assigning the value of a worksheet function to a variable rather than a specific solution to the COUNTA example given above.)

Thanks,

m

I would appreciate a simple- practical or even visual way to see those cells that are "covered" as the result of OFFSET setting function.

Code shows like this:

-------------------------------------------------------------------------------

Function AssignData(myRange As Range) As Double()

Dim ColNums As Integer, RowNums As Integer

ColNums = 1

RowNums = 1

If IsArray(myRange.Cells.Value) Then

ColNums = myRange.Columns.Count

RowNums = myRange.Rows.Count

End If

Dim tmp() As Double

ReDim tmp(1 To RowNums, 1 To ColNums)

Dim ColIdx As Integer, RowIdx As Integer

For RowIdx = 1 To RowNums

For ColIdx = 1 To ColNums

tmp(RowIdx, ColIdx) = myRange.Cells(RowIdx, ColIdx).Value

tmp(RowIdx, ColIdx) = myRange.Cells(RowIdx, ColIdx).Value

Next ColIdx

Next RowIdx

AssignData = tmp

End Function

-------------------------------------------------------------------------------

If the cells contain pure numerical numbers in selected cell range, it works fine. Problems come out when the data in selected cell range are obtained from formulas: Data won't assign to the array correctly during debugging.

It is really funny thing. If you use another subroutine to extract one element from this array and show it in a cell, it always give you the correct data. But if you watch the variable during debugging, it may give you zero for the same element.

for example, I want to store the cell range "B1:D25" in an array variable MyArray. If you output the element (20,3) in this array, you get it correctly. However, during debugging, the corresponding data may not assign to MyArray(20,3) when you watch it.

So I don't know what the problem is. Please help how i can pass the values in the cell range exactly to the array variable. Thanks

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