User Defined Function returning #Value!

I have a function that I created.  When I test it in the Intermediate Window,  
? Kountifs("Registered Nurse"), it returns a 12 which is correct.

I want to be able to use this function a my datasheet.  I have included 
basically the same function =Kountifs("Registered Nurse").  But, ont the 
datasheet I receive a #Value! rather than the 12. 

Does anyone have ideas why?
0
Utf
11/25/2009 3:17:02 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

4 Replies
1134 Views

Similar Articles

[PageSpeed] 56

Why dont you post the UDF..

If this post helps click Yes
---------------
Jacob Skaria


"DogLover" wrote:

> I have a function that I created.  When I test it in the Intermediate Window,  
> ? Kountifs("Registered Nurse"), it returns a 12 which is correct.
> 
> I want to be able to use this function a my datasheet.  I have included 
> basically the same function =Kountifs("Registered Nurse").  But, ont the 
> datasheet I receive a #Value! rather than the 12. 
> 
> Does anyone have ideas why?
0
Utf
11/25/2009 3:24:01 PM
Here is the UDF code.

Function Kountifs(mPositionC As String) As Long
Dim mTimeCriteria As String
Dim mPositionCriteria As String
Dim mBeginDateCriteria As Variant
Dim mEndDateCriteria As Variant
Dim mStatusCriteria As String
Dim mShiftCriteria As String
Dim mEntityCriteria As String
Dim mDeptNoCriteria As String
Dim mQuestion1Criteria As String

Dim mTimeRange As Range
Dim mPositionRange As Range
Dim mOrientMoYrRange As Range
Dim mStatusRange As Range
Dim mShiftRange As Range
Dim mDeptNoRange As Range
Dim mEntityRange As Range
Dim mQuestion1Range As Range

Dim mFormula As String
Dim mBegMo As Integer, mBegYr As Integer
Dim mEndMo As Integer, mEndYr As Integer

mPositionCriteria = mPositionC      ' This line of Code allows automatic 
RECALCULATION
'mEntityCriteria = mEntityC
'mBeginDateCriteria = mBeginDateC
'mEndDateCriteria = mEndDateC
'mStatusCriteria = mStatusC
'mShiftCriteria = mShiftC
'mDeptNoCriteria = mDeptNoRC
'MsgBox "Position Reset   " & mPositionC
'  Needed if Subroutine vs Functio, change to passing variable later
'mPositionCriteria = Worksheets("RFJ").Range("N6")
mEntityCriteria = Worksheets("RFJ").Range("N7")
mBeginDateCriteria = Worksheets("RFJ").Range("N8")
mEndDateCriteria = Worksheets("RFJ").Range("N9")
mStatusCriteria = Worksheets("RFJ").Range("N10")
mShiftCriteria = Worksheets("RFJ").Range("N11")
mDeptNoCriteria = Worksheets("RFJ").Range("N12")

mBegMo = Month(mBeginDateCriteria)
mBegYr = Year(mBeginDateCriteria)

If Month(mEndDateCriteria) = 12 Then
     mEndMo = 1
     mEndYr = Year(mBeginDateCriteria) + 1
Else
     mEndMo = Month(mEndDateCriteria) + 1
     mEndYr = Year(mBeginDateCriteria)
End If

' MsgBox "Begin mo   " & mBegMo & "  Beg year   " & mBegYr
' MsgBox "End mo   " & mEndMo & "  End year   " & mEndYr

' Set Criterias
mBeginDateCriteria = ">=" & "DATE(" & mBegYr & "," & mBegMo & ",1)"
mEndDateCriteria = "<" & "DATE(" & mEndYr & "," & mEndMo & ",1)"
mTimeCriteria = "=" & Chr(34) & "First day of employment (Time 1)" & Chr(34)
mQuestion1Criteria = "<>" & Chr(34) & "*" & Chr(34)

'Position Criteria
If mPositionCriteria = "<>" Then
   mPositionCriteria = "<>" & Chr(34) & "*" & Chr(34)      ' ALL Records
Else
   mPositionCriteria = "=" & Chr(34) & mPositionCriteria & Chr(34)
End If

'Entity Criteria
If mEntityCriteria = "<>" Then
   mEntityCriteria = "<>" & Chr(34) & "*" & Chr(34)      ' ALL Records
Else
   mEntityCriteria = "=" & Chr(34) & mEntityCriteria & Chr(34)
