From VBA, how do I test for a cell with value #N/A
For example, Cells(1,8) contains #N/A. The following VBA statement:
If Worksheets("Sheet1").Cells(1, 8).Value = "" Then
gives a type mismatch error.
Thanks for any suggestions.
You could use the .text property:
If Worksheets("Sheet1").Cells(1, 8).Text = "" Then
Or you could use:
if iserror(worksheets("sheet1").cells(1,8).value) then
'do something with the error
if worksheets("sheet1").cells(1,8).Value = ""
You might even be able to use:
if isempty(worksheets("sheet1&...Email messages are being deleted
Not entirely sure if this is the right group but anyway here goes.
Somehow I've altered something which has made my Vista Windows Mail
6.0.6000.16386 delete each mail message once I've read it. The deleted
messages don't even go into the deleted items folder.
Everything was working fine until I started messing with the settings to try
and delete old usenet postings.
I've tried to back out the changes which I remember making, but so far no
Thanks in advance
They're not deleted. While in the Inbox: View | Current View...If formula #3
In a formula how do i say, larger than 56 but smaller than 420... o
larger than 56 AND smaller than 420
Message posted from http://www.ExcelForum.com
> In a formula how do i say, larger than 56 but smaller than 420... or
> larger than 56 AND smaller than 420?
> Message posted from http://www.ExcelForum.com/
...Users can't empty deleted folder from webmail
We're using E2K. A handful of people only get to their email thru webaccess.
There are a few of these individuals that cannot delete their deleted items
folder while in webmail. What they have to do is click on the deleted items
folder, then click on each individual email and delete them that way. But if
they try by clicking on the empty deleted items folder icon, they get the
following msg, " An error occurred while trying to empty the deleted Item
folder". Please advise.
...Returning to previous record on Scrolling form
I have a very long form with sometimes hundreds of records. For each record,
there is a text box where the user can enter a quantity to be added/deleted
to the existing quantity. Once the quantity has been added/deleted, the form
requeries so that the quantity displayed is now accurate.
Since the form is so long, I want a way for the form to return to the record
that the user just updated. In Access 2003, I used this code:
dim rst as recordset
dim IntKeyVal as string
IntKeyVal = idx_IDNumber
<<<other VBA code here to add/delete from existing quantity&g...Delete Pivot Table Formula
How do i delete calculated fields from pivot table field lists?
Please check your previous post of this question...
> How do i delete calculated fields from pivot table field lists?
...Deleted e-mails and size of PRIV.EDB
We have asked many users to delete old e-mails and we have
move numerous users over to our old Exchange 5.5 server to
our new Exchange 2003 server. When we look at the size of
the PRIV.EDB the file it has not decreased. The server
has been rebooted and we still do not see any change. I
know that the size of the file should decrease by at least
500MB - 750MB. Does any know how to get the .EDB to
release the space? Thanks in advance!!!
you must perform an offline defrag of the database to reclaim the space on
http://support.microsoft.com/default.aspx?scid=kb;en...Add buttons for Center Across and Paste Formula
It would be very helpful if the Customize Toolbars funtion gave access to
buttons for Center Across and Paste Formulas so you don't have to a) traverse
through menus or drop-downs, or b) create macros and custom buttons to
accomplish these oft-used features.
I get tired of always having to create macros on each system I work on in
order to set up custom buttons that accomplish these simple tasks.
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button i...conditional formula needed
it might be possible to do this as a conditional formula (=if) otherwise a
macro might be needed.
the thing i'm unsure about is this formula looks at the cells beneath it and
keeps looking until a result is returned, i'm not sure if that can be done as
a conditional formula.
if you really want to know what this is for send me a pm and i'll tell you.
if m3 = 0 then ""
if l3 = i then ""
if m3 = 1 and if l3 = "c" and if i3 > 0
and if e4 – d3 < -.0066 then result = 1
write result in n3
else go to row 5
if e5 – d3 < -.006...Cells will not calculate
I am using the replace feature to modify a rather long formula. I have to
modify it in each column, abour 30 cells per column.
Anyway I am replacing $D with $E = the replace feature works perfectly but
after the change the cell does not calculate and return the data... HELP. It
is far to tedious to make the chabge manually.
Even with calculation set to manual, the cell should recalculate after
making a find and replace change. Your form...Why do cells in Excel print shorter than displayed on-screen?
When I type a lot of data in a given cell (textwrapping is on), the entire
contents of the cell display correctly. But when I print the worksheet, the
cell height appears shorter -- truncating my text.
What's up with that? And how do I fix it without adjusting every cell
manually (which screws up my display)?
...Why does office turn off when I delete a message?
I have Office Outlook 2003 SP1 and all critical updates. When I do anything
with a message, other than read it (for instance, delete it), Outlook shuts
down and the message appears telling me that the program has run into a
problem and needs to close. There is no error number and no recourse but to
click to either send an error report or don't to MS. Has anyone had this
problem and has anyone found a cure for it. Thanks for whatever help you can
Try both detect and repair and scanpst.exe.
Milly Staples [MVP - Outlook]
Post all replies to the group to keep the disc...same numer/character at beginning of cells in row
I have to make a worksheet of products. I can copy the part numbers but need
to have all the part numbers preceded by "2M" no quotation marks. example:
starting number looks like this "el000c" need to have it look like this
The information is being copied from several other sheets via cut and paste.
Is it possible to paste the "el000c" number with the 3B already there and
staying after pasting several cells at once?
Thanks in advance regardless of the answer.
Do you want to precede with "2M" or "3B"? Or is i...Returning the windowheight of a subreport
I need to adjust the height of a text box that is adjacent to a subreport in
the detail section of the main report for each instance as the report is
generated. What I've tried doing is below:
Dim height as integer
height = reports.item("subreport").windowheight
me.textbox.height = height
With that I got a can't find report error.
I was then told to change the code to this:
Dim height as integer
height = Reports("NameOfReport").Item("Subreport").windowheight
me.textbox.height = height
However with that I received an application defined or object de...Round formula
How can I round 980 to 1,000?
With 980 in A1, you can use this:
Hope this helps.
On Apr 1, 3:07=A0pm, Khaled <khaled...@gmail.com> wrote:
> How can I round 980 to 1,000?
It did help, thanks Pete.
On Apr 1, 4:23=A0pm, Pete_UK <pashu...@auditel.net> wrote:
> With 980 in A1, you can use this:
> Hope this helps.
> On Apr 1, 3:07=A0pm, Khaled <khaled...@gmail.com> wrote:
> > How can I round 980 to 1,000?- Hide quoted text -
> -...formulas for Actual Duration and BCWP
I have yet to be able to figure out the formulas that derive both Actual
Duration and BCWP when using actual data in my plans. I understand from
where the data is to come, but I have not found a consistent set of formulas
I can use to calculate Actual Duration, which is essential to calculating %
Complete...nor can I figure out how MSP (standalone 2007) calculates BCWP. I
have asked the question on this forum in the past as to how MSP calculates
the EV elements but have not gotten an answer I can use to run calculations
myself and have them = Actual Duration or BCWP I see in th...Delete function frozen-Entourage
Operating System: Mac OS X 10.6 (Snow Leopard)
Email Client: pop
My wife deleted a block of e-mails and before it was completed she clicked on a message in the block that she wanted to save. This cause the tool bar containing the Delete button to turn gray and freeze. Now large blocks of her mail in her Inbox and Delete files show as blank space (white screen) when these folders are opened but either clicking on this space or scrolling through it restores the e-mails listed there. At times clicking on a block of e-mails (Wednesday for example) causes the entire block to turn...Deleting specific records from a column
I have a little problem. I have two sets of records - let's say for the
purpose of this exercise they are titled "a" and "b". The record sets
differ but do have many matching records. My objective is to extract
all the "b" records that match up with an equivalent record from the
"a" column. The final record set will have these "b" records and their
matching "a" records deleted from it. Example
Column a Column b Final column
earth earth sky
sky grain fire
fire ...Can't Delete Budget Items
Money 2003 Delux and Business: Earlier in the year I had two EPay
transactions set up to move money to outher accounts. One was a weekly item
and the other was a monthly item. I stopped both of these series quite a
while back by going to the Bills and Deposits list, right clicking on them,
selecting delete and deleting all unrecorded occurancnes of the scheduled
transactions. This week I was working on my budget for next year. At the
bottom is the "Transfer out of Budget Accounts" area. Both of these are
still listed as transfers. When I right click on them for the contect men...Deleting Contacts and Accounts
We had the capability to delete Contacts and Accounts.
Since last week we have only been able to de-activate them.
The CRM settings are the same for each of the roles. (They have deletion
...deleting hotmail from outlook 2002
I currently have an email address that is ....@msn.com
and it was setup thru hotmail. I want to change
everything to Outlook 2002 and not use hotmail.
Currently, outlook is my default mail client and somehow,
i have a hotmail folder in outlook. This is where all of
my incoming mail goes (inbox folder under hotmail).
Also, I have tried to export address book from hotmail
and import to outlook but have been unsuccessful. I
would appreciate any help!!
If your only mail account is Hotmail, you will need to continue to use
Hotmail? What replacement mail service do you have in mind?
...Novice at formulas needs help
Please remember...I'm a novice at EXCEL...self-taught...I want to write
a formula that will convert what is in one column to another column
based on info in yet another column....
If D=1 and K=B then L=NH...another example...
If D=7 through 12(as in grade in school) and K=D then L=IH
Message posted from http://www.ExcelForum.com/
use the IF formula. E.g. if your target cell is C1 enter the follwoing
this will enter the value of B1 in C1 if A1 is "D"
have a look at the Excel helpfile for the IF formula
=IF(AND(D1...Selecting Font Colors
Greetings group, my 1st post here.
I've been trying to figure out how to use/create additional text font colors
beyond the 40 palate or 16 additional in the Format Cells dialog.
Is this possible? Is there a show codes area to tweak? or better still a
color wheel for the Text Font Color available.
I'm using: Microsoft(R) Office Excel 2003 (11.8012.6568) SP2
- thanks in advance M.D.
About all you can do is modify the existing colors under
This is not a global change, just a workbook by workbook basis.
You can import your modified scheme...Formula for calculating # of days between dates minus a particular date if present
I'm trying to create a formula that will calculate the number of days
between two dates automatically subtracting a day(s) from the equation
if a specified date(s) falls in between the two dates.
For example, I don't want 1/15/04 to register as a counted day. If I
enter the dates 1/1/04 in one cell and 1/12/04 in another cell then
it's a straight foward DATEDIF equation. But if I enter 1/1/04 and
1/28/04, then I want the formula to figure out the number of days
between 1/1 and 1/28 automatically subtracting 1/15 from the equation.
This gets trickier as I need some calucations th...range specified by the value in another cell
I have the following problem:
I have let say 100 numbers in column A, and a number
n<100 in cell B1. I want to have the sum of the first
n numbers in column A, so I would need to specifiy the range
A1:An, but I need this independently of the actual value of n.
I have tried A1:A(B1) with but it doesn't work.
So has somebody any idea
Message posted from http://www.ExcelForum.com
2 ways for it
1. Use the formula
2. Create a named range
The formula will be