Type mismatch error using Sumproduct

I've searched through similar topics and tried several variations of code, 
but I keep getting a type-mismatch error when trying to use Sumproduct.  I am 
wanting to use this in several parts of my code.  The first instance (which 
is also where the error keeps occurring) is below.

With Worksheets("Hours")
     TotalHrs = .Evaluate("SUMPRODUCT((HoursCodes=ClassCode)* _
          (HourStat=CliStat)*PeriodHours)")
End With

TotalHrs is dim'd as Single, ClassCode as String (either 4 or 5 characters 
long), CliStat as Integer (0, 1, or 99), and the rest as Ranges.  I've tried 
adjusting the syntax in every way I can think of and based on other examples 
I could find, but each time I get the type-mismatch error.  Do I just have 
something dim'd incorrectly?

Thanks for time and help.
0
Utf
3/23/2010 9:19:02 PM
excel.programming 6508 articles. 2 followers. Follow

7 Replies
821 Views

Similar Articles

[PageSpeed] 45

If you were writing the formula in a cell, it would look something like:
=SUMPRODUCT((HoursCodes="A")*(HourStat="x")*PeriodHours)

Since both the classcode and clistat values are strings, they need to be
surrounded by double quotes.

=chr(34) is a double quote in VBA land.



Option Explicit
Sub testme()

    Dim TotalHrs As Double 'don't use Single
    Dim ClassCode As String
    Dim CLIStat As String
    Dim myFormula As String
    
    ClassCode = "A"
    CLIStat = "x"
    
    '=SUMPRODUCT((HourCodes="a")*(HourStat="x")*PeriodHours)
    With Worksheets("Hours")
    
        myFormula = "SUMPRODUCT(" _
                    & "(HoursCodes=" & Chr(34) & ClassCode & Chr(34) & ")" _
                   & "*(HourStat=" & Chr(34) & CLIStat & Chr(34) & ")" _
                   & "*PeriodHours)"
                    
        TotalHrs = .Evaluate(myFormula)
                     
    End With
    
    MsgBox TotalHrs

End Sub