End If

'Status Criteria
If mStatusCriteria = "<>" Then
   mStatusCriteria = "<>" & Chr(34) & "*" & Chr(34)      ' ALL Records
Else
   mStatusCriteria = "=" & Chr(34) & mStatusCriteria & Chr(34)
End If

'Shift Criteria
If mShiftCriteria = "<>" Then
   mShiftCriteria = "<>" & Chr(34) & "*" & Chr(34)      ' ALL Records
Else
   mShiftCriteria = "=" & Chr(34) & mShiftCriteria & Chr(34)
End If

'Dept No Criteria (NUMERIC FIELD)
If mDeptNoCriteria = "<>" Then
   mDeptNoCriteria = ">=" & 0        ' ALL Records
Else
   mDeptNoCriteria = "=" & mDeptNoCriteria
End If

With Worksheets("Data")
Set mTimeRange = .Range("DataTime")
Set mPositionRange = .Range("DataPosition")
Set mOrientMoYrRange = .Range("DataOrientMoYr")
Set mStatusRange = .Range("DataStatus")
Set mShiftRange = .Range("DataShift")
Set mDeptNoRange = .Range("DataDeptNo")
Set mEntityRange = .Range("DataEntity")
Set mQuestion1Range = .Range("DataQuestion1")


mFormula = "=SUMPRODUCT(--(" & mTimeRange.Address & mTimeCriteria & "),"
mFormula = mFormula & "--(" & mPositionRange.Address & mPositionCriteria & 
"),"
mFormula = mFormula & "--(" & mOrientMoYrRange.Address & mBeginDateCriteria 
& "),"
mFormula = mFormula & "--(" & mOrientMoYrRange.Address & mEndDateCriteria & 
"),"
mFormula = mFormula & "--(" & mShiftRange.Address & mShiftCriteria & "), "
mFormula = mFormula & "--(" & mStatusRange.Address & mStatusCriteria & "),"
mFormula = mFormula & "--(" & mEntityRange.Address & mEntityCriteria & "),"
mFormula = mFormula & "-- (" & mQuestion1Range.Address & mQuestion1Criteria 
& ") )"

'Store the formula on the DATA sheet
..Range("A2").Formula = mFormula

'Evaluate the formula
Kountifs = .Evaluate("A2")
End With
MsgBox Kountifs
   
If IsError(Kountifs) Then
    MsgBox "Error in evaluating"
End If
   
End Function

"Jacob Skaria" wrote:

> Why dont you post the UDF..
> 
> If this post helps click Yes
> ---------------
> Jacob Skaria
> 
> 
> "DogLover" wrote:
> 
> > I have a function that I created.  When I test it in the Intermediate Window,  
> > ? Kountifs("Registered Nurse"), it returns a 12 which is correct.
> > 
> > I want to be able to use this function a my datasheet.  I have included 
> > basically the same function =Kountifs("Registered Nurse").  But, ont the 
> > datasheet I receive a #Value! rather than the 12. 
> > 
> > Does anyone have ideas why?
0
Utf
11/25/2009 3:37:03 PM
You will have to remove all msgboxs and the lines which assign a 
value/formula to a range ...which will not work when you try this as a 
UDF....With UDF you can pass arguments and return a value..

If this post helps click Yes
---------------
Jacob Skaria


"DogLover" wrote:

> Here is the UDF code.
> 
> Function Kountifs(mPositionC As String) As Long
> Dim mTimeCriteria As String
> Dim mPositionCriteria As String
> Dim mBeginDateCriteria As Variant
> Dim mEndDateCriteria As Variant
> Dim mStatusCriteria As String
> Dim mShiftCriteria As String
> Dim mEntityCriteria As String
> Dim mDeptNoCriteria As String
> Dim mQuestion1Criteria As String
> 
> Dim mTimeRange As Range
> Dim mPositionRange As Range
> Dim mOrientMoYrRange As Range
> Dim mStatusRange As Range
> Dim mShiftRange As Range
> Dim mDeptNoRange As Range
> Dim mEntityRange As Range
> Dim mQuestion1Range As Range
> 
> Dim mFormula As String
> Dim mBegMo As Integer, mBegYr As Integer
> Dim mEndMo As Integer, mEndYr As Integer
> 
> mPositionCriteria = mPositionC      ' This line of Code allows automatic 
> RECALCULATION
> 'mEntityCriteria = mEntityC
> 'mBeginDateCriteria = mBeginDateC
> 'mEndDateCriteria = mEndDateC
> 'mStatusCriteria = mStatusC
> 'mShiftCriteria = mShiftC
> 'mDeptNoCriteria = mDeptNoRC
> 'MsgBox "Position Reset   " & mPositionC
> '  Needed if Subroutine vs Functio, change to passing variable later
> 'mPositionCriteria = Worksheets("RFJ").Range("N6")
> mEntityCriteria = Worksheets("RFJ").Range("N7")
> mBeginDateCriteria = Worksheets("RFJ").Range("N8")
> mEndDateCriteria = Worksheets("RFJ").Range("N9")
> mStatusCriteria = Worksheets("RFJ").Range("N10")
> mShiftCriteria = Worksheets("RFJ").Range("N11")
> mDeptNoCriteria = Worksheets("RFJ").Range("N12")
> 
> mBegMo = Month(mBeginDateCriteria)
> mBegYr = Year(mBeginDateCriteria)
> 
> If Month(mEndDateCriteria) = 12 Then
>      mEndMo = 1
>      mEndYr = Year(mBeginDateCriteria) + 1
> Else
>      mEndMo = Month(mEndDateCriteria) + 1
>      mEndYr = Year(mBeginDateCriteria)
> End If
> 
> ' MsgBox "Begin mo   " & mBegMo & "  Beg year   " & mBegYr
> ' MsgBox "End mo   " & mEndMo & "  End year   " & mEndYr
> 
> ' Set Criterias
> mBeginDateCriteria = ">=" & "DATE(" & mBegYr & "," & mBegMo & ",1)"
> mEndDateCriteria = "<" & "DATE(" & mEndYr & "," & mEndMo & ",1)"
> mTimeCriteria = "=" & Chr(34) & "First day of employment (Time 1)" & Chr(34)
> mQuestion1Criteria = "<>" & Chr(34) & "*" & Chr(34)
> 
> 'Position Criteria
> If mPositionCriteria = "<>" Then
>    mPositionCriteria = "<>" & Chr(34) & "*" & Chr(34)      ' ALL Records
> Else
>    mPositionCriteria = "=" & Chr(34) & mPositionCriteria & Chr(34)
> End If
> 
> 'Entity Criteria
> If mEntityCriteria = "<>" Then
>    mEntityCriteria = "<>" & Chr(34) & "*" & Chr(34)      ' ALL Records
> Else
>    mEntityCriteria = "=" & Chr(34) & mEntityCriteria & Chr(34)
> End If
> 
> 'Status Criteria
> If mStatusCriteria = "<>" Then
>    mStatusCriteria = "<>" & Chr(34) & "*" & Chr(34)      ' ALL Records
> Else
>    mStatusCriteria = "=" & Chr(34) & mStatusCriteria & Chr(34)
> End If
> 
> 'Shift Criteria
> If mShiftCriteria = "<>" Then
>    mShiftCriteria = "<>" & Chr(34) & "*" & Chr(34)      ' ALL Records
> Else
>    mShiftCriteria = "=" & Chr(34) & mShiftCriteria & Chr(34)
> End If
> 
> 'Dept No Criteria (NUMERIC FIELD)
> If mDeptNoCriteria = "<>" Then
>    mDeptNoCriteria = ">=" & 0        ' ALL Records
> Else
>    mDeptNoCriteria = "=" & mDeptNoCriteria
> End If
> 
> With Worksheets("Data")
> Set mTimeRange = .Range("DataTime")
> Set mPositionRange = .Range("DataPosition")
> Set mOrientMoYrRange = .Range("DataOrientMoYr")
> Set mStatusRange = .Range("DataStatus")
> Set mShiftRange = .Range("DataShift")
> Set mDeptNoRange = .Range("DataDeptNo")
> Set mEntityRange = .Range("DataEntity")
> Set mQuestion1Range = .Range("DataQuestion1")
> 
> 
> mFormula = "=SUMPRODUCT(--(" & mTimeRange.Address & mTimeCriteria & "),"
> mFormula = mFormula & "--(" & mPositionRange.Address & mPositionCriteria & 
> "),"
> mFormula = mFormula & "--(" & mOrientMoYrRange.Address & mBeginDateCriteria 
> & "),"
> mFormula = mFormula & "--(" & mOrientMoYrRange.Address & mEndDateCriteria & 
> "),"
> mFormula = mFormula & "--(" & mShiftRange.Address & mShiftCriteria & "), "
> mFormula = mFormula & "--(" & mStatusRange.Address & mStatusCriteria & "),"
> mFormula = mFormula & "--(" & mEntityRange.Address & mEntityCriteria & "),"
> mFormula = mFormula & "-- (" & mQuestion1Range.Address & mQuestion1Criteria 
> & ") )"
> 
> 'Store the formula on the DATA sheet
> .Range("A2").Formula = mFormula
> 
> 'Evaluate the formula
> Kountifs = .Evaluate("A2")
> End With
> MsgBox Kountifs
>    
> If IsError(Kountifs) Then
>     MsgBox "Error in evaluating"
> End If
>    
> End Function
> 
> "Jacob Skaria" wrote:
> 
> > Why dont you post the UDF..
> > 
> > If this post helps click Yes
> > ---------------
> > Jacob Skaria
> > 
> > 
> > "DogLover" wrote:
> > 
> > > I have a function that I created.  When I test it in the Intermediate Window,  
> > > ? Kountifs("Registered Nurse"), it returns a 12 which is correct.
> > > 
> > > I want to be able to use this function a my datasheet.  I have included 
> > > basically the same function =Kountifs("Registered Nurse").  But, ont the 
> > > datasheet I receive a #Value! rather than the 12. 
> > > 
> > > Does anyone have ideas why?
0
Utf
11/25/2009 6:33:01 PM
As Jacob says: worksheet UDFs will only return values to the calling cell 
and are not allowed to change data in other cells.
so remove these lines

