Sumproduct formula works in spreadsheet, NOT VBA. Help please
Basically, I want to replace this formula with VBA Code
I can get this one to work to sum
=SUMPRODUCT(--(DataTime="First day of employment (Time
1)"),--(DataPosition=N6),(DataQuestion1)
When I change this to Count rather than Sum, my Code Evaluates as an Error
=SUMPRODUCT(--(DataTime="First day of employment (Time
1)"),--(DataPosition="Registered Nurse"),--(DataQuestion1<>"*"))
The only line of code I switch out is the last mFormula row. The line of
code that works I've commented out. I am really stumped...
This is the code that I have..
Dim mTimeCriteria As String
Dim mPositionCriteria As String
Dim mQuestion1Criteria As String
Dim mTimeRange As Range
Dim mPositionRange As Range
Dim mQuestion1Range As Range
Dim mFormula As String
Dim Kountifs As Variant 'could be an error
mTimeCriteria = "First day of employment (Time 1)"
mPositionCriteria = "Registered Nurse"
mQuestion1Criteria = "*"
With Worksheets("Data")
Set mTimeRange = .Range("DataTime")
Set mPositionRange = .Range("DataPosition")
Set mQuestion1Range = .Range("DataQuestion1")
mFormula = "SUMPRODUCT(--(" & mTimeRange.Address & "=" & Chr(34) &
mTimeCriteria & Chr(34) & "),"
mFormula = mFormula & "--(" & mPositionRange.Address & "=" & Chr(34) &
mPositionCriteria & Chr(34) & "),"
mFormula = mFormula & "-- (" & mQuestion1Range.Address & "<>" & Chr(34)
& mQuestion1Criteria & Chr(34) & ") "
'mFormula = mFormula & mQuestion1Range.Address & ")" 'This sums
correctly
' MsgBox mFormula
Kountifs = .Evaluate(mFormula)
End With
If IsError(Kountifs) Then
MsgBox "Error in evaluating"
Else
MsgBox Kountifs
End If
|
0 |
|
Utf
11/21/2009 2:35:02 AM
excel.programming
6508 articles. 
2 followers.
3 Replies
698 Views
Hi
You miss a closing paranthesis in your formula.
> mFormula = mFormula & "-- (" & mQuestion1Range.Address & "<>" & Chr(34)
> & mQuestion1Criteria & Chr(34) & ")" ' <=== Two closing paranthesis needed
> here.
Regards,
Per
"DogLover" <DogLover@discussions.microsoft.com> skrev i meddelelsen
news:B09154BB-93F2-4C2F-BAC5-1DE75AF001DE@microsoft.com...
> Basically, I want to replace this formula with VBA Code
>
> I can get this one to work to sum
> =SUMPRODUCT(--(DataTime="First day of employment (Time
> 1)"),--(DataPosition=N6),(DataQuestion1)
>
> When I change this to Count rather than Sum, my Code Evaluates as an Error
> =SUMPRODUCT(--(DataTime="First day of employment (Time
> 1)"),--(DataPosition="Registered Nurse"),--(DataQuestion1<>"*"))
>
> The only line of code I switch out is the last mFormula row. The line of
> code that works I've commented out. I am really stumped...
>
> This is the code that I have..
>
> Dim mTimeCriteria As String
> Dim mPositionCriteria As String
> Dim mQuestion1Criteria As String
> Dim mTimeRange As Range
> Dim mPositionRange As Range
> Dim mQuestion1Range As Range
> Dim mFormula As String
> Dim Kountifs As Variant 'could be an error
>
> mTimeCriteria = "First day of employment (Time 1)"
> mPositionCriteria = "Registered Nurse"
> mQuestion1Criteria = "*"
>
> With Worksheets("Data")
> Set mTimeRange = .Range("DataTime")
> Set mPositionRange = .Range("DataPosition")
> Set mQuestion1Range = .Range("DataQuestion1")
>
> mFormula = "SUMPRODUCT(--(" & mTimeRange.Address & "=" & Chr(34) &
> mTimeCriteria & Chr(34) & "),"
> mFormula = mFormula & "--(" & mPositionRange.Address & "=" & Chr(34) &
> mPositionCriteria & Chr(34) & "),"
> mFormula = mFormula & "-- (" & mQuestion1Range.Address & "<>" & Chr(34)
> & mQuestion1Criteria & Chr(34) & ") "
>
> 'mFormula = mFormula & mQuestion1Range.Address & ")" 'This sums
> correctly
>
> ' MsgBox mFormula
>
> Kountifs = .Evaluate(mFormula)
>
> End With
>
> If IsError(Kountifs) Then
> MsgBox "Error in evaluating"
> Else
> MsgBox Kountifs
> End If
|
|
0 |
|
Per
11/21/2009 9:00:52 AM
Thanks. That was it.
"Per Jessen" wrote:
> Hi
>
> You miss a closing paranthesis in your formula.
>
> > mFormula = mFormula & "-- (" & mQuestion1Range.Address & "<>" & Chr(34)
> > & mQuestion1Criteria & Chr(34) & ")" ' <=== Two closing paranthesis needed
> > here.
>
>
> Regards,
> Per
>
>
>
> "DogLover" <DogLover@discussions.microsoft.com> skrev i meddelelsen
> news:B09154BB-93F2-4C2F-BAC5-1DE75AF001DE@microsoft.com...
> > Basically, I want to replace this formula with VBA Code
> >
> > I can get this one to work to sum
> > =SUMPRODUCT(--(DataTime="First day of employment (Time
> > 1)"),--(DataPosition=N6),(DataQuestion1)
> >
> > When I change this to Count rather than Sum, my Code Evaluates as an Error
> > =SUMPRODUCT(--(DataTime="First day of employment (Time
> > 1)"),--(DataPosition="Registered Nurse"),--(DataQuestion1<>"*"))
> >
> > The only line of code I switch out is the last mFormula row. The line of
> > code that works I've commented out. I am really stumped...
> >
> > This is the code that I have..
> >
> > Dim mTimeCriteria As String
> > Dim mPositionCriteria As String
> > Dim mQuestion1Criteria As String
> > Dim mTimeRange As Range
> > Dim mPositionRange As Range
> > Dim mQuestion1Range As Range
> > Dim mFormula As String
> > Dim Kountifs As Variant 'could be an error
> >
> > mTimeCriteria = "First day of employment (Time 1)"
> > mPositionCriteria = "Registered Nurse"
> > mQuestion1Criteria = "*"
> >
> > With Worksheets("Data")
> > Set mTimeRange = .Range("DataTime")
> > Set mPositionRange = .Range("DataPosition")
> > Set mQuestion1Range = .Range("DataQuestion1")
> >
> > mFormula = "SUMPRODUCT(--(" & mTimeRange.Address & "=" & Chr(34) &
> > mTimeCriteria & Chr(34) & "),"
> > mFormula = mFormula & "--(" & mPositionRange.Address & "=" & Chr(34) &
> > mPositionCriteria & Chr(34) & "),"
> > mFormula = mFormula & "-- (" & mQuestion1Range.Address & "<>" & Chr(34)
> > & mQuestion1Criteria & Chr(34) & ") "
> >
> > 'mFormula = mFormula & mQuestion1Range.Address & ")" 'This sums
> > correctly
> >
> > ' MsgBox mFormula
> >
> > Kountifs = .Evaluate(mFormula)
> >
> > End With
> >
> > If IsError(Kountifs) Then
> > MsgBox "Error in evaluating"
> > Else
> > MsgBox Kountifs
> > End If
>
> .
>
|
|
0 |
|
Utf
11/21/2009 1:09:01 PM
Check your other post.
In fact, if you're going to repost your question, please go back to the other
threads and say that you reposted.
That way, others won't waste their time duplicating answers.
DogLover wrote:
>
> Basically, I want to replace this formula with VBA Code
>
> I can get this one to work to sum
> =SUMPRODUCT(--(DataTime="First day of employment (Time
> 1)"),--(DataPosition=N6),(DataQuestion1)
>
> When I change this to Count rather than Sum, my Code Evaluates as an Error
> =SUMPRODUCT(--(DataTime="First day of employment (Time
> 1)"),--(DataPosition="Registered Nurse"),--(DataQuestion1<>"*"))
>
> The only line of code I switch out is the last mFormula row. The line of
> code that works I've commented out. I am really stumped...
>
> This is the code that I have..
>
> Dim mTimeCriteria As String
> Dim mPositionCriteria As String
> Dim mQuestion1Criteria As String
> Dim mTimeRange As Range
> Dim mPositionRange As Range
> Dim mQuestion1Range As Range
> Dim mFormula As String
> Dim Kountifs As Variant 'could be an error
>
> mTimeCriteria = "First day of employment (Time 1)"
> mPositionCriteria = "Registered Nurse"
> mQuestion1Criteria = "*"
>
> With Worksheets("Data")
> Set mTimeRange = .Range("DataTime")
> Set mPositionRange = .Range("DataPosition")
> Set mQuestion1Range = .Range("DataQuestion1")
>
> mFormula = "SUMPRODUCT(--(" & mTimeRange.Address & "=" & Chr(34) &
> mTimeCriteria & Chr(34) & "),"
> mFormula = mFormula & "--(" & mPositionRange.Address & "=" & Chr(34) &
> mPositionCriteria & Chr(34) & "),"
> mFormula = mFormula & "-- (" & mQuestion1Range.Address & "<>" & Chr(34)
> & mQuestion1Criteria & Chr(34) & ") "
>
> 'mFormula = mFormula & mQuestion1Range.Address & ")" 'This sums
> correctly
>
> ' MsgBox mFormula
>
> Kountifs = .Evaluate(mFormula)
>
> End With
>
> If IsError(Kountifs) Then
> MsgBox "Error in evaluating"
> Else
> MsgBox Kountifs
> End If
--
Dave Peterson
|
|
0 |
|
Dave
11/21/2009 1:35:18 PM
Similar Artilces:
Help! Outlook 2000 multiple e-mail accounts.
Hi, I am using Outlook 2000. I have set up five different email accounts and when I click on "Send/Receive" I can see the five accounts listed, but when I click on any one of them, the Inbox does not change, it stays with the Default Inbox. Even the Login window appears and I enter the login info, it still stays with the Default Inbox. At this point I cnanot access other e-mail accounts other than the Default one which appears everytime I open Outlook. Any suggestions/comments/assistances is much appreciated. Thank you. Thomas Do you have Outlook 2000 configured in Internet Mail ...
Embedded spreadsheet
I have embedded spreadsheets that produce a stoplight function for project status - Red, Yellow, Green Each spreadsheet is identified with a project name. I have an intro slide that depicts the summary of each project with the stoplight status. The following/supporting slides show the individual projects with its respective stoplight spreadsheet. What I want to do: Link the individual spreadsheets to the spreadsheets in the summary slide. Thus, when I change the status in the summary slide, the corresponding spreadsheet in the individual project slide changes as well. In oth...
Hiding and Unhiding Rows.. Unhide does not work..
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I use this formating feature quite often. But, on this one worksheet which is a rather large data list (6000 Rows) I can NOT unhide rows that I have previously hidden. It just does not work. <br><br>I have other workbooks open and I have no problem hiding and unhiding in the normal way. <br><br>The workbook is NOT protected. The cells are not locked. But the file was originally a windows file. <br><br>Is there some switch I do not know about? This is possibly a rare occurren...
Help: Budget doesn't load bills for entire year
Budget reports only show the bill from the month it was created August and forward. Since I just upgraded from Quicken, all of the bills were paid before this month. How can I make money realize that the budget should include these as well. ...
Macros
I'm getting confused. I built two macros for a given spreadsheet and inserted them on a custom toolbar. When we copy the spreadsheet, giving it a new name, and then open the copy and run the macros - excel opens a new spreadsheet with the name of the original. I'm guessing that the macros are assigned specifically to a particular spreadsheet somehow? How do you suggest I work around this? I'd like to copy the spreadsheet and have the macro work in the newly created spreadsheet too. Even if it entails some minor coding changes, thats fine - or is there a way to tell the macr...
Amex/Costco account update not working
Wondering if anyone else is having issues update and Amex/Costco account in 2006 version. It was working on 2004 version but has been broken since the upgrade. Andrew, Downloads from Amex/Costco work fine here. Explain what "issues" are you experiencing? Kevin "Andrew" <justme@nowhere.com> wrote in message news:42efdc69_1@newsfeed.slurp.net... > Wondering if anyone else is having issues update and Amex/Costco account > in 2006 version. It was working on 2004 version but has been broken since > the upgrade. > > Kevin, I had problems wi...
PLEASE HELP--ERROR MESSSAGE
I have two error messages. The reason why I am getting this is because I got a new computer and I copied my old outlook information and put it in the new computer. Everytime I open my outlook this message appears and the only thin I have to do is click ok and everything works. But how do I fix it? I did reload outlook serval times and there error message still comes even if I do not put my old information in it. Error 1: "The add-in "C:\Program Files\Microsoft Office\Office\SBCMSYNC.DLL" could not be installed or loaded. This problem may be resolved by using Detect and Repai...
Formula query #2
I am trying to identify a formula which will add and then display th number of times a number appears in a range. For example, the range looks like this .......A...........B............C 1...1110......1110.......1114 2...1110..... 1111.......1111 3...1111......1112.......1110 4...1111......1113.......1111 The numbers which appear in the applicable range e.g. 1110, could b any number between 1110 and 9999 and as such I cannot simply predefin which number to look for. I am hoping the output could like as follows: .......A........B 1....1110....4 2....1111....5 3....1112....1 4.....
if formula #2
i have this formula in a cell D3 =IF(A1="F",B1&B2) how can i make it multiple for example i want to add this IF(a2="G",B1&B3) in D3 also. thanks Either - =IF(AND(A1="F",A2="G"),B1&B3,) =IF(OR(A1="F",A2="G"),B1&B3,) Regards. Bill Ridgeway Computer Solutions "jaypee" <jaypee2cool@yahoo.com> wrote in message news:O3NhGKIhGHA.1272@TK2MSFTNGP03.phx.gbl... >i have this formula in a cell D3 =IF(A1="F",B1&B2) > how can i make it multiple for example i want to add this IF(a2="G&q...
Inserting formulas with PL/SQL
I'm not familiar with PL/SQL, but my friend is using it to place data into an Excel spreadsheet. She can place strings into the spreadsheet, but cannot figure out how to put in formulas. Specifically, she is trying to place web links into a column of cells. She is able to put in 'http://www...' as a string, but then cannot link from the cell. I suggested using Excel's hyperlink function, but again it ends up as a string, not a function. .... .... owa_util.mime_header(ccontent_type =>'application/vnd.ms-excel'); htp.p('ID;ORACLE'); htp.p('...
How to make a column of formulas all ROUND
I created a spreadsheet in which I have a column of formulas. Most of these fomulas are simply pulling a single number off another sheet. I want to make all the formulas ROUND versions of the existing formula without having to go into each cell and making the change. They are not in order to which I can just make the first fomula a ROUND fomula and copy down. So, is there a way to select a range of cells and make the existing fomulas all ROUND versions? Thanks. Would this help? Sub RoundAdd() Dim mystr As String Dim cel As Range For Each cel In Selection If...
Office 2001 for Mac help needed!
As an undergrad, I received a copy of Microsoft Office 2001 for Mac and it is installed on my iMac that I used there. However, I just bought a new MacBook for graduate school and need to install Office on that computer now, but when I put the disk in and double click on install it wont work. All of the icons have an x on them and I believe it says it "This application cannot run on this system" or something to that effect. HELP! I need Office asap since classes start this week! Thank you Laura Hi Laura - Your best bet is to hie thee to the university bookstore & get the u...
reset button in spreadsheet
Is it possible to place a button in a spreadsheet that will undo all the changes that have been made since the spreadsheet was opened? Thanks, Clem. Clem There is one. You will see it up in right-hand corner. It is an "X" and when clicked, will close the workbook. Click NO when asked to save changes. Gord Dibben MS Excel MVP On Sat, 10 Feb 2007 14:53:08 -0500, "C.M.G." <cmg@ohiorepromed.moc> wrote: > >Is it possible to place a button in a spreadsheet that will undo all the >changes that have been made since the spreadsheet was opened? > &...
Hlp! Requery Cbo Not Working
Hello, I'm using Access 2000. I added a combo box to enable users to select an existing employee name. The source of the form is a query that contains the last&", "&first to concatenate the two fields so they show correctly in the combo box. Everything is fine until I add or delete an employee. The combo box doesn't update automatically, so I added the following event in the form's After Update: Private Sub Form_AfterUpdate() Me!cboName.Requery End Sub However, now the combo and the employee records no longer are in synch. Nothing at ...
Reminder is not work in default folder #2
I used as follow coding to send email to outlook client(outlook 2000): ''''''''''''''''''''''''''''''=AD'''''''''''''''' Dim ll As New CDO.Message Dim bb As New CDO.Configuration bb.Fields(cdoSMTPServer) =3D "nc80012" bb.Fields(cdoSendUsingMethod) =3D cdoSendUsingPort bb.Fields.Update Set ll.Configuration =3D bb ll.Fields.Append "urn:schemas:mailheader:x-mess=ADage-flag", adBSTR _ ...
EXCEL FORMULA #28
Good afternoon, I'm trying to fine a formula which would show me how much money I would save on a mortgage if I were to pay additional principal each month--in addition to paying the additional principal how long would it take to pay off. I'm looking at a 160k mortgage at 7.5 for 30 years. I'll like to pay this off as soon as possible by paying additional principal each month. There are tons of free templates at: http://office.microsoft.com/en-us/default.aspx Maybe you'll find something you like. Kam1999i wrote: > > Good afternoon, > > I'm ...
short cut for copy past w formulas??
I hope I can explain this right. If Sheet 1 cell 1A were to equal Sheet 2 Cell 1A I need a quick way to copy that formula so on sheet 1 cell 9A will = sheet 2 cell 2A , sheet 1 row 17 A = Sheet 2 Cell 3A and so on .. If i do a straight copy past it equals the row on sheet 2 which is no good to me.. I only have about 300 cells to do so any short cut would help. Thanks in advance. In sheet1 cell A1 enter the formula =OFFSET(Sheet2!$A$1,TRUNC((ROW(A1)-1)/8),0) Then copy this down to every 8n+1'th row -- Return email address is not as DEEP as it appears "DR, Bob" <bob@mai...
Help on percentages greater than 100
Thanks in advance for your assistance. I have a fairly larger list of numbers that needs to be divided to get a percntage. Due to restrictions, accesses etc some of the numbers which were accurate at the time will result in a number greater that 100%. These values (%) will eventually end up on a graph and should not exceed 100%. A B C 1 88 89 =b1/a1 (101%) 2 88 88 =b2/b2 (100%) the ( ) above are the results. What I need is a way to show the numbers that are > 100% but limit th...
Word problems 2003 and 2007 version
hey everyone both on my main computer and my brothers laptop word isnt working! its so annoying! on my desktop PC i have windows xp and word 2003. after getting a virus which stopped the internet from working i had to do a system restore - which got rid of the virus. but when i click on word it says that "this shortcut only applies to installed products", and when i go to word through program files it says "this product is not installed on this user" - but there is no setup thing i can press - will i need the disc maybe? on my bro's laptop he got the sa...
Formula for Date
I'm new to formulas and just want to display the current date in my outlook form (e.g., December 18, 2004). What I've done is created a combination text field where I have the following fields: [Email Opening Date] [Full Name] [Job Title] [Company] [Business Address] Dear [Full Name]: When I send a new message, I then copy the values into my email instead of copying the data (name, title, address, salutation) one field at a time. This allows me to personalize the email. The problem is that I do not know what to do to with formulas to show the current date as I note above. Thank...
help
Hi, I'm trying to turn the warning message off when I use: Cells.Replace What:="Z Divisional Totals", Replacement:="Divisional Totals", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Second part is: Is there a way to check either the column and rows filled to create border where is pointed to "APPLYBORDER". Your help would be much apprecated. r = Range("A10").End(xlDown).Row ApplyBorder Range("A10", "A" & r) ApplyBorder Range("B10", "B" & r) ApplyBorder Range("C10", "C&qu...
Finding the last line on a spreadsheet
If hitting the Ctrl - Home buttons will find the first entry on a spreadsheet, what is the quickest way to find the last line. does anyone know of a website with these types of shortcuts. Ctrl-End will send you to the last line. in the Excel Help, select "Hotkey" and you'll get a list of all the different keyboard shortcuts. "derwood" <darren.irvine@gmail.com> wrote in message news:1131173033.742797.236360@g49g2000cwa.googlegroups.com... > If hitting the Ctrl - Home buttons will find the first entry on a > spreadsheet, what is the quickest way to find ...
Editable Excel Spreadsheet Online?
Hi, I tried to recent find information on this, but could find very little. How difficult would it be to host an excel spreadsheet online where visitors to the site can directly view and edit it? Right now, I can upload the spreadsheet to our web site and visitors can view it, but if they edit it, they can only save it to their local drive. I would like the users to be able to save the copy on the server. What would be involved in something like this? I assume for starters (if it's do-able) we'd need Windows hosting (we're not hosting ourselves) and some ASP support. Any de...
SpellCheck Will it work on the Access 2007 Runtime?
I have a program that I have compiled. It has the following. Private Sub cmdSpellCheck_Click() Me.Call_Notes.SetFocus RunCommand acCmdSpelling End Sub Is this going to work on a runtime or will I need a full version of Access to use it? You can simulate using the runtime of A2007 quite easily for testing. To do it, change the extension of the file from accdb to accdr. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Avid Fan" <me@privacy.net> wrote in message news:ulRyIhQ4KHA.4804@TK2MSFTNGP04.phx.gbl... >I have a program t...
Format Cells Date (or any change) not working on imported data
Hello, I've just spent ages researching this and not come up with what I need to be able to do. I have a worksheet for some simple data that has been imported, a date, text and number column (as they display graphically to the end user). All are a "general" format when using Format > Cells. The issue I have is that the date information is in an American date format and I would like to change them into a UK date format. Format > Cells and selecting any option (including custom and special) makes no changes to the imported data. I have seen the work arounds whereby you sp...
Hi, I am using Outlook 2000. I have set up five different email accounts and when I click on "Send/Receive" I can see the five accounts listed, but when I click on any one of them, the Inbox does not change, it stays with the Default Inbox. Even the Login window appears and I enter the login info, it still stays with the Default Inbox. At this point I cnanot access other e-mail accounts other than the Default one which appears everytime I open Outlook. Any suggestions/comments/assistances is much appreciated. Thank you. Thomas Do you have Outlook 2000 configured in Internet Mail ...
Embedded spreadsheet
I have embedded spreadsheets that produce a stoplight function for project status - Red, Yellow, Green Each spreadsheet is identified with a project name. I have an intro slide that depicts the summary of each project with the stoplight status. The following/supporting slides show the individual projects with its respective stoplight spreadsheet. What I want to do: Link the individual spreadsheets to the spreadsheets in the summary slide. Thus, when I change the status in the summary slide, the corresponding spreadsheet in the individual project slide changes as well. In oth...
Hiding and Unhiding Rows.. Unhide does not work..
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I use this formating feature quite often. But, on this one worksheet which is a rather large data list (6000 Rows) I can NOT unhide rows that I have previously hidden. It just does not work. <br><br>I have other workbooks open and I have no problem hiding and unhiding in the normal way. <br><br>The workbook is NOT protected. The cells are not locked. But the file was originally a windows file. <br><br>Is there some switch I do not know about? This is possibly a rare occurren...
Help: Budget doesn't load bills for entire year
Budget reports only show the bill from the month it was created August and forward. Since I just upgraded from Quicken, all of the bills were paid before this month. How can I make money realize that the budget should include these as well. ...
Macros
I'm getting confused. I built two macros for a given spreadsheet and inserted them on a custom toolbar. When we copy the spreadsheet, giving it a new name, and then open the copy and run the macros - excel opens a new spreadsheet with the name of the original. I'm guessing that the macros are assigned specifically to a particular spreadsheet somehow? How do you suggest I work around this? I'd like to copy the spreadsheet and have the macro work in the newly created spreadsheet too. Even if it entails some minor coding changes, thats fine - or is there a way to tell the macr...
Amex/Costco account update not working
Wondering if anyone else is having issues update and Amex/Costco account in 2006 version. It was working on 2004 version but has been broken since the upgrade. Andrew, Downloads from Amex/Costco work fine here. Explain what "issues" are you experiencing? Kevin "Andrew" <justme@nowhere.com> wrote in message news:42efdc69_1@newsfeed.slurp.net... > Wondering if anyone else is having issues update and Amex/Costco account > in 2006 version. It was working on 2004 version but has been broken since > the upgrade. > > Kevin, I had problems wi...
PLEASE HELP--ERROR MESSSAGE
I have two error messages. The reason why I am getting this is because I got a new computer and I copied my old outlook information and put it in the new computer. Everytime I open my outlook this message appears and the only thin I have to do is click ok and everything works. But how do I fix it? I did reload outlook serval times and there error message still comes even if I do not put my old information in it. Error 1: "The add-in "C:\Program Files\Microsoft Office\Office\SBCMSYNC.DLL" could not be installed or loaded. This problem may be resolved by using Detect and Repai...
Formula query #2
I am trying to identify a formula which will add and then display th number of times a number appears in a range. For example, the range looks like this .......A...........B............C 1...1110......1110.......1114 2...1110..... 1111.......1111 3...1111......1112.......1110 4...1111......1113.......1111 The numbers which appear in the applicable range e.g. 1110, could b any number between 1110 and 9999 and as such I cannot simply predefin which number to look for. I am hoping the output could like as follows: .......A........B 1....1110....4 2....1111....5 3....1112....1 4.....
if formula #2
i have this formula in a cell D3 =IF(A1="F",B1&B2) how can i make it multiple for example i want to add this IF(a2="G",B1&B3) in D3 also. thanks Either - =IF(AND(A1="F",A2="G"),B1&B3,) =IF(OR(A1="F",A2="G"),B1&B3,) Regards. Bill Ridgeway Computer Solutions "jaypee" <jaypee2cool@yahoo.com> wrote in message news:O3NhGKIhGHA.1272@TK2MSFTNGP03.phx.gbl... >i have this formula in a cell D3 =IF(A1="F",B1&B2) > how can i make it multiple for example i want to add this IF(a2="G&q...
Inserting formulas with PL/SQL
I'm not familiar with PL/SQL, but my friend is using it to place data into an Excel spreadsheet. She can place strings into the spreadsheet, but cannot figure out how to put in formulas. Specifically, she is trying to place web links into a column of cells. She is able to put in 'http://www...' as a string, but then cannot link from the cell. I suggested using Excel's hyperlink function, but again it ends up as a string, not a function. .... .... owa_util.mime_header(ccontent_type =>'application/vnd.ms-excel'); htp.p('ID;ORACLE'); htp.p('...
How to make a column of formulas all ROUND
I created a spreadsheet in which I have a column of formulas. Most of these fomulas are simply pulling a single number off another sheet. I want to make all the formulas ROUND versions of the existing formula without having to go into each cell and making the change. They are not in order to which I can just make the first fomula a ROUND fomula and copy down. So, is there a way to select a range of cells and make the existing fomulas all ROUND versions? Thanks. Would this help? Sub RoundAdd() Dim mystr As String Dim cel As Range For Each cel In Selection If...
Office 2001 for Mac help needed!
As an undergrad, I received a copy of Microsoft Office 2001 for Mac and it is installed on my iMac that I used there. However, I just bought a new MacBook for graduate school and need to install Office on that computer now, but when I put the disk in and double click on install it wont work. All of the icons have an x on them and I believe it says it "This application cannot run on this system" or something to that effect. HELP! I need Office asap since classes start this week! Thank you Laura Hi Laura - Your best bet is to hie thee to the university bookstore & get the u...
reset button in spreadsheet
Is it possible to place a button in a spreadsheet that will undo all the changes that have been made since the spreadsheet was opened? Thanks, Clem. Clem There is one. You will see it up in right-hand corner. It is an "X" and when clicked, will close the workbook. Click NO when asked to save changes. Gord Dibben MS Excel MVP On Sat, 10 Feb 2007 14:53:08 -0500, "C.M.G." <cmg@ohiorepromed.moc> wrote: > >Is it possible to place a button in a spreadsheet that will undo all the >changes that have been made since the spreadsheet was opened? > &...
Hlp! Requery Cbo Not Working
Hello, I'm using Access 2000. I added a combo box to enable users to select an existing employee name. The source of the form is a query that contains the last&", "&first to concatenate the two fields so they show correctly in the combo box. Everything is fine until I add or delete an employee. The combo box doesn't update automatically, so I added the following event in the form's After Update: Private Sub Form_AfterUpdate() Me!cboName.Requery End Sub However, now the combo and the employee records no longer are in synch. Nothing at ...
Reminder is not work in default folder #2
I used as follow coding to send email to outlook client(outlook 2000): ''''''''''''''''''''''''''''''=AD'''''''''''''''' Dim ll As New CDO.Message Dim bb As New CDO.Configuration bb.Fields(cdoSMTPServer) =3D "nc80012" bb.Fields(cdoSendUsingMethod) =3D cdoSendUsingPort bb.Fields.Update Set ll.Configuration =3D bb ll.Fields.Append "urn:schemas:mailheader:x-mess=ADage-flag", adBSTR _ ...
EXCEL FORMULA #28
Good afternoon, I'm trying to fine a formula which would show me how much money I would save on a mortgage if I were to pay additional principal each month--in addition to paying the additional principal how long would it take to pay off. I'm looking at a 160k mortgage at 7.5 for 30 years. I'll like to pay this off as soon as possible by paying additional principal each month. There are tons of free templates at: http://office.microsoft.com/en-us/default.aspx Maybe you'll find something you like. Kam1999i wrote: > > Good afternoon, > > I'm ...
short cut for copy past w formulas??
I hope I can explain this right. If Sheet 1 cell 1A were to equal Sheet 2 Cell 1A I need a quick way to copy that formula so on sheet 1 cell 9A will = sheet 2 cell 2A , sheet 1 row 17 A = Sheet 2 Cell 3A and so on .. If i do a straight copy past it equals the row on sheet 2 which is no good to me.. I only have about 300 cells to do so any short cut would help. Thanks in advance. In sheet1 cell A1 enter the formula =OFFSET(Sheet2!$A$1,TRUNC((ROW(A1)-1)/8),0) Then copy this down to every 8n+1'th row -- Return email address is not as DEEP as it appears "DR, Bob" <bob@mai...
Help on percentages greater than 100
Thanks in advance for your assistance. I have a fairly larger list of numbers that needs to be divided to get a percntage. Due to restrictions, accesses etc some of the numbers which were accurate at the time will result in a number greater that 100%. These values (%) will eventually end up on a graph and should not exceed 100%. A B C 1 88 89 =b1/a1 (101%) 2 88 88 =b2/b2 (100%) the ( ) above are the results. What I need is a way to show the numbers that are > 100% but limit th...
Word problems 2003 and 2007 version
hey everyone both on my main computer and my brothers laptop word isnt working! its so annoying! on my desktop PC i have windows xp and word 2003. after getting a virus which stopped the internet from working i had to do a system restore - which got rid of the virus. but when i click on word it says that "this shortcut only applies to installed products", and when i go to word through program files it says "this product is not installed on this user" - but there is no setup thing i can press - will i need the disc maybe? on my bro's laptop he got the sa...
Formula for Date
I'm new to formulas and just want to display the current date in my outlook form (e.g., December 18, 2004). What I've done is created a combination text field where I have the following fields: [Email Opening Date] [Full Name] [Job Title] [Company] [Business Address] Dear [Full Name]: When I send a new message, I then copy the values into my email instead of copying the data (name, title, address, salutation) one field at a time. This allows me to personalize the email. The problem is that I do not know what to do to with formulas to show the current date as I note above. Thank...
help
Hi, I'm trying to turn the warning message off when I use: Cells.Replace What:="Z Divisional Totals", Replacement:="Divisional Totals", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Second part is: Is there a way to check either the column and rows filled to create border where is pointed to "APPLYBORDER". Your help would be much apprecated. r = Range("A10").End(xlDown).Row ApplyBorder Range("A10", "A" & r) ApplyBorder Range("B10", "B" & r) ApplyBorder Range("C10", "C&qu...
Finding the last line on a spreadsheet
If hitting the Ctrl - Home buttons will find the first entry on a spreadsheet, what is the quickest way to find the last line. does anyone know of a website with these types of shortcuts. Ctrl-End will send you to the last line. in the Excel Help, select "Hotkey" and you'll get a list of all the different keyboard shortcuts. "derwood" <darren.irvine@gmail.com> wrote in message news:1131173033.742797.236360@g49g2000cwa.googlegroups.com... > If hitting the Ctrl - Home buttons will find the first entry on a > spreadsheet, what is the quickest way to find ...
Editable Excel Spreadsheet Online?
Hi, I tried to recent find information on this, but could find very little. How difficult would it be to host an excel spreadsheet online where visitors to the site can directly view and edit it? Right now, I can upload the spreadsheet to our web site and visitors can view it, but if they edit it, they can only save it to their local drive. I would like the users to be able to save the copy on the server. What would be involved in something like this? I assume for starters (if it's do-able) we'd need Windows hosting (we're not hosting ourselves) and some ASP support. Any de...
SpellCheck Will it work on the Access 2007 Runtime?
I have a program that I have compiled. It has the following. Private Sub cmdSpellCheck_Click() Me.Call_Notes.SetFocus RunCommand acCmdSpelling End Sub Is this going to work on a runtime or will I need a full version of Access to use it? You can simulate using the runtime of A2007 quite easily for testing. To do it, change the extension of the file from accdb to accdr. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Avid Fan" <me@privacy.net> wrote in message news:ulRyIhQ4KHA.4804@TK2MSFTNGP04.phx.gbl... >I have a program t...
Format Cells Date (or any change) not working on imported data
Hello, I've just spent ages researching this and not come up with what I need to be able to do. I have a worksheet for some simple data that has been imported, a date, text and number column (as they display graphically to the end user). All are a "general" format when using Format > Cells. The issue I have is that the date information is in an American date format and I would like to change them into a UK date format. Format > Cells and selecting any option (including custom and special) makes no changes to the imported data. I have seen the work arounds whereby you sp...