Set Bookmark Range results in Type Mismatch
I'm writing a VBA program which copies information from Excel into bookmarks in Word. I originally wrote it to be run from the Word document, but it makes more sense to have it in Excel so I'm migrating it over.
The original code worked, and used ActiveDocument throughout, which I've replaced with myDoc.
However, when run it gives the Type Mismatch error when I try to set the bookmark range and I can't figure out why.
myDoc is defined and works well in other places (for instance, if you add the line:
into this sub it corre...need to create a list box based on the selection of another list
I've done this in excel using =indirect in the list box. need to repeat in
acces.. did have a response about a week ago, but cannot trace the link
someone gave me. sorry to have to ask again, but it looked exatly what i
wanted - had a new computer at work and they lost all my stored data...
what's your question ?
> I've done this in excel using =indirect in the list box. need to repeat in
> acces.. did have a response about a week ago, but cannot trace the link
> someone gave me. sorry to have t...Paste special treats cells as a picture
I just got a new computer, and a newer version of excel, which is great, but
whenever I try to copy from one file to another, my paste special function
treats the copied cells as a picture. Instead of the normal paste special
popup, where it asks me how I want the data to come out, I get a different
popup that shows the source as a "Microsoft Office Excel Worksheet Object."
It asks me what format I want the picture to come out as, and gives me a list
of different file formats. If I try to just paste a link, it puts a picture
of the other file on the new one. I talked to so...How do I set up a chart to only chart values greater than 0
I have a chart in Excell that I would like to only have chart those items
with a value of greater than 0 (zero). Not sure if there is a better, easier
way than having manually manipulate the data.
Let's say the values are in B1:B20
In C1 enter =IF(B1>0,B1,NA())
Now use column C for the chart; the displayed #N/A values will be ignored by
the chart engine
Bernard V Liengme
Microsoft Excel MVP
remove caps from email
"debra49424" <firstname.lastname@example.org> wrote in message
news:A513E053-4AD9-44FE-952E-E9A60F49...Match values in more than one column
I have three columns of data and wish to highlight the matches or
change the format if there are any duplicates.
A B C
Apple Banana Cheese
Banana Orange Milk
Chocolate Milk Water
Biscuits Pear Bread
Wafers Plum Banana
So Banana would be highlighted in all, Milk in B and C. I assume I'd
use conditional formatting, but the MATCH functio...Text values to numeric values
Is there a formula to convert a text value in a cell to a numeri
jayveejay's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=504
View this thread: http://www.excelforum.com/showthread.php?threadid=39463
Good afternoon Jatveejay
Yes there is. Say your numeric value is in A1, in B1 the formula:
will show the number as a value. If you wanted to convert a number in
its cell from a text number to a value number, ie., not use a formula
in a di...Need null values for several fields in db
I have an equipment inventory db. I need to run a query that would give me
the records that contain null values in several of the fields. I have run a
successful query to return records that have a null value in one field but
don't know how to combine so that query returns records that also have null
values in other fields or any other fields.
You join the seperate queries with a UNION.
SELECT * From YourTable WHERE YourField Is NULL
SELECT * From YourTable WHERE YourField2 Is NULL
SELECT * From YourTable WHERE YourField3 Is NUL...Adding summaries, rows and pre-population
I have a worksheet where the user can click a button marked 'Create
Activity'. This spawns an Activity worksheet with the naming convention
'Activity (n)'. The user can click this as many times as they like, but must
click it at least once.
For each Activity sheet that they have created I would like to be able to
copy a specific range e.g. A15:J28 on a 'Summary' worksheet and paste it at
A30 on the 'Summary' worksheet and so on. So the 'Summary' worksheet just
gets longer and longer if the user has created more activities.
Problem # ...Copy and paste formula; Value pastes instead
I've tried starting several worksheets, checking formats,
pasting special (formula only). For some reason the
formula actually copies OK by looking at the formula bar,
but the value in the cell is identical to the value of
the copied cell.Incorrect. Tried even the simplest
formulas with no joy.
Is there a way to get a cell's format determined by the format of
a different cell?!
e.g. Could one get an entire row market up in say bold red if
one cell in that row was say less than 100
select the rows that you want to apply the conditional formatting to,
ensuring that the first row of this group is the first row at the top of the
screen (it gets confused sometimes if it isn't)
the following example assums you've selected from row 2 onwards and you want
the row to turn red if ...Sharing a sub-folder of another's inbox
I can access another inbox with no trouble. I need to access a sub-folder of this inbox - I have permission for this but cannot find how to access. Is it possible?
The owner of the mailbox needs to assign the appropriate permissions at the
folder level, and grant you at least "reviewer" permissions at the root
mailbox folder level.
Then you'll need to add their mailbox to your Exchange server service
properties so it will open in your folder list - it's in the advanced tab of
the Exchange server properties.
> I can access another inbox with no trouble. ...Clipboard pasting
I copy something to the clipboard using Publisher 2007 and the computer tells
me it is in the clipboard; however, when I go to paste that information into
another document, Publisher acts like it is not there. I then need to close
the document I wish to paste it into, reopen it, and then, like magic, there
is the information in the clipboard and I am able to paste it. UGH!!!!!!
Open the Office Clipboard in Publisher. Edit, Office Clipboard. What program
are you pasting to? If it is an Office program, open the Office Clipboard in
that program too.
...SUMIF/AVERAGEIF with mulitle range and sum ranges
Im getting an #VALUE! when putting in this formula
I know I'm using it wrong, please help!
You can't use muliple range references like that with AVERAGEIF.
Kind of long (but not as long as it could get!):
Microsoft Excel MVP
&qu...formula to get sheet name
can a formula retreive a worksheet name or does it need to be macro based?
This formula that takes care of the possible situation whereby you have only
one sheet in the workbook and its name is the same as the workbook's. Note:
CELL("filename") will only work if the file has been saved at least once and
if the file is opened in a different language system, the argument
"filename" will need to be changed manually to the corresponding word (e.g.
in Spanish "nombrearchivo"):
=LOOKUP(REPT("z",255),SUBSTITUTE(MID(CE...Giving a name to a row or column
I have a spreadsheet that has a bunch of rows of different length.
I am doing a match to find a column I want - which is between my
starting column and an ending column that is big enough to fit my
But periodically I insert a new column in the front, making my longest
I can solve this problem by making my end row huge. Maybe make it
zz (what's the largest column possible?). But this offends my
sensibilities a bit. What would be better is to somehow assign a
variable name for my column that would be moved right when I add a new
I s...Row height #7
Is there any way you can set the row heights, apart from doing i
What I am looking for is say the row height of row 1 to be set to th
value in cell A1, row 2 height set to the value in B1 etc.
Thanks very much in advance
Message posted from http://www.ExcelForum.com
One way would be to right click sheet tab>view code>copy/paste this>modify
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then Rows(1).RowHeight = Target
If Target.Address = "$B$1" Then Rows(2).RowHeight = Target
D...Setting the Focus to a Control if it is empty
I have a Form where if the user exits a Control without selecting an
Employee Name, I would like to display a error box and then set the Focus
back to the same Control. I have attched the Code I am using and get the
Error Box but instead of setting the Focus to the same Control, moves on to
the next Control in the TAb Order. Please help.....
Private Sub EmployeeName_Exit(Cancel As Integer)
Dim varEmployee As Variant
If Not IsNull(Me.[EmployeeName]) Then
varEmployee = DLookup("[EmployeeID]", "[EmployeeList]",
"[EmployeeName] = '&q...Look up multi values
I’m struggling figuring out how you would do the following?
Work sheet data.
A1 =25 B1 = 40
1 25 30
2 25 40
3 27 41
4 28 42
What I want to do is lookup the values 25 & 40 (A1 & B1) from table C1 D4
Matching the value 25 in Column C & 40 in Column D & return the location in
the table to Cell A2? If there is no matching value, were decimals come into
play, i.e. 25.8, then return the next largest from column C with its parent
value from D, Returned value = 2 or 3 if 25.5.
Sounds simple but I get lost very quick!
Any Help greatly appreciated.
one way. This is an array...Checking a Yes/No field for its value
I'm trying to set a value in a custom number field based on alternatives in
two other fields, and this is what I have tried so far:
IIf([Text18]="Business Activity",[Number10],[Number1]), where summary tasks
may or may not meet the test condition.
This works for tasks and milestones, apart from summary tasks where the
relevant value is not copied in - it's set to 0.
So how do I add in an additional test for summary tasks to set the value
Here's what I have tried so far ...
IIf( [Summary], truepart, falsepart )
IIf( [Summary]=True, truepart, fa...how to automatically insert row when i hit enter
I've got a list in a1 to a10, at the bottom of the list i have a sum of a1
to a10. when hitting enter in a10, how could i automatically insert a row
so i can enter more data?
right click sheet tab>view code>insert this>SAVE
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$10" Then Target.Rows.Insert
"Dano" <email@example.com> wrote in message
> I've got a list in a1 to a10, at the bottom of th...Delete row in table by code
I have a table named Uploaded Documents that is created by a code that was
developed by someone that used to work here. I have no coding experience so
need some help. I need to add something to the end of his code that would
delete any row where in the field SVBrand it contains either ## or @@
anywhere in that field. Any help would be apprectiated.
Put this in the code and it will delete those rows:
strSQL = "DELETE * FROM [Uploaded Documents] WHERE svBrand = '##' OR
Dave Hargis, Microsoft A..."Paste Special" with the right click mouse
I would like to have the option of "Paste Special" with the right click mouse
in all microsoft office suite.
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 in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/office/community/en-us/default.mspx?mid=de847738-df56-4...Numeric values in data area of the pivot tables
How can I bring a numeric data into the data area of a
pivot table? I need to sort the numeric values in the
numerical order. Any idea?
...Parameters And Null Values
I have a query that I wish to return ALL values between 2 dates AND any
records that the date happens to be null
I have used the following:
WHERE (((Schedule.DateSubmitted) Between [Forms]![ViewReports]![DateFrom] And
[Forms]![ViewReports]![DateTo] Or (Schedule.DateSubmitted) Is Null))
And the result is ONLY the Null values.
WHERE (((Schedule.DateSubmitted) Between [Forms]![ViewReports]![DateFrom] And
[Forms]![ViewReports]![DateTo] And (Schedule.DateSubmitted) Is Null))
And the result is Nothing...an empty set.
What do I need to do so that it will show me all records betwe...Pasting formulas advances cell reference
When i paste special from one cell into another, it advances the cell
reference and i just want an exact match i.e.
A1 formula ='Feb 2010'!C18 but when i paste it into another cell it pastes
The C changes to D, how do i stop this happening?
Thanks for any help in advance
Use absolute referencing. Below are the different reference styles.
A1 Relative referencing. Both column and row will change if you copy or drag
$A1 The column reference is fixed and will not change
A$1 The row reference is fixed and wil...