'Store the formula on the DATA sheet
 .Range("A2").Formula = mFormula

(MsgBox is OK but only for debug purposes)

Also your function will not work properly if any of the referenced 
cells/named ranges change unless you make it Volatile by adding
Application.Volatile

For better error handling define the function as Variant rather than Long 
and trap and return an error when it occurs

Kountifs=CVErr(XLerrNA)

or whatever error value you think is appropriate.

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"DogLover" <DogLover@discussions.microsoft.com> wrote in message 
news:3A143AAD-AE54-409B-98D9-29C340D334D4@microsoft.com...
> Here is the UDF code.
>
> Function Kountifs(mPositionC As String) As Long
> Dim mTimeCriteria As String
> Dim mPositionCriteria As String
> Dim mBeginDateCriteria As Variant
> Dim mEndDateCriteria As Variant
> Dim mStatusCriteria As String
> Dim mShiftCriteria As String
> Dim mEntityCriteria As String
> Dim mDeptNoCriteria As String
> Dim mQuestion1Criteria As String
>
> Dim mTimeRange As Range
> Dim mPositionRange As Range
> Dim mOrientMoYrRange As Range
> Dim mStatusRange As Range
> Dim mShiftRange As Range
> Dim mDeptNoRange As Range
> Dim mEntityRange As Range
> Dim mQuestion1Range As Range
>
> Dim mFormula As String
> Dim mBegMo As Integer, mBegYr As Integer
> Dim mEndMo As Integer, mEndYr As Integer
>
> mPositionCriteria = mPositionC      ' This line of Code allows automatic
> RECALCULATION
> 'mEntityCriteria = mEntityC
> 'mBeginDateCriteria = mBeginDateC
> 'mEndDateCriteria = mEndDateC
> 'mStatusCriteria = mStatusC
> 'mShiftCriteria = mShiftC
> 'mDeptNoCriteria = mDeptNoRC
> 'MsgBox "Position Reset   " & mPositionC
> '  Needed if Subroutine vs Functio, change to passing variable later
> 'mPositionCriteria = Worksheets("RFJ").Range("N6")
> mEntityCriteria = Worksheets("RFJ").Range("N7")
> mBeginDateCriteria = Worksheets("RFJ").Range("N8")
> mEndDateCriteria = Worksheets("RFJ").Range("N9")
> mStatusCriteria = Worksheets("RFJ").Range("N10")
> mShiftCriteria = Worksheets("RFJ").Range("N11")
> mDeptNoCriteria = Worksheets("RFJ").Range("N12")
>
> mBegMo = Month(mBeginDateCriteria)
> mBegYr = Year(mBeginDateCriteria)
>
> If Month(mEndDateCriteria) = 12 Then
>     mEndMo = 1
>     mEndYr = Year(mBeginDateCriteria) + 1
> Else
>     mEndMo = Month(mEndDateCriteria) + 1
>     mEndYr = Year(mBeginDateCriteria)
> End If
>
> ' MsgBox "Begin mo   " & mBegMo & "  Beg year   " & mBegYr
> ' MsgBox "End mo   " & mEndMo & "  End year   " & mEndYr
>
> ' Set Criterias
> mBeginDateCriteria = ">=" & "DATE(" & mBegYr & "," & mBegMo & ",1)"
> mEndDateCriteria = "<" & "DATE(" & mEndYr & "," & mEndMo & ",1)"
> mTimeCriteria = "=" & Chr(34) & "First day of employment (Time 1)" & 
> Chr(34)
> mQuestion1Criteria = "<>" & Chr(34) & "*" & Chr(34)
>
> 'Position Criteria
> If mPositionCriteria = "<>" Then
>   mPositionCriteria = "<>" & Chr(34) & "*" & Chr(34)      ' ALL Records
> Else
>   mPositionCriteria = "=" & Chr(34) & mPositionCriteria & Chr(34)
> End If
>
> 'Entity Criteria
> If mEntityCriteria = "<>" Then
>   mEntityCriteria = "<>" & Chr(34) & "*" & Chr(34)      ' ALL Records
> Else
>   mEntityCriteria = "=" & Chr(34) & mEntityCriteria & Chr(34)
> End If
>
> 'Status Criteria
> If mStatusCriteria = "<>" Then
>   mStatusCriteria = "<>" & Chr(34) & "*" & Chr(34)      ' ALL Records
> Else
>   mStatusCriteria = "=" & Chr(34) & mStatusCriteria & Chr(34)
> End If
>
> 'Shift Criteria
> If mShiftCriteria = "<>" Then
>   mShiftCriteria = "<>" & Chr(34) & "*" & Chr(34)      ' ALL Records
> Else
>   mShiftCriteria = "=" & Chr(34) & mShiftCriteria & Chr(34)
> End If
>
> 'Dept No Criteria (NUMERIC FIELD)
> If mDeptNoCriteria = "<>" Then
>   mDeptNoCriteria = ">=" & 0        ' ALL Records
> Else
>   mDeptNoCriteria = "=" & mDeptNoCriteria
> End If
>
> With Worksheets("Data")
> Set mTimeRange = .Range("DataTime")
> Set mPositionRange = .Range("DataPosition")
> Set mOrientMoYrRange = .Range("DataOrientMoYr")
> Set mStatusRange = .Range("DataStatus")
> Set mShiftRange = .Range("DataShift")
> Set mDeptNoRange = .Range("DataDeptNo")
> Set mEntityRange = .Range("DataEntity")
> Set mQuestion1Range = .Range("DataQuestion1")
>
>
> mFormula = "=SUMPRODUCT(--(" & mTimeRange.Address & mTimeCriteria & "),"
> mFormula = mFormula & "--(" & mPositionRange.Address & mPositionCriteria &
> "),"
> mFormula = mFormula & "--(" & mOrientMoYrRange.Address & 
> mBeginDateCriteria
> & "),"
> mFormula = mFormula & "--(" & mOrientMoYrRange.Address & mEndDateCriteria 
> &
> "),"
> mFormula = mFormula & "--(" & mShiftRange.Address & mShiftCriteria & "), "
> mFormula = mFormula & "--(" & mStatusRange.Address & mStatusCriteria & 
> "),"
> mFormula = mFormula & "--(" & mEntityRange.Address & mEntityCriteria & 
> "),"
> mFormula = mFormula & "-- (" & mQuestion1Range.Address & 
> mQuestion1Criteria
> & ") )"
>
> 'Store the formula on the DATA sheet
> .Range("A2").Formula = mFormula
>
> 'Evaluate the formula
> Kountifs = .Evaluate("A2")
> End With
> MsgBox Kountifs
>
> If IsError(Kountifs) Then
>    MsgBox "Error in evaluating"
> End If
>
> End Function
>
> "Jacob Skaria" wrote:
>
>> Why dont you post the UDF..
>>
>> If this post helps click Yes
>> ---------------
>> Jacob Skaria
>>
>>
>> "DogLover" wrote:
>>
>> > I have a function that I created.  When I test it in the Intermediate 
>> > Window,
>> > ? Kountifs("Registered Nurse"), it returns a 12 which is correct.
>> >
>> > I want to be able to use this function a my datasheet.  I have included
>> > basically the same function =Kountifs("Registered Nurse").  But, ont 
>> > the
>> > datasheet I receive a #Value! rather than the 12.
>> >
>> > Does anyone have ideas why?
> 


