Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


VBA Function Not Working

I copied this function from a VBA book and I get a Error 91 "object variable or with Block variable not set"

I have made sure that Microsoft scripting is referenced.
any clues would be appreciated.
I might add that this is a follow up on a thread I discussed with "Datasmart", thanks to John for getting me started.

Function BrowseForfolderShell() As String
Dim objshell As Object, objfolder As Object
Set obshell = CreateObject("Shell.Application")
'Uncomment next line to start at desktop
'Set objfolder = objshell.BrowseForFolder(0, "Please Select a Folder", 0, 0)
Set objfolder = objshell.BrowseForFolder(0, "Please select a Folder", 0, "c:")
If (Not objfolder Is Nothing) Then

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Trim Function Not Working
This Trim funcion is not working for me for some reason. I have attached a sample file. Can someone help me with this? You can test the macro to see. I just want to delete the trailing blank characters on Column A. Here is the code.

View Replies!   View Related
Date Function Is Not Working..
I have set of data in which i want to put filter Milestones Type <> Milestone
and a filter Actual date is greater than or equal to 30 days ago and is less than or equal to today.

View Replies!   View Related
Array Function Test Not Working
Ok, I'm sure that there is something really obvious here, but I'm just not seeing it....

Test Data starting at cell A1:

View Replies!   View Related
DMIN Function Not Working As Expected
I'm new to the DMIN function and I'm having a problem.

Cell A2: Date (rest of column is date format) (heading for the column)
Cell A2: Credit (rest of column is acctg. format) (heading for the column)
Cell A2: Debit (rest of column is acctg. format) (heading for the column)
Cell A2: Balance (rest of column is, equals balance field from previous row + credit - debit for current row, acctg. format) (heading for the column)
Column E: Notes (rest of column is generic text) (heading for the column)

Cell K1: Date (the text "Date")
Cell K2: > TODAY() (the text "> TODAY()") -- maybe this should be ="> TODAY()"?

Cell H2: =DMIN(A2:D1000,"Date",K1:K2)

I'm trying to get the minimum balance for the Balance column where the date is greater than today (lowest balance that I have in the future, which helps me see if I will be overdrafting).

I'm getting a value of 0 in H2. I have also tried =DMIN(A2:D1000,1,K1:K2) and get the same result.

View Replies!   View Related
Calculate Working Days Function
I have just built a spreadsheet to calculate training hours amongst other things. I have used the function NETWORKDAYS which calculates the number of working days between two dates. This works fine on my laptop which has the analysis tools pack installed. Unfortunately my work place IS policy wont allow the analysis pack to be installed so need to find away around this. Does anyone know a formula that will perform the same function as the NETWORKDAYS. THis is crucial to the accurate calculation of training hours.

View Replies!   View Related
>>> Sound User Function Not Working
I attempted to create a user function as per the link that VBA Noob posted in this post

I get #Name error. I placed the wav file Applause.wav in the same folder as the workbook. What did I do wrong?. Also, assuming I get this problem sorted out, how would I represent the condition "pass" or "fail" as in cell B3 in the Alarm formula? Would it be; Alarm(B3,"Pass") or Alarm(B3,"=Pass") or something else?

View Replies!   View Related
Aggregate Function On Vlookup Array Not Working
I am trying to create a single cell formula that returns the min value of an array returned from a vlookup function. This is part of a more complex solution that I am trying to implement, but I think I have narrowed my problem down to this issue, so I have created a very simple example to demonstrate.

Cells A1 to B5 contain a lookup table:

table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}

LetterNumber D4 C3 B2 A1

Cells A7 to C8 contain the input values:

table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}..................

View Replies!   View Related
Validation And Lookup Function Are Working Weird
There is a range of quarters and relevant dates like from and till - means if you have 1 Q 2009 => it means from 01.01.09 till 31.03.09. I would like to have following in my excel. There is a validation for selecting Q from list, once you select Q, there is LOOKUP function displaying from and till dates. But however it's not working, in same cases it's working, but in most cases the from/till period is not relevant to selected quarter.