I find this more difficult to read (most the time!), but you could use it if you
like it better:

        myFormula = "SUMPRODUCT(" _
                    & "(HoursCodes=""" & ClassCode & """)" _
                    & "*(HourStat=""" & CLIStat & """)" _
                    & "*PeriodHours)"

======
ps.  The reason I used a separate string variable for the formula is so that I
could step through the code, debug.print the formula to the immediate window and
copy|Paste into a cell in excel proper.

Lots of times, I like to get excel's help with my formula typing errors.

pps.  There's no reason ever to use Single or Integer.  Always use Double and
Long.  Modern computers will have less work to do and you don't have to worry
about overflow errors.

Luke wrote:
> 
> I've searched through similar topics and tried several variations of code,
> but I keep getting a type-mismatch error when trying to use Sumproduct.  I am
> wanting to use this in several parts of my code.  The first instance (which
> is also where the error keeps occurring) is below.
> 
> With Worksheets("Hours")
>      TotalHrs = .Evaluate("SUMPRODUCT((HoursCodes=ClassCode)* _
>           (HourStat=CliStat)*PeriodHours)")
> End With
> 
> TotalHrs is dim'd as Single, ClassCode as String (either 4 or 5 characters
> long), CliStat as Integer (0, 1, or 99), and the rest as Ranges.  I've tried
> adjusting the syntax in every way I can think of and based on other examples
> I could find, but each time I get the type-mismatch error.  Do I just have
> something dim'd incorrectly?
> 
> Thanks for time and help.

-- 

Dave Peterson
0
Dave
3/23/2010 9:53:56 PM
I'm still getting the type-mismatch error, although now it is at the 
"TotalHrs = .Evaluate(myFormula)" line.  CLIStat is actually an Integer (0, 
1, or 99), or Long if you prefer.  Will that make a difference in the formula 
you gave?  I did change TotalHrs to Double and CLIStat to Long, however.

"Dave Peterson" wrote:

> If you were writing the formula in a cell, it would look something like:
> =SUMPRODUCT((HoursCodes="A")*(HourStat="x")*PeriodHours)
> 
> Since both the classcode and clistat values are strings, they need to be
> surrounded by double quotes.
> 
> =chr(34) is a double quote in VBA land.
> 
> 
> 
> Option Explicit
> Sub testme()
> 
>     Dim TotalHrs As Double 'don't use Single
>     Dim ClassCode As String
>     Dim CLIStat As String
>     Dim myFormula As String
>     
>     ClassCode = "A"
>     CLIStat = "x"
>     
>     '=SUMPRODUCT((HourCodes="a")*(HourStat="x")*PeriodHours)
>     With Worksheets("Hours")
>     
>         myFormula = "SUMPRODUCT(" _
>                     & "(HoursCodes=" & Chr(34) & ClassCode & Chr(34) & ")" _
>                    & "*(HourStat=" & Chr(34) & CLIStat & Chr(34) & ")" _
>                    & "*PeriodHours)"
>                     
>         TotalHrs = .Evaluate(myFormula)
>                      
>     End With
>     
>     MsgBox TotalHrs
> 
> End Sub
> 
> I find this more difficult to read (most the time!), but you could use it if you
> like it better:
> 
>         myFormula = "SUMPRODUCT(" _
>                     & "(HoursCodes=""" & ClassCode & """)" _
>                     & "*(HourStat=""" & CLIStat & """)" _
>                     & "*PeriodHours)"
> 
> ======
> ps.  The reason I used a separate string variable for the formula is so that I
> could step through the code, debug.print the formula to the immediate window and
> copy|Paste into a cell in excel proper.
> 
> Lots of times, I like to get excel's help with my formula typing errors.
> 
> pps.  There's no reason ever to use Single or Integer.  Always use Double and
> Long.  Modern computers will have less work to do and you don't have to worry
> about overflow errors.
> 
> Luke wrote:
> > 
> > I've searched through similar topics and tried several variations of code,
> > but I keep getting a type-mismatch error when trying to use Sumproduct.  I am
> > wanting to use this in several parts of my code.  The first instance (which
> > is also where the error keeps occurring) is below.
> > 
> > With Worksheets("Hours")
> >      TotalHrs = .Evaluate("SUMPRODUCT((HoursCodes=ClassCode)* _
> >           (HourStat=CliStat)*PeriodHours)")
> > End With
> > 
> > TotalHrs is dim'd as Single, ClassCode as String (either 4 or 5 characters
> > long), CliStat as Integer (0, 1, or 99), and the rest as Ranges.  I've tried
> > adjusting the syntax in every way I can think of and based on other examples
> > I could find, but each time I get the type-mismatch error.  Do I just have
> > something dim'd incorrectly?
> > 
> > Thanks for time and help.
> 
> -- 
> 
> Dave Peterson
> .
> 
0
Utf
3/24/2010 2:17:01 PM
If CLIStat is a number, then remove the double quotes from that portion of the
formula.

But that won't make the formula cause an error like this.  It just won't find a
match, so those rows will be ignored/treated as 0.

I'm guessing that at least one of those ranges contains errors.

Can you get the equivalent formula to work in a worksheet cell (just do it
manually)?  If you do, then share that formula in the follow-up.



Luke wrote:
> 
> I'm still getting the type-mismatch error, although now it is at the
> "TotalHrs = .Evaluate(myFormula)" line.  CLIStat is actually an Integer (0,
> 1, or 99), or Long if you prefer.  Will that make a difference in the formula
> you gave?  I did change TotalHrs to Double and CLIStat to Long, however.
> 
> "Dave Peterson" wrote:
> 
> > If you were writing the formula in a cell, it would look something like:
> > =SUMPRODUCT((HoursCodes="A")*(HourStat="x")*PeriodHours)
> >
> > Since both the classcode and clistat values are strings, they need to be
> > surrounded by double quotes.
> >
> > =chr(34) is a double quote in VBA land.
> >
> >
> >
> > Option Explicit
> > Sub testme()
> >
> >     Dim TotalHrs As Double 'don't use Single
> >     Dim ClassCode As String
> >     Dim CLIStat As String
> >     Dim myFormula As String
> >
> >     ClassCode = "A"
> >     CLIStat = "x"
> >
> >     '=SUMPRODUCT((HourCodes="a")*(HourStat="x")*PeriodHours)
> >     With Worksheets("Hours")
> >
> >         myFormula = "SUMPRODUCT(" _
> >                     & "(HoursCodes=" & Chr(34) & ClassCode & Chr(34) & ")" _
> >                    & "*(HourStat=" & Chr(34) & CLIStat & Chr(34) & ")" _
> >                    & "*PeriodHours)"
> >
> >         TotalHrs = .Evaluate(myFormula)
> >
> >     End With
> >
> >     MsgBox TotalHrs
> >
> > End Sub
> >
> > I find this more difficult to read (most the time!), but you could use it if you
> > like it better:
> >
> >         myFormula = "SUMPRODUCT(" _
> >                     & "(HoursCodes=""" & ClassCode & """)" _
> >                     & "*(HourStat=""" & CLIStat & """)" _
> >                     & "*PeriodHours)"
> >
> > ======
> > ps.  The reason I used a separate string variable for the formula is so that I
> > could step through the code, debug.print the formula to the immediate window and
> > copy|Paste into a cell in excel proper.
> >
> > Lots of times, I like to get excel's help with my formula typing errors.
> >
> > pps.  There's no reason ever to use Single or Integer.  Always use Double and
> > Long.  Modern computers will have less work to do and you don't have to worry
> > about overflow errors.
> >
> > Luke wrote:
> > >
> > > I've searched through similar topics and tried several variations of code,
> > > but I keep getting a type-mismatch error when trying to use Sumproduct.  I am
> > > wanting to use this in several parts of my code.  The first instance (which
> > > is also where the error keeps occurring) is below.
> > >
> > > With Worksheets("Hours")
> > >      TotalHrs = .Evaluate("SUMPRODUCT((HoursCodes=ClassCode)* _
> > >           (HourStat=CliStat)*PeriodHours)")
> > > End With
> > >
> > > TotalHrs is dim'd as Single, ClassCode as String (either 4 or 5 characters
> > > long), CliStat as Integer (0, 1, or 99), and the rest as Ranges.  I've tried
> > > adjusting the syntax in every way I can think of and based on other examples
> > > I could find, but each time I get the type-mismatch error.  Do I just have
> > > something dim'd incorrectly?
> > >
> > > Thanks for time and help.
> >
> > --
> >
> > Dave Peterson
> > .
> >

-- 

Dave Peterson
0
Dave
3/24/2010 3:24:12 PM
"Luke" wrote:
>      TotalHrs = .Evaluate("SUMPRODUCT((HoursCodes=ClassCode)* _
>           (HourStat=CliStat)*PeriodHours)")
> End With
> 
> TotalHrs is dim'd as Single, ClassCode as String (either 4 or 5 characters 
> long), CliStat as Integer (0, 1, or 99), and the rest as Ranges. 

You cannot reference VBA variables directly in the quoted string passed to 
Evaluate.  This is error-prone and difficult to read.  So it is prudent to 
build the Evaluate argument in a string variable that you can display in 
debug mode or print to the Immediate window.  For example:

Dim sEval as String
sEval = "SUMPRODUCT((" & HoursCodes.Address & "=" & _
   ClassCode & ")*(" & HourStat.Address & "=" & CliStat & ")," _
   & PeriodHours.Address & ")"
TotalHrs = .Evaluate(sEval)

Aside.... TotalHrs should probably be type Double, not Single.  Generally, 
all floating point variables should be type Double, especially if some of 
them will be stored into or compared with worksheet values.  The reason is 
complicated to explain fully.  In a nutshell, the conversion from Single to 
Double is imperfect.


----- original message -----

"Luke" wrote:
> I've searched through similar topics and tried several variations of code, 
> but I keep getting a type-mismatch error when trying to use Sumproduct.  I am 
> wanting to use this in several parts of my code.  The first instance (which 
> is also where the error keeps occurring) is below.
> 
> With Worksheets("Hours")
>      TotalHrs = .Evaluate("SUMPRODUCT((HoursCodes=ClassCode)* _
>           (HourStat=CliStat)*PeriodHours)")
> End With
> 
> TotalHrs is dim'd as Single, ClassCode as String (either 4 or 5 characters 
> long), CliStat as Integer (0, 1, or 99), and the rest as Ranges.  I've tried 
> adjusting the syntax in every way I can think of and based on other examples 
> I could find, but each time I get the type-mismatch error.  Do I just have 
> something dim'd incorrectly?
> 
> Thanks for time and help.
0
Utf
3/24/2010 4:08:01 PM
Errata....

I wrote:
> Dim sEval as String
> sEval = "SUMPRODUCT((" & HoursCodes.Address & "=" & _
>    ClassCode & ")*(" & HourStat.Address & "=" & CliStat & ")," _
>    & PeriodHours.Address & ")"
> TotalHrs = .Evaluate(sEval)

First, I neglected to notice that ClassCode is a String variable.  So its 
value needs to be quoted in the Evaluate string, just as it would be quoted 
in the SUMPRODUCT expression in the worksheet.  To wit:

sEval = "SUMPRODUCT((" & HoursCodes.Address & "=""" & _
    ClassCode & """)*(" & HourStat.Address & "=" & CliStat & ")," _
    & PeriodHours.Address & ")"

Second, I did not realize that Dave had posted nearly the same idea.  The 
key difference is the need for .Address when referencing the Range variables. 
 Also note that CliStat is not quoted.  I assume that the values in the 
HourStat range are numeric, not text.


----- original message -----

"Joe User" wrote:

> "Luke" wrote:
> >      TotalHrs = .Evaluate("SUMPRODUCT((HoursCodes=ClassCode)* _
> >           (HourStat=CliStat)*PeriodHours)")
> > End With
> > 
> > TotalHrs is dim'd as Single, ClassCode as String (either 4 or 5 characters 
> > long), CliStat as Integer (0, 1, or 99), and the rest as Ranges. 
> 
> You cannot reference VBA variables directly in the quoted string passed to 
> Evaluate.  This is error-prone and difficult to read.  So it is prudent to 
> build the Evaluate argument in a string variable that you can display in 
> debug mode or print to the Immediate window.  For example:
> 
> Dim sEval as String
> sEval = "SUMPRODUCT((" & HoursCodes.Address & "=" & _
>    ClassCode & ")*(" & HourStat.Address & "=" & CliStat & ")," _
>    & PeriodHours.Address & ")"
> TotalHrs = .Evaluate(sEval)
> 
> Aside.... TotalHrs should probably be type Double, not Single.  Generally, 
> all floating point variables should be type Double, especially if some of 
> them will be stored into or compared with worksheet values.  The reason is 
> complicated to explain fully.  In a nutshell, the conversion from Single to 
> Double is imperfect.
> 
> 
> ----- original message -----
> 
> "Luke" wrote:
> > I've searched through similar topics and tried several variations of code, 
> > but I keep getting a type-mismatch error when trying to use Sumproduct.  I am 
> > wanting to use this in several parts of my code.  The first instance (which 
> > is also where the error keeps occurring) is below.
> > 
> > With Worksheets("Hours")
> >      TotalHrs = .Evaluate("SUMPRODUCT((HoursCodes=ClassCode)* _
> >           (HourStat=CliStat)*PeriodHours)")
> > End With
> > 
> > TotalHrs is dim'd as Single, ClassCode as String (either 4 or 5 characters 
> > long), CliStat as Integer (0, 1, or 99), and the rest as Ranges.  I've tried 
> > adjusting the syntax in every way I can think of and based on other examples 
> > I could find, but each time I get the type-mismatch error.  Do I just have 
> > something dim'd incorrectly?
> > 
> > Thanks for time and help.
0
Utf
3/24/2010 4:16:04 PM
The sweet smell of success!  Thank you so very much.  It was the ".Address" 
part that did it.  I also used Dave's suggestion for the Chr(34) instead of 
the double quotes as my eyes start having troubles when all those quotes 
marks get together.

Whew.  No more For-Next loops evaluating each of my 1265 rows of data.

Thanks all.

"Joe User" wrote:

> Errata....
> 
> I wrote:
> > Dim sEval as String
> > sEval = "SUMPRODUCT((" & HoursCodes.Address & "=" & _
> >    ClassCode & ")*(" & HourStat.Address & "=" & CliStat & ")," _
> >    & PeriodHours.Address & ")"
> > TotalHrs = .Evaluate(sEval)
> 
> First, I neglected to notice that ClassCode is a String variable.  So its 
> value needs to be quoted in the Evaluate string, just as it would be quoted 
> in the SUMPRODUCT expression in the worksheet.  To wit:
> 
> sEval = "SUMPRODUCT((" & HoursCodes.Address & "=""" & _
>     ClassCode & """)*(" & HourStat.Address & "=" & CliStat & ")," _
>     & PeriodHours.Address & ")"
> 
> Second, I did not realize that Dave had posted nearly the same idea.  The 
> key difference is the need for .Address when referencing the Range variables. 
>  Also note that CliStat is not quoted.  I assume that the values in the 
> HourStat range are numeric, not text.
> 
> 
> ----- original message -----
> 
> "Joe User" wrote:
> 
> > "Luke" wrote:
> > >      TotalHrs = .Evaluate("SUMPRODUCT((HoursCodes=ClassCode)* _
> > >           (HourStat=CliStat)*PeriodHours)")
> > > End With
> > > 
> > > TotalHrs is dim'd as Single, ClassCode as String (either 4 or 5 characters 
> > > long), CliStat as Integer (0, 1, or 99), and the rest as Ranges. 
> > 
> > You cannot reference VBA variables directly in the quoted string passed to 
> > Evaluate.  This is error-prone and difficult to read.  So it is prudent to 
> > build the Evaluate argument in a string variable that you can display in 
> > debug mode or print to the Immediate window.  For example:
> > 
> > Dim sEval as String
> > sEval = "SUMPRODUCT((" & HoursCodes.Address & "=" & _
> >    ClassCode & ")*(" & HourStat.Address & "=" & CliStat & ")," _
> >    & PeriodHours.Address & ")"
> > TotalHrs = .Evaluate(sEval)
> > 
> > Aside.... TotalHrs should probably be type Double, not Single.  Generally, 
> > all floating point variables should be type Double, especially if some of 
> > them will be stored into or compared with worksheet values.  The reason is 
> > complicated to explain fully.  In a nutshell, the conversion from Single to 
> > Double is imperfect.
> > 
> > 
> > ----- original message -----
> > 
> > "Luke" wrote:
> > > I've searched through similar topics and tried several variations of code, 
> > > but I keep getting a type-mismatch error when trying to use Sumproduct.  I am 
> > > wanting to use this in several parts of my code.  The first instance (which 
> > > is also where the error keeps occurring) is below.
> > > 
> > > With Worksheets("Hours")
> > >      TotalHrs = .Evaluate("SUMPRODUCT((HoursCodes=ClassCode)* _
> > >           (HourStat=CliStat)*PeriodHours)")
> > > End With
> > > 
> > > TotalHrs is dim'd as Single, ClassCode as String (either 4 or 5 characters 
> > > long), CliStat as Integer (0, 1, or 99), and the rest as Ranges.  I've tried 
> > > adjusting the syntax in every way I can think of and based on other examples 
> > > I could find, but each time I get the type-mismatch error.  Do I just have 
> > > something dim'd incorrectly?
> > > 
> > > Thanks for time and help.
0
Utf
3/24/2010 4:43:02 PM
I thought that HoursCode and HourStat were both range names in the worksheet.

Sorry.

Luke wrote:
> 
> The sweet smell of success!  Thank you so very much.  It was the ".Address"
> part that did it.  I also used Dave's suggestion for the Chr(34) instead of
> the double quotes as my eyes start having troubles when all those quotes
> marks get together.
> 
> Whew.  No more For-Next loops evaluating each of my 1265 rows of data.
> 
> Thanks all.
> 
> "Joe User" wrote:
> 
> > Errata....
> >
> > I wrote:
> > > Dim sEval as String
> > > sEval = "SUMPRODUCT((" & HoursCodes.Address & "=" & _
> > >    ClassCode & ")*(" & HourStat.Address & "=" & CliStat & ")," _
> > >    & PeriodHours.Address & ")"
> > > TotalHrs = .Evaluate(sEval)
> >
> > First, I neglected to notice that ClassCode is a String variable.  So its
> > value needs to be quoted in the Evaluate string, just as it would be quoted
> > in the SUMPRODUCT expression in the worksheet.  To wit:
> >
> > sEval = "SUMPRODUCT((" & HoursCodes.Address & "=""" & _
> >     ClassCode & """)*(" & HourStat.Address & "=" & CliStat & ")," _
> >     & PeriodHours.Address & ")"
> >
> > Second, I did not realize that Dave had posted nearly the same idea.  The
> > key difference is the need for .Address when referencing the Range variables.
> >  Also note that CliStat is not quoted.  I assume that the values in the
> > HourStat range are numeric, not text.
> >
> >
> > ----- original message -----
> >
> > "Joe User" wrote:
> >
> > > "Luke" wrote:
> > > >      TotalHrs = .Evaluate("SUMPRODUCT((HoursCodes=ClassCode)* _
> > > >           (HourStat=CliStat)*PeriodHours)")
> > > > End With
> > > >
> > > > TotalHrs is dim'd as Single, ClassCode as String (either 4 or 5 characters
> > > > long), CliStat as Integer (0, 1, or 99), and the rest as Ranges.
> > >
> > > You cannot reference VBA variables directly in the quoted string passed to
> > > Evaluate.  This is error-prone and difficult to read.  So it is prudent to
> > > build the Evaluate argument in a string variable that you can display in
> > > debug mode or print to the Immediate window.  For example:
> > >
> > > Dim sEval as String
> > > sEval = "SUMPRODUCT((" & HoursCodes.Address & "=" & _
> > >    ClassCode & ")*(" & HourStat.Address & "=" & CliStat & ")," _
> > >    & PeriodHours.Address & ")"
> > > TotalHrs = .Evaluate(sEval)
> > >
> > > Aside.... TotalHrs should probably be type Double, not Single.  Generally,
> > > all floating point variables should be type Double, especially if some of
> > > them will be stored into or compared with worksheet values.  The reason is
> > > complicated to explain fully.  In a nutshell, the conversion from Single to
> > > Double is imperfect.
> > >
> > >
> > > ----- original message -----
> > >
> > > "Luke" wrote:
> > > > I've searched through similar topics and tried several variations of code,
> > > > but I keep getting a type-mismatch error when trying to use Sumproduct.  I am
> > > > wanting to use this in several parts of my code.  The first instance (which
> > > > is also where the error keeps occurring) is below.
> > > >
> > > > With Worksheets("Hours")
> > > >      TotalHrs = .Evaluate("SUMPRODUCT((HoursCodes=ClassCode)* _
> > > >           (HourStat=CliStat)*PeriodHours)")
> > > > End With
> > > >
> > > > TotalHrs is dim'd as Single, ClassCode as String (either 4 or 5 characters
> > > > long), CliStat as Integer (0, 1, or 99), and the rest as Ranges.  I've tried
> > > > adjusting the syntax in every way I can think of and based on other examples
> > > > I could find, but each time I get the type-mismatch error.  Do I just have
> > > > something dim'd incorrectly?
> > > >
> > > > Thanks for time and help.

-- 

Dave Peterson
0
Dave
3/24/2010 5:25:34 PM
Reply:

Similar Artilces:

Business Portal Error-SQL server does not exist or access denied
Hi, We are running business portal 4.0 for one of our customer. It was running correctly, however, they have changed the SQL server port (previously it was set as default 1433). After that the business portal becomes very slow and while creating a new request (purchase requisition) if we open the item pop up; it is showing exception "SQL Server does not exist or access denied...." Can any body tell me how can I provide the new port number to business portal connection to the database. Thanks and Regards, Waliullah, Thanks for using the newsgroups. I have a...

Sort by Credit Card Type on EDC Detail Report
What do I have to do to get my EDC Detail Report to show different credit card types, like Visa, MC or Amex. Now under Tender Type, it just shows Credit card...well...I kinda knew that. How do I fix it? add tender types for each credit card type rather than just "credit card". then z out. all transactions after this change will reflect what you want in the reports. "tl" <tl@discussions.microsoft.com> wrote in message news:A54EEC95-208C-4962-A089-84B22A67632A@microsoft.com... > What do I have to do to get my EDC Detail Report to show different credit >...

Using mouse wheel in VB editor
Just tried using the mouse wheel to scroll within visual basic editor in excel but it wont let me. How can I turn this feature on. I am using Excel 2003. Thanks in advance. This is a known problem with the latest MS mouse drivers. You can either install version 4 of the drivers (current version is 5), or use FreeWheel, and freeware program at http://www.geocities.com/SiliconValley/2060/freewheel.html . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "fullers" <fullers@discussions.microsoft.com> wrote in message ne...

Send to certain person using specific email account
I send an email to a certain receipient but don't want to use the default email account. Is it possible that when I send to them (plain text) , that it will choose the other account I want to use to send it? Please advise how I set this up. Thanks. I do not believe that this can be achieved automatically through standard use of Outlook as you need to manually specify the name of the account to send from if different than the default. However, it would be possible using the Outlook object model. An Addin would need to be developed that monitored each mail item prior to sending. If the d...

crm 3.0 error 03-01-06
Hello, I'me getting this error while installing crm3.0 for SBS: "error writing to file microsoft.mshtml.dll verify that you have access to that directory" That file is in the C:\Program Files\Microsoft.NET\Primary Interop Assemblies directory. I (and 'everyone') has full access to that dir. What can I do about this?? kind regards, Thomas ...

Use exchange from a trusted domain
Hi folks, I have domain a and domain b. Domain a is all set upp with exchange etc. Domain b was bought, and they don't have exchange. I have trust between the domains and file sharing and authentication are ok. What we want to do is, make users on domain b and let them use the exchange (on a seperate storage) on domain a. The users on domain b must logon to domain b, but need to have mailbox on domain a. Domain a are running on win2003 and exchange 2003. Domain are running on win2003 with forest and domain ready for exchange. Any advices? thanks in advance, - Bjarni Hi, Thi...

Error in Outlook Today
Whenever I go to Outlook Today, I get a runtime error, line: 297 Error: Class Not Registered. Then I get the error two more times when I click 'Customize Outlook Today...' and the list 'Show Outlook Today In This Style' is empty, and the box under it has a broken image icon. What could be the cause of this? Sorry...forgot to say...I'm using Outlook 2003 Student and Teacher Edition on Windows XP. >-----Original Message----- >Whenever I go to Outlook Today, I get a runtime error, >line: 297 Error: Class Not Registered. Then I get the >error two more time...

Using Access 2003 on client to see Sharepoint 2007
Testing a Sharepoint environment and I can easily manipulate and run the database from Access 2007, but when I try to use Access 2003 it wants me to save the database to a location. The Access 2003 has the compatiblity pack, but it seems to not work when I download it. So the question is using Sharepoint 2007 and posting the Access 2007 format to it, can a user go to the sharepoint site and use a 2003 Access database that has the compatibility pack and open without having to download it? -- Message posted via http://www.accessmonster.com Send check for $500.00 to: David A Jenn...

Re: 'Uknown Error 0x800CCC97'
I just heard back from the folks with whom I filed this bug. They say the bug is fixed in cppop 5.4 - request that your ISP upgrade to that. -- Jeff Stephenson Outlook Development This posting is provided "AS IS" with no warranties, and confers no rights "Jeff Stephenson [MSFT]" <stephenson@online.microsoft.com> wrote in message news:... > See the attached reply to another similar question. Your ISP's POP3 server > has a bug, and they should get a fixed version of the server. > > -- > Jeff Stephenson > Outlook Development > This posting...

Mother of a sumproduct (ish) problem!
At least it is to me - now I humbly beseech your magic :) A1 to A10 contain 10 unique letters, e.g. A,Q,E... B1 to B10 contain 10 unique letters, e.g. Z,B,A... C1 to C100 contain nonunique letters, e.g. E,E,F,E,Q,Q... D1 to D100 contain nonunique letters, e.g. A,T,F,A,X,B... E1 to E100 contain values, e.g. 9,1,3,5,7,2... In pseudo logic, for x = 1 to 10 I want to find each Ax:Bx pairing in Cy:Dy and sum E when matched. So I want to sum Ey for the rows where [Cy is "A" and Dy is "Z"], plus each Ey where [Cy="Q" and Dy="B"], etc. for the sample...

stop error defeating me
Hi, XP Pro PC. When I start the computer I can start in safe mode but when I try to start in normal mode it loads to the log on screen. I type the username and password in then it starts to load but stops after a few moments with a blue screen. The error is Stop: c000021a (fatal system error) The windows subsystem system process terminated unexpectedly with a status of 0xc0000005 (0x7c9106c3 0x0055f36c). Begininning dump of physical memory. I have uninstalled AVG, also taken out the graphics card and uninstalled all the drivers for it. I have also changed the RAM. I have also d...

Error in database....
A user posted a batch in payables management. After posting, there was an error encountered. It displays that the table updating was interrupted, use batch recovery to continue the posting. But when I used the batch recovery, it was not successful to continue the update process. When I click the "More Details" button it displays, A save operation on table 'PM_Transaction_WORK' caused a sharing error. How can I resolve this issue? Thanks, John John, it is a db sharing violation. Have all users logout DELETE tempdb..DEX_LOCK DELETE tempd..DEX_SESSION DELETE dynami...

How Use ShellExecute with a CMemFile
Hello, I need to store files of various types (such as .doc, .xls, .jpg, .pdf, ..wmv, ...) in a database. Upon retrieval of such a file I have the file as data in memory in a CMemFile. I need to then "display" the file in the aproriate application. If I were to write the CMemFile to disk and create an actual file I could then, of course, use ShellExecute() to launch , say Word to display a .doc file. However, I would much prefer to avoid the overhead of writing the data to an actual file and then dealing with having to detect when the User is done and cleaning up the file I had ...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

error 553
The following error occurs when sending email from my business domain. It does not occur when sending through my roadrunner account. The following recipient(s) could not be reached: on 10/22/2003 2:05 PM 553 sorry, that domain isn't in my list of allowed rcpthosts (#5.7.1) What does this mean and how can it be fixed? ...

80070005 error #2
I am getting this error when trying to view public folder property from system manager. My issue is same as what you can find out from http://forums.msexchange.org/ultimatebb.cgi? ubb=get_topic;f=19;t=000114 Anyone has a clue? ...

Help about numeric type field. Thanks.
I created a SQL Server 2005 CE DB ( .sdf, version 3.0) with vs2005. And I created a table which has 2 fields: fld1 - int, fld2 - numeric(38,25). But I encounted an error messagebox when I tried to insert a record (4005,9000000). The msgbox said Conversion overflows. The setting for my numeric fld2 is (precision=38,scale=25). So why occur error when to insert 9000000? Thanks in advance. ...

Using INDIRECT within functions
I learned a bit from JLathman in a previous post but tried to use the same methodology with this formula (also learned from this Group to get the last entered value in a column) without much luck. The original formula was this with 2009 sheet being static: =LOOKUP(2,1/('2009'!H3:H65536<>""),'2009'!H3:H65536) Trying to make it dynamic I tried the following without success: =LOOKUP(INDIRECT("2,1/(" & O1 & "!$H$3:$H$65536<>"")," & O1 & "!$H$3:$H$65536)")) Did I miss something or are...

How to use count for calls per hour
I am trying to create a pivot table and chart that will show me the # of calls per hour by day for a range of dates Aug 1 - Sept 19. My data looks like this where each line is the date and time of the call: How do I get a count of the # of the calls per hour by day? 8/1/08 12:48 AM 8/1/08 1:53 AM 8/1/08 2:36 AM 8/1/08 3:24 AM 8/1/08 3:37 AM 8/1/08 4:04 AM 8/1/08 4:44 AM 8/1/08 4:55 AM 8/1/08 4:58 AM 8/1/08 7:02 AM 8/1/08 7:28 AM 8/1/08 7:43 AM 8/1/08 7:47 AM 8/1/08 7:56 AM 8/1/08 7:58 AM 8/1/08 8:13 AM 8/1/08 8:33 AM 8/1/08 9:07 AM 8/1/08 10:28 AM 8/1/08 10:38 AM 8/1/08 10:44 AM 8/1/08 ...

How export all mailbox-enabled users from the GAL using Outlook?
Hello, We have a user which, for administrative reasons, needs to export all mailbox-enabled users in the GAL now and then. They just need all the names. Is there a straightforward way for them to do that? We don't want to give them any special permissions and want to avoid server scripting. Ideally, they should be able to export it to a CSV file or any text file. Thanks, - Alan. Alan wrote: > We have a user which, for administrative reasons, needs to export all > mailbox-enabled users in the GAL now and then. They just need all the > names. Is there a straightforward way fo...

SQL 2008 running on a VM using all allocated memory
Hi, I've got a sql2008 server running on a VM. There's 9GB of physical RAM, which 7GB have been allocated to SQL Server. But when i look at task manager, i see that the SQL server is actually using all 7GB, which is pegging the memory usage of the overal box at above 90% used. We're mostly a sql2005 shop, and none of those servers are doing this. I have sql2005 running on VM's, someone actual servers as a named or default instance, and some even clustered. None of them have this problem. Is this a normal thing with sql2008 only? Any insight would be greatly appre...

Anyone use Promys CRM software integrated with GP Dynamics?
Our company is considering using Promys as a CRM to create quotes and sales orders for the sales team. I am concerned about the integration with GP and what the pitfalls may be. Is anyone here currently using Promys with GP Dynamics? ...

Loading Text File to TextBox using LoadFromFile
Hi All, I'm creating a form that allows the user to pick a txt file (dialog) and then display the path and contents on the form. The code has been cobbled together as I found the pieces that worked, so bear with. I got the file picker working and displaying the file name on the form, but the file contents won't display. I had a feeling the problem had to do with importing a namespace (see the error in the code when I tried "Imports System.IO") or with a missing reference. Using Access 2003. References: VB for Apps, MS Access 11 Obj Lib, OLE Auto, MS V...

Can't use openURL or access internet on some locations
I have previously raised a question where openUrl threw an exception in a specific office location. However the problem I have now is that the application just freeze when I call openURL. It seems as it is waiting for something. This only happens at one company so far the company does have a proxy but so do I at work and I have no problems. I have also tried the Microsoft TEAR sample and it behaves the exact same way, it says "Opening internet...Connection made" and then it just stops. If I run it on my computer it gets the webpage and everything finishes ok. I have tried to set t...

CRM Error
Hello When a user replies to an CRM email, clicks the "reply" button or the "reply all" button, clicks in the body of the email message and clicks "insert template", this error appears. This does not happen every time, and happens to various users. Does anyone know why we would get this error? ...