0
Charles
11/25/2009 7:31:55 PM
Reply:

Similar Artilces:

Carriage Return in General Text Box?
Have a cell defined as GENERAL with wrapped text. How can I put a carriage return in the middle of my wrapped text and start a new paragraph, all in one cell. Use Alt+Enter -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Al Franz" <albert@nospam.netmation.com> wrote in message news:eJmtR4kAFHA.2608@TK2MSFTNGP10.phx.gbl... > Have a cell defined as GENERAL with wrapped text. How can I put a > carriage return in the middle of my wrapped text and start a new > paragraph, all in one cell. > > ...

How to Check/Track BIS (BlackBerry) Users?
We use Eaxchange 2003 and have Outlook Web Access enabled. We have users who are accesing their Exchange email on BlackBerry's via the BIS OWA connection option. 1. How can an Exchange Administrator tell which users are accessing OWA via BIS? 2. Are their settings that would allow OWA to remain turned on but BIS access disabled? Thank you. Clark On Tue, 4 Apr 2006 16:41:01 -0700, Clark <Clark@discussions.microsoft.com> wrote: >We use Eaxchange 2003 and have Outlook Web Access enabled. We have users who >are accesing their Exchange email on BlackBerry's via the BI...

how to transfer outlook email files to new user on pc
I am setting up a new user on pc using office professional 2003--what is best way to transfer a large number of emails in my account to the other account user on my pc thanks Rob Copy the .pst file with Outlook closed, and then Open Outlook and Choose Open from the File menu, then Outlook Data File... follow instructions... "rob a" <rob a@discussions.microsoft.com> wrote in message news:8FD76AB2-9F2B-490B-AC2E-70AA1D486623@microsoft.com... >I am setting up a new user on pc using office professional 2003--what is >best > way to transfer a large number of emails...