View Replies!   View Related
Excel 2007 Net Working Days Function
I am trying to use the NETWORKDAYS function in excel to calculate the working days for my company. the problem is I m located in UAE and here Saturdays and Fridays are official days off for my company. Now the problem is that the excel have this built in function that Sunday and Saturdays are weekend days. I want to customize the function for my local days off for my company.

View Replies!   View Related
Working Day Function: (Day + Next 1st Working Day)
I was looking for a final result as follows

21-Aug-09 + 1 = 24-Aug-09 (Day + next 1st working day)

21-Aug-09 + 3 = 26-Aug-09 (Day + next 3rd working day)

View Replies!   View Related
Reformatting Date Into Number. Reformat Cell Not Working. Function Maybe?
I am rewriting functions for my companies inventory spreadsheets. Excel for some reason took the item codes and formatted them into date format. Right now it is in 10/01/1902 format, and it needs to be in 10-1902 format. I've tried reformatting the cells but so far I haven't found one that works.

View Replies!   View Related
UDF Working When Called From Sub, Doesn't Work As Worksheet Function
I have written a user-defined function that searches for a small range within a larger range. The function requires two input parameters: the range you are looking for, and the range you want to look within.

For example, I might look for the string of values in cells A1:D1 in a larger range E1:H20. The function returns the row number in the larger range where the smaller range is found.

My problem is this: The function is working fine when I call it from another sub procedure. However, when I try to run it as a worksheet function, I get a "#VALUE!" error. The function pops up in the "insert function" menu, and it prompts for the two input parameters.

View Replies!   View Related
Working With NamedRanges In VBA
This might be elementary, but I cannot get it to work.

I need to name range in VBA, work with the named ranges afterwards and change the names. I have split it into e couple of questions below.

1. Naming a range
By using 'Worksheets("Ark1").Range("C4").Name = Worksheets("Ark1").Range("C3").Value' I named cell C4.
This worked fine (but it is the most appropriate method?).

2. Retrieving a name
By using 'Worksheets("Arka").Range("C4").Name' I try to retrieve the name for cell C4, but this does not work for me. In stead of the expected name I get '=Ark1!$C$4'.
How do I retrieve the name (in this case 'Test')?

3. Renaming a range
The next thing I need to do is rename the cell. How do I go about that?

View Replies!   View Related
Get Vlookup In Vba Working
I have 3 sheets ("zip", "report", "ches") in a workbook

I want "ches" column A to be filled with data from "report" column D if there is a match of "zip" cell A12 from "report" column S

I keep getting the error
"unable to get the Vlookup property of the WorksheetFunction class"

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRange As Range
Dim myRange1 As Range

Set myRange = ActiveWorkbook.Worksheets("report").Range("A1:S65536")
Set myRange1 = ActiveWorkbook.Worksheets("ches").Range("a65536").End(xlUp).Offset(1, 0)

myRange1.Value = Application.WorksheetFunction.VLookup(ActiveWorkbook.Worksheets("zip").Range("a12"), myRange, 4)
End Sub

View Replies!   View Related
VBA-code Not Working
I have the next VBA-

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("E:N")) Is Nothing Then
If Range("E34").Value = 0 Then
Application.EnableEvents = False
Range("G8").Value = Range("G8").Value + 1
If Range("J34").Value = 0 Then
Application.EnableEvents = False
Range("G9").Value = Range("G9").Value + 1
Application.EnableEvents = True
End If
End If
End If
End Sub

The code don't work. What's wrong with it?

View Replies!   View Related
VBA Code Not Working As Intended
Why is the following code not running the macro the number of times I type into the box?

Sub Macro10()
'Sub RunABunch()
Dim ans As Variant, i As Long
ans = Application.InputBox("Enter a Number of loops", Type:=1)
If Not IsNumeric(ans) Then
' user hit cancel, so exit sub
Exit Sub
End If
For i = 1 To CLng(ans)
' do your work here
Next i
' Macro10 Macro
Cells.Find(What:="Chief", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 2).Range("A1").Select
End Sub

I didnt write the loop and input portion of the code, I just recorded the macro and added that bit of code from the net.

View Replies!   View Related
Vba Working With Columns By Header Name
I am looking for a better solution working with columns in VBA.

Currently, I refer to columns by their letter or # in my VBA code.

For example:

Columns("AW:AW").FormulaR1C1 = "=+RC[-8]"
I have a scenario where I have to work with a dynamic data source that scrambles columns around (column is not always in the same position on the spreadsheet). Currenct...Also, there may be missing columns or new columns that need to be recognized.

What I would like to do is refer to my columns by header name.

For example (PSUEDO CODE):

If column ("MTD BUD") or ("MTD REV") do not exist Then
Goto ErrorHandler
Columns("MTD REV").FormulaR1C1 = "=+RC[MTD BUD]"
End if
Would the best way to go about something like this be to create an array with column header names that I can run things against? How would this look?

(column headers are always in row 2)

View Replies!   View Related
Working With Pivot Tables Using VBA
I am working with VBA to create a pivot table, and have done just fine so far. However, I need to create a two buttons that will run the following macros: 1. A macro that will remove the selected header (either row or column) from the pivot table.

2. A macro that will put the removed header back into the pivot table.

I need to be able to click on the header (whether its the row or column header) and then press the button to remove it from the table. The second button should then add that header back into the table. My code for creating the table is fine, I just need to work out the buttons. Below is what I have so far. I was think that if I had variable for the header name it would be able to tell which header to remove (so I used Set iField = ActiveCell.Value), I also tried ActiveCell.Text. All I need to do is put the text in the selected field into the PivotFields range to make it hidden. However, I keep getting an error (Compile Error: Object Required) on the line Set iField = ActiveCell.Value. Below is my current code.

Sub PivotTable()
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Pivot Table Data'!R1C1:R1892C7").CreatePivotTable TableDestination:="", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Product", _
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sales").Orientation = _
ActiveWorkbook.ShowPivotTableFieldList = False
End Sub..................................

View Replies!   View Related
VBA Macro Not Working When Workbook Is Protected
I wish I could have been more specific with my Title. This is what I am encountering. I used the McGimpsey code for generating a unique sequential number every time a workbook opens. I tailored the code to fit my needs and stuck it into my invoice sheet. For the longest time I was unable to make it work until I moved the code from a template to a standard workbook. Then I was able to make it work as long as the sheet was unprotected. I realized that to get the code to work I had to then unprotect the cells that the code was writing to. Now that I've done that I am getting errors in the code that weren't there before, rendering the macro useless. The code I am using is:

View Replies!   View Related
VBA Sorting Nth Range Not Working
I have a values in column C of stuff1, stuff2 and on down.

I have a value in cell A1.

I want to start in C1, and every Nth, where N is the value in cell A1, select and paste into column H.

My problem is that the selected cells don't start in C1, in fact, based on the value in A1, it starts in different cells in column C. AND, if i press the button twice, the returned values are different.

View Replies!   View Related
Vba Copy Paste Between Workbooks Not Working
Why isn't my copy and paste working between workbooks? We use excel 2000. I've resorted to using a lot of selection because I can't seem to figure out what is going on otherwise...

I'm sure this should be much simpler...

Sub PortData()
Dim wbA As Workbook, wbB As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet, ws5 As Worksheet, ws6 As Worksheet
Dim sPath As String, sName As String, bTextSwitch As Boolean
Dim nLast As Long
Dim i As Long

View Replies!   View Related
VBA Code Not Working When I Protect The Sheet
I have used the below code to make hide/unhide some columns in my excel sheet. It works fine. Now, I need to protrect the sheet except the coloured cells. So I unlocked the cell by clicking the property of the colored cells & then did this :- tools- protection-Protect sheet - protect unlocked cells. But after protecting this sheet the macro code of hiding/unhiding of the columns are NOT WORKING & Errors is coming. I have used the below code:-

View Replies!   View Related
Handle Quotation Marks Within Text VBA Is Working On
I have a macro that imports xml and then allows the user to update certain fields. The XML contains many quotation marks and inbound I deal with this by replacing " with ' using Find & Replace.

But before exporting the XML again I need to replace the ' with " but am stuck on how to do this.

For instance I'm trying the following with no success:

Cells.Replace What:="'", Replacement:=""", LookAt:=xlPart" _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

View Replies!   View Related
VBA Noob Multiplication Isn't Working With Decimal
I just started using VBA and WOW at the possibilities!! I have some data I am trying to calculate and decided upon researching VBA that this would be the easiest way to do what I need to. The code goes through no errors but D2 is .0001 and N2 is 12000 and the answer I get when I multiply the 2 through VBA is 0.000 however if I change the number in D2 to 2 then I get 24000. I tried the help menu and possibly this is because I didn't do something right with the decimal?

View Replies!   View Related
Write The VBA Function Through The Function Wizard
I have written a VBA program and using in Ms.Excel. The problem is when I try to write the VBA function through the function wizard it works but when I write the function in excel worksheet cell, it doesnt execute. I do not now the reason.

View Replies!   View Related
VBA If Range Value = Something AND Another Cell Has No Entry- Prompt The User To Enter Data- My Code Is Not Working
I am trying to display a message box for the user if there is "agency" in cell o8 but nothing in p8... I tried the following code but it doesnt work..

If Range("o8").Value = "Agency" And Range("p8").Value = "" Then

MsgBox "Please provide name of agency in cell p8"

Sheet9.Shapes("cross").Visible = True


View Replies!   View Related
Changing A VBA Function Into An IF Function
i am using the following code to create a function in excel

i have to put the worksheets onto mac's so i cannot use a macro

can anyone help me change this into an iF function

load is cell D9 and the factor is in cell E9

Function Pmax(Load, Factor) As Double
Select Case (Factor)
Case "0"
Pmax = ""
Case "1"
Pmax = Load
Case "2"
Pmax = Load * 1.09
Case "3"

View Replies!   View Related
Macros That Were Working In Excel 2003 Are Not Working In 2007
I have a workbook in excel 2003 which I had been running the following macros (listed below). We recently upgraded to Excel 07, and neither are working. When I try to run them, the "debug" option highlights the following line in the sort macro "Range("A2:z" & lastcell).Sort key1:=.Columns(1)". This is driving me crazy, as the macros worked perfectly under the older version of Microsoft. Is there an issue with crossfunctionality between '03 and '07'.

Private Sub Worksheet_change(ByVal target As Excel.Range)
If target.Column = 1 Then
ThisRow = target.Row
startRow = 1
i = 1
Set ws = ActiveSheet
maxRow = Cells.SpecialCells(xlLastCell).Row
maxCol = Cells.SpecialCells(xlLastCell).Column
ActiveSheet.UsedRange.Interior.ColorIndex = xlNone
Do While i

View Replies!   View Related
Function In VBA
why the below code does not work?

Sub AnsiSub()
Selection.Formula = Value(Trim(Substitute(A3, char(160), char(32))))
MsgBox ("Complete")
End Sub

View Replies!   View Related
VBA Function To Sub
I have a user-defined type as follows:

Type DocketIdent
cStore As String
cSuburb As String
cPostcode As String
cCheckOutID As Integer
cDatePurchase As String
cTimePurchase As String
cInvoiceNo As String
cNoOfItems As Integer
End Type

I have a function that uses a DocketIdent variable and assigns values as shown above to it. i.e. cStore, cSuburb etc.

I'm trying to return these individual Docket details from a Function to a Sub procedure.

Is this possible and would I have to return each field of the type individually?

View Replies!   View Related
Function Instead Of One Within Vba
I try to use a worksheet function instead of one within vba.

Here's what I want to do:

Return the value from a cell within a vertical range named "Phases Active." The value to be returned is the one in the same row as the non- blank cell 1 row to the left of this range. So, I can use "x" to make a selection of the range basically. VLookup, right?


View Replies!   View Related
VBA Round Function Vs Worksheet Round Function
I don't know if there is a setting I'm missing or I'm going mad but when I use the round function in VBA it doesn't round.

I am using Excel 2000. See the example attached.

In the cell A2 I have a value 0.525, cell B2 has a formula "=round(A2,2)" which = 0.53, but cell C2 is assigned via VBA ie
Sheet1.Cells(2, 3).Value = Round(Sheet1.Cells(2, 1).Value, 2)

and the result is 0.52??

View Replies!   View Related
CountA Function Using VBA
How can I make it work ?

Sub test2()
Dim Wbk, aaa, bbb
Wbk = "\server1folder1file1.xls"
aaa = Application.WorksheetFunction.CountA(Workbooks(Wbk).Sheets(1).Range("A:A"))
bbb = MsgBox(aaa)
End Sub

View Replies!   View Related
VBA GPA Function
Currently I am using this formula =AVERAGE(CHOOSE(MATCH(A1:E1,{"A+","A","A","B+","B","B-","C+","C","C-","D+","D","F"},0),4,3.8,3.6,3.4,3.2,3,2.8,2.6,2.4,2.2,2,0)) to calculate the GPA's and it works fine. What I am trying to do is to wite a function for GPA's and I am coming up short. I am still trying to learn VBA can someone please guide me. Thanks in advance Stephen.

Function gpa(r As Range) As String

On Error GoTo Fail

For Each cell In r
Select Case UCase(Trim(r))

Case "A+": gpa = 4#
Case "A": gpa = 3.8
Case "A-": gpa = 3.6
Case "B+": gpa = 3.4
Case "B": gpa = 3.2
Case "B-": gpa = 3#
Case "C+": gpa = 2.8
Case "C": gpa = 2.6
Case "C-": gpa = 2.4
Case "D+": gpa = 2.2..........

View Replies!   View Related
WorkDay Function VBA
The function is to show the next workday date in cell a1

View Replies!   View Related
#value Error In VBA Function
my formula called ' conversion' used to convert foreign money in EUR does not
seem to work. My formula gives a value error. I wonder why...

Function Conversion(Position As Integer, Currency As Integer) As Integer

Dim USD As Integer
Dim GBP As Integer
Dim CHF As Integer
Dim JPY As Integer
Dim NOK As Integer
Dim NZD As Integer
Dim SEK As Integer
Dim ZAR As Integer
Dim ISK As Integer
Dim TRY As Integer
Dim AUD As Integer

' here i give a destination to my forex quotes who are in a excel sheet
USD = Range("N9").Value
GBP = Range("N10").Value
CHF = Range("N11").Value
JPY = Range("N12").Value
NOK = Range("N13").Value
NZD = Range("N14").Value
SEK = Range("N15").Value
ZAR = Range("N16").Value
ISK = Range("N17").Value
TRY = Range("N18").Value
AUD = Range("N19").Value

View Replies!   View Related
VBA IF Function Or If IsEmpty
I need a IF funtion to look at a series of cells and if the cells are empty I want it to proceed to userform1. If the cells have data already i want it to go to userform5.

I tried IF IsEmpty(cell list)=True Then UserForm1 and IF IsEmpty(cell list)=False Then UserForm5

But it only bring up userform5 no matter what the cells have in them.

View Replies!   View Related
Yield Function In VBA
I just upgraded to Office 2007 and am having trouble getting the Yield function to work in a macro I wrote in office 2003. I have added the analysis tool pack and the analysis toolpack for VBA and it still does not work.

The error I get is that the object or property is not supported. All variables used in the function are assuming the correct values.

MarketYield = Application.Worksheetfuntion.Yield(Date1, BondMaturityDate, TotalWAC2, MarketPrice2, 100, 2)

View Replies!   View Related
VBA Left Function
I am trying to use the left function to include only the first account number, for example if a cell has "120122 280000" (there may be many spaces between the two numbers), I only want 120122.
So far my programming only returns the 280000 into the cell.

I am working with cell values in column "K".

View Replies!   View Related
VBA Simple Right Function
Is there any reason why this wouldn't work in Excel 2007 despite working fine in 2003?

.Range("C2:C15132") = Right(.Range("C2:C15132"), 7)

View Replies!   View Related
Worksheet Function In VBA
My workbook has a sheet called "MyList", another called "HisList" , a named range called "myNamedRange" which is "MyList!A:A".

How do I get this functionality of this excel formula

View Replies!   View Related
Replace Function In VBA
I'm working with a word table pasted into excel. The dashes in the word table paste into Excel (2003) as small boxes. I can use the find and replace function in Excel and in the Excel environment the replace function works. I recorded the replace function so I could develop the action in VBA. The recoded action does not work. The data I've pasted in below looks fine, however when pasted into Excel all of the "-" paste in as small boxes. When I used the chr function the chr code equals 63 which is "?". I used chr(63) in the replace function. This causes the VBA replace function to replace all characters in the file with dashes. I've tried several other codes (127,129,141,143,144,157) (none worked) based on the following website:


Cells.Replace What:=Chr(63), Replacement:="-" 'finds and replaces the - that converted to boxes

I can paste in the data using paste special "unicode text" but I loose the strike through formating which I need to identify obsolete data.

View Replies!   View Related
Vba Function Not Updating
I have the folowing vba function that i use to check the existance of files but when the workbook is refreshed, the formula doesn't update. It's only updated when i double click it's cell.

View Replies!   View Related
MOD Function In VBA
In the excel worksheet, I use MOD(YEAR();4) to determine whether there are 365 or 366 days in that year. I would like to use this function also in my User Defined Function VBA Code. I cannot find how to do this.

View Replies!   View Related
Using The OFFSET Function In VBA
I am trying to write two different codes to copy a range of data. Basically I am trying to understand how the OFFSET function works in VBA. The first code listed works fine and is as follows:

View Replies!   View Related
Use The Iferror Function In VBA
I'm trying to use the iferror function in VBA and the whole code is not executing, as the syntax error is being noticed first, and not allowing the whole code to execute. What I want to happen is if the vlookup returns an error, the cell value should be "*". The code is:

View Replies!   View Related
Find Function In Vba
I have this source data that has different types of currencies in it. It will be processed by a marco. But Before i do the processing, i would like excel to check if all currencies in the source data have had their rates determined by the user in another worksheet. This is the code i am using. However, when the marco trys to find rates that has not been determined (cannot be found in remarks sheets), it will just skip the msgbox code.

Find = Cells.Find(What:=local_currency, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select

If Find = True And ActiveCell.Next.Next.Value <> "" Then
Find = Empty
MsgBox "The currency " & local_currency & " does not exist in the currency list"
End If

View Replies!   View Related
Maxif Function In Vba
I want to write a reuseable maxif function in vba. The following code is working when entered directly into the cell:

= SUMPRODUCT(MAX((B1:B6=B2) * (A1:A6)))

Now I want to pack this function into vba code to make it commonly available. I tried it with the following code but it doesn't work. I assume there is a problem with the passing of the range parameters. I also tried it with String and Variant as parameter types - without success. Can anybody help me?

Public Function MaxIF(criteriaRange As Range, searchValue As Variant, calcRange As Range)

AciveCell.Formula = "=SumProduct(Max((criteriaRange = searchValue) * (calcRange)))"

End Function

View Replies!   View Related
Countif Function In Vba
I'm trying to use the worksheet function countif in my code and it does not recognize it. But it does recognize counta. Here's a sample of my code.

Private Sub UserForm_Activate()
Dim MyUnion As Range
Set MyUnion = Union(Rows(12), Rows(14), Rows(16), Rows(18), Rows(20), Rows(22), _
Rows(29), Rows(31), Rows(33), Rows(35), Rows(37), Rows(39), _
Rows(46), Rows(48), Rows(50), Rows(52), Rows(54), Rows(56), _
Rows(63), Rows(65), Rows(67), Rows(69), Rows(71), Rows(73))
txtTtlDays.Text = Application.worksheetfunction.countif(MyUnion, "&A")
End Sub

View Replies!   View Related
Xirr Function In VBA
I am having trouble with the Xirr function in VBA. The problem revolves around the sensitivity of the solution to the 'initial guess'. specifically the boundary between a +ve and -ve guess. i.e. if the xirr is large and -ve a positive guess will always give me a solution very close to zero, as opposed to the more meaningfull solution say -65%.

To get around this i have constructed the UDF below, which is used as part of a sub, however for some investments i get the type mismatch error (error(13)) even though the arrays are of identical size and the data should be uniform as it is a dump from a database.

Has anyone encountered this 'type mismatch' problem with xirr before? is there a way to access the vba code for xirr so that it can be altered?

Function fkxirr(arrVals, arrDates)

Dim r As Double
Dim Step As Double
Dim tolerance As Double
Dim xir As Double

r = 10
tolerance = 0.000001
Step = 0.1

View Replies!   View Related
Copyright 2005-08, All rights reserved