Word 2008 created files not fully readable by PC Word (2007 or 2003) users
When I created word files on my mac using Word 2008, pc users that I send the file to are only able to view the first two pages upon opening. I get the same result when trying to open the files on my word 2007 version through my Mac using the Parrallel program to access the windows OS. Is there an update that I am missing to correct this issue? Well, it's impossible to answer your question since you haven't given any indication of what your present update level is � or what version of OS X you're using. What I can tell you is that 12.2.3 is the latest available, so if t...

#VALUE! error: vlookup works in Excel 2000 but not 2003
Hi, My client has a spreadsheet which works fine in Excel 2000 but when opened with Excel 2003 it populates the pages with #VALUE! and the best I can tell is that the problem is with the following formulas. Can anyone advise if they have seen this before. I am unable to post their file but this is the code that I think is a bit suspect. =IF(ISERROR(VLOOKUP(B14,'C:\Documents and Settings\User\My Documents\[Price List 040301.xls]Items'!$A:$M,11,FALSE))=TRUE,"",VLOOKUP(B14,'C:\Documents and Settings\User\My Documents\[Price List 040301.xls]Items'!$A:$M,11,FALSE...

In which header file is VK_OEM_PLUS defined?
In MSDN, it says that VK_OEM_PLUS is a defined key (constant? ). But I can't find the the header file in which VK_OEM_PLUS is defined. "Fei Xu, or Flying Xu" <FeiXuorFlyingXu@discussions.microsoft.com> wrote in message news:BA1B6413-96DF-44BA-A1B6-A972DFC50F91@microsoft.com... > In MSDN, it says that VK_OEM_PLUS is a defined key (constant? ). > But I can't find the the header file in which VK_OEM_PLUS is defined. Based on my PSDK install, it appears in 'WinUser.h'. -- Jeff Partch [VC++ MVP] ...

Is it possible to create a crm user without a domain login name?
Is it possible to create a crm user without a domain login name? Isn't domain logon name a required field? I heard somewhere that this was possible? We need to change some account's owners to future domain users. Domainlogname is required field in CRM Like --- (Microsoft\ba) and it means you need to create a user in CRM .Account always has an owner , which is CRM user. would like to hear if someone has some tricks to bypass the domainlogname :=) ------ Aamir Blog = http://mscrmsupport.wordpress.com/ IT is required when you create the user, but once you have created the user, ...

Default value for a field in the create form
Hi all, It's possible to define a default value for a field, in the create form ? Thanks, Hugo You can default a picklist value with out of the box customization but that's it. >-----Original Message----- >Hi all, > >It's possible to define a default value for a field, in the create form ? > >Thanks, >Hugo > > >. > ...

Finding values in different rows/columns
This should be simple, but I'm lost. To greatly simplify my sheets: 1 11 12 13 14 15 21 22 23 24 25 31 32 33 34 35 41 42 43 44 45 2 11 12 13 14 15 21 22 23 24 25 31 32 33 34 35 41 42 43 44 45 I want to do a VLOOKUP(1,A1:A10,1,false) and return the value in D4, or VLOOKUP(2,A1:A10,1,false) and return the value in C9. In other words, find my A cell reference and return the value in a different row/column from that. Another question: When I move a cell (cell1) to another cell (cell2) a cell (cell3) that references cell1 moves its reference from cell1 to c...

Adding CRM users on an SBS2003 instance
I just installed CRM 3.0 on our instance of SBS2003. All of the documentation I've read thus far instructs me to use Active Directory to add new users. What about using SBS2003's built in "Server Management" app, will that work as well? I have a half a dozen existing users on the SBS server prior to installing CRM. Some have Exchange email boxes some do not. How do I go about adding them to CRM? I assume the ones with exchange email boxes will need to have one set up for them to fully participate in CRM. This is my FIRST ever CRM installation, so please be gentle if these are...

Select values from list
Hi all, I got a very stupid problem, but like any stupid problem is bothering me... Some time ago I created a Worbook, and in a few columns of a worksheet I created a kind of combobox, integrated in the sheet, to help the user to select values from a list positioned elewhere in the sheet. To put it simple, when the user selects a cell, appears a down arrow on the right side, and clicking it it's shown a list to choose from. The values of the list are in a cell range on the right side of the sheet. Well, now I need to add new columns, but I can't find out how to re-create these co...

Survival distribution function chart
Is anyone familiar with the Kaplan-Meier estimator, and if there is a way I can get excel to draw a traditional survivior curve? The charts on this page http://www.xlstat.com/demoKM.xls are the style that I would like to produce. However, these appear to have been produced using an add-on called XLstat Pro. I am attempting to produce a spreadsheet to analyse various statistical models which are all reliant on producing a survivor curve in this style. Any help anyone could offer me would be gratefully received as I have spent days trying and can not find a way of doing this. -- max0d --...

to use workday function in excel vba code
hi all, is there a way to use wrokday function in my worksheet controls.? i have a date time picker in my worksheet and a text box and a button. on click of this button i should get next desired date. like when i select a date from the calendar and click on button ther is onclick function in my macro. this onclick should calculate next desired date(assume if i choos 10/11/2005 and add 2 to this date i should get 12/11/2005) i know how to use workday function using a cell reference but................ i don't know how to put this into a vba code. if i use workday("10/11/2005&qu...

Reset all users mailbox limits to default policy
What process can I run to change all Exchange users mailbox storage limits to reflect the default policy for the store their mailbox resides on? I have a mix of users who have manual storage limit settings but would like to change all back to default settings with out having to address each user indivdually. Thanks, Brian bjorgenson@charter.net wrote: >What process can I run to change all Exchange users mailbox storage >limits to reflect the default policy for the store their mailbox >resides on? I have a mix of users who have manual storage limit >settings but would like to ch...

fixing pie chart colors depending upon value ?
This is really about using an excel chart inside an access report but nobody in microsoft.access.reports seems to know the answer. Using a grouped field in a database I end up with two values per record showing the number of 'goods' and the number of 'bads' The pie chart plots these two numbers. I want the 'good' slice always to be green and the 'bad' slice always to be red. Setting the format worked OK until I had 7 goods and zero bads, then the whole chart was red instead of green. Any idea how I can fix this? Howard The technique described in thi...

i want to allow users to edit changes afetr password protect of c
Please use the body of the message to describe what you want. Gord Dibben MS Excel MVP On Wed, 19 Aug 2009 01:15:02 -0700, mamta raul <mamta raul@discussions.microsoft.com> wrote: ...

How to create and set values for newly created attributes in active directory
Hi, I need to add a new string attribute in the properties of users in the active directory. I have changed the schema and added the attribute. Then, it gets listed in the attributes list. But, I could not set this attribute value for users. Please help me in setting this new attribute value for each and every user of users in AD. Thanks, - mv. -- m v ramana ------------------------------------------------------------------------ m v ramana's Profile: http://forums.techarena.in/members/181470.htm View this thread: http://forums.techarena.in/active-directory/129...

Pivot Tables
When a pivot table has 2 indices, the values of the first index ar left blank on the 2nd and subsequent lines of the 2nd index. Can thes index values be automatically duplicated? Thanks ANdrew Middleto -- Message posted from http://www.ExcelForum.com Andrew, Send me your email address and I'll PM you a small add-in I put together to do this.. Cheers, Dave >-----Original Message----- >When a pivot table has 2 indices, the values of the first index are >left blank on the 2nd and subsequent lines of the 2nd index. Can these >index values be automatically duplicated?...

If functions??
Im trying to create a spreadsheet file where I can input data into a column and have it insert a certain factor. For example I want to be able to input data so that if i type 70 in one column it enters .985 in the next column and if i enter 71 then 979 is entered that column and so forth. The numbers are part of a temperature correction factor for a lab test so they range from 60 to 89 degrees therefore there are about 30 if then combinations. I tried visual basic but got lost can anyone help me out with a code or point me in the right direction?? Thanks -- DBassmaker -----------------...

Add pecent and value on the same graph
I would like to add the percent and the value and display it within a pie chart. Thanks for your help. You can calculate the percentages on the worksheet, and use a formula to create the labels. For example, with the following data in in A1:B3 -- Jan 25 Feb 25 Mar 50 Enter the following formula in cell C1, and copy down to C3: =B1/SUM($B$1:$B$3) To calculate the text for the pie chart labels, enter the following formula in cell D1, and copy down to D3: =B1 & " " & TEXT(C1,"0%") When you create the pie chart, in step 2 of the Chart Wizard, click ...

user defined fields
Cards-Sales-Customer, in lower left hand corner of the Customer Maintenance screen there are two user defined fields. one of our users would like to have User Defined 1 changed to "Tax ID/SS #". She should have been able to do this after looking at her user rights to "Tools-Customize-modifier" and "cards-Sales- Customer". loged in as "sa" and was not able to modify that same window. All I get is a greyed out Modifier / modify current window. ideas? Try going to Cards / Sales / Receivables and click on the Options tab. You will notice the...

Custom user function sheet recalculate problem #2
Hi All, I have a custom user function called in many cells in my spreadshee much like -=getdata(ref1,ref2)- which works great! Within this functio a call is made to an SQL database to retrieve one piece information an return it to the spreadsheet. Althought it seems really quick for on cell its obviously a major overhead for many cells. Currently 100 cell updates takes about 15 secs which is actually not bad when it happening only once. However we will run large and complicate spreadsheets with perhaps many times more data being refreshe regularly in different ways. What I want to achive ...

Autocomplete Custom Functions and functions arguments
Hi, I have custom functions in VBA. I want Excel to show the Autocomplete options as the Microsoft Office Excel displays below the cell a dynamic drop-down list of valid functions, names, and text strings that match the letters or trigger. Also, I want to make the arguments be shown automatically For example, to make clear what i want On pressing =sum for example ( excel automatically shows Sum(Number1, [Number2],...) to help the user on the arguments So, I want my custom function also show the same. Thanks for your reply to may question and the response. On May 20, 5:15=A0pm, ...

Adding argument description to custom functions
Hi does anyone knows how to add description to custom functions' arguments so that a description of each argument can be displayed in the formula palette? thanks ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ VBA functions don't support text descriptions in the Function wizard. You can look at the information Laurent Longre has posted on his site about this: http://longre.free.fr/english/func_cats.htm Newer addin that supports this: http://longre.free.fr...

Changing properties with a function
Hi all, I'm trying to change the colour and special effect of a rectangle through a function because I want to have stop and go lights and I want to be changing them frequently, so my function is like this: Public Function SwitchOn (BoxName) as String Form_Home.BoxName.BackColor.Value = 4634122 Form_Home.BoxName.SpecialEffect.Value = "Sunken" end function then I call it by using: switchon (box1) - also tried switchon ("box1") but it doesn't want to work at all... how do I get the damn thing to work???? Do I have to set BoxName as a Rectangle or an Object ins...