Can I use "find / replace" to automatically "Insert Rows" in a do.
Have a long column of numbers
Every time I see the number 5, I need to insert two rows. Doing this by
hand is tedious. Can you help me find a shortcut?
The IF function does not do this, right?
Not 25, 35, 5351, just plain old 5?
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
FirstRow = 1
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For iRow = LastRow To FirstRow Step -1
If .Cells(iRow, "C").Value = 5 Then
...Pivot Table not pulling all fields or data
Without getting too verbose, I have a 900 row by 26 column database I am
applying a Pivot Table to. The code seems to run fine, but it is not
returning some of the fields, and of course the related data. I have tried
reformatting the entire columns I am pulling from but no joy......however,
when I reformat an errant cell from General to TEXT, that row comes
in........and/or, if I change the value in one of the missing cells, it will
bring that row in. The code runs fine, and was mostly derived by recording
a macro in XL2k, but the data cannot be trusted becau...Displaying a stored image on a form
I created an OLE Object type field in a table and then I inserted an graphic
I then created a form on which I want to display my graphic file. I tried
to display the graphic in a bound object control but the contol only
displays the file name or an icon on the form. How can I get the actual
graphic to display?
I can display an image on a form but this is only a link to a graphic file
and if I move or delete the original graphic file the database connection is
lost. I want the image to be stored in the database so there is no linkage
to outside files.
Has anyone done th...Control of exit from a Form opened within a subroutine
A DoCmd.OpenForm statement occurs part way down my Access 2000
subroutine "Prcs". The opened form presents the user with some screen
info from which he/she must decide which of the two command buttons
"Continue" and "Quit" should be clicked. "Quit" sends execution
control elsewhere while "Continue" is designed to continue processing
tested vb code ' Includes evaluation of "strCrit"
DoCmd.OpenForm "frmShowinfo", , , strCrit
more tested vb code
I've not found a way to stop contr...Running Balance in excel pivot table
Product, transaction no
have data fields:
qty rec, qty iss, balance (calculated field: qty rec - qty iss)
want balance to be a running total with base field of product
so want something like:
inv-----------------0--------------------...Popup Form question
How can I set a pop-up form to only take one record?
I have a popup form (single form) to take some customer info however when if
they tab past the last field all the data disappears.
Anyway to prevent this?
Thanks in advance
"Dave" <firstname.lastname@example.org> wrote in
> How can I set a pop-up form to only take one record?
> I have a popup form (single form) to take some customer info
> however when if they tab past the last field all the data
> disappears. Anyway to prevent this?
> Thanks in ...How to use the countif() function to count items in a table
I have a table contains multple columns of info as shown
in the below
AcctID ProjectName PMName Phase Status
EIG Project 1 Wellie Initiation New
MLI Project 2 John Develop Active
EIG Project 11 Wellie Qualify Active
EIG Project UUU John Design InActive
MLI Project XX Carol Deployment Completed
MLI Project YYY Carol Design Active
I'd like to use the countif() function to complete the
following metric such that I know how many projects for
each status for each AcctID.
i have created a table in excel but want the edges of table to be slightly
curved instead of being a right angle....is there a way of doing it?
Not sure what you meant by "table." How did you draw/create the table?
Have you checked out AutoShapes from the drawing toolbar? =
Autoshapes>Basic shapes>first one second row is a rounded rectangle. =
Draw the rectangle then right-click ...... Format AutoShape>Colors and =
Lines tab>Fill - color - no fill or transparency 99%. Grid lines =
Is this what you want?
"Sam" <Sam@discuss...Pivot Table Text Only, No calculations
:confused: I have a spreadsheet of data, text only. An address list.
want to look at the table by city. Is there a way to do this withou
calculation? For example, I want to have a city has the page field an
look at all the addresses, including columns, within that city.
rmm30's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=835
View this thread: http://www.excelforum.com/showthread.php?threadid=26902
A pivot table is a good tool for summarizing data, but won't help...Data vs. forms & Unload/reload questions
A .mdb file seems to encompass both the database and all the 'code' (forms,
modules, and logic) used to CRUD the data in the tables.
1) Is there any way to separate the database from the code into separate
files, eg. the db is mydata.mdb and the 'code is in a separate file, eg.
2) Say you have v1.0 of an app and the tables and attributes are populated
with data. You then go and build v2.0 of the app, adding tables and columns
to what already existed in v1.0, and perhaps moving some of the v1.0
attributes into different tables. What is the recommended way to
unload/reloa...Return a value when Date x is between Date y and z
I have a table something like this:
A B C
1 From To Result
2 01/07/2009 01/07/2010 0-1
3 01/07/2010 01/07/2011 1-2
4 01/07/2011 01/07/2012 2-3
5 01/07/2012 01/07/2013 3-4
In another column (X) I have a list of Dates and I want return the
"result" from the row where the date lies between. So for example in
X3 I have 16/10/2011 and I can see this date is between the 2 dates in
row 4 in the table above so I want to return "2-3"
I've tried an array formula ...Form Locked !
Hi, I created a form using the wizard based on a query, everything looks ok
but i cannot enter data into the form as it appears to be locked ? what have
i done wrong, i been trying to solve this for 2 hours now - please can you
One common reason is the query used for the form.
Open the query and see if you can add a new record to the query.
If you can't, have a look here:
"blake7" <email@example.com> wrote in message
...URGENT: Exporting table to an SQL script
I have a database in Access 2007 which I'm currently trying to rebuild in
SQL Server Express in Visual Studio.
Problem is, I can't seem to find an easy way of exporting tables. I've been
using copy/paste on the data, but that only works with simple tables where I
can build the table in SQLEx first.
Is there a way in Access 2007 (or an Add-in I can download) to export a
table as a list of SQL commands? Yknow.. CREATE TABLE, INSERT, etc!?
you can use upsizing wizard to export whole database, or Export tables to
ODBC connection to your sql server...Default form in Public Calendar folder
I have a calendar in the public folders. Is there any way so that when the
user clicks on the "New" button in Outlook that the default form that opens
is a "Meeting Request" as opposed to an appointment?
Sylvia <firstname.lastname@example.org> wrote:
> I have a calendar in the public folders. Is there any way so that
> when the user clicks on the "New" button in Outlook that the default
> form that opens is a "Meeting Request" as opposed to an appointment?
I don't think so, but you can certainly click the down-arrow next to the New ...Unwanted Code Being Added to Form
This is a multi-part message in MIME format.
Access 2007 sp2.
I thought I seen it all.
For some reason code was added to the OnClose of one of my forms that I =
DID NOT put there!
The code is a 1 liner using the NavigateTo command.(irrelevant I =
I recall testing using custom Navigation Panes about a year ago but =
abandoned it. I recall having
code in the OnClose of my form(s) that switched to a different Custom =
Nav Pan...forms not listed in database contents, yet can add data to them
Hi - there are no forms listed when I open up the database in design view.
All the tables, queries, and macros are there.
When I open up the database to add data, all the forms are there and can be
used. The data is stored. So, it seems that the forms are still there, but
just cannot be seen in design view.
The database, when I open it up in design view, says Access 2000 file object
even though I am using Access 2003 - could that be the problem?
You cannot open an entire database in Design View. Try opening it normally,
selecting a form, and choosing Design View from the V...Multiple combo boxes on a form
I have a form based on two tables. I would like a combo box for a field from
each table that will fill in various fields from the relevant table. Does
anyone have any suggestions?
On Fri, 17 Aug 2007 23:58:37 GMT, "Malt" <u36720@uwe> wrote:
>I have a form based on two tables. I would like a combo box for a field from
>each table that will fill in various fields from the relevant table. Does
>anyone have any suggestions?
Why would you want to do that?
It sounds like you're trying to store data from one table redundantly into a
second table. This is essentially ...inserting text form field in a text box
I'm designing a form that has several text boxes I want to be able to insert
a text form field that I can type in different names in when needed, while
keeping the rest of the text constant. Every time I go to insert form field
from Legacy it is not available. Anybody have a solution.
Your subject line has the missing clue. There is no way to insert a
legacy form field (text or any other kind) in a text box, footnote,
endnote, or header/footer.
You can remove the text box and put the field into ordinary text, or
you can use a text content control in a text box or elsewhe...SeriesCollection Values Property Oddity
This has long perplexed me. Thought I would ask someone.
The Values property of a series apparently returns an array of all the point
values in the series. Howver, one apparently can't extract an element from
this array. The following macros demo the issue. Looking for enlightenment:
Dim i As Long
For i = 1 To .Points.Count
MsgBox Application.Index(.Values, i)
'This also works
Dim arr As Variant
Dim i As Long
With ActiveSheet.ChartObjects(1).Char...Multiple table lookup
How do I set the LOOKUP formulas for this situation:
* Column A displays the names of salesmen (10)
* Cells B1:F1 display the names of Products (5)
* In cells B2:F11 are displayed the % of budget achieved by each salesman
for each of the 5 products
In a separate table I set up a commission matrix showing the % commission
due for each product according to the % of budget achieved; the names of the
products run across the top, the % achieved vs. budget in column A, and the %
commission payable in the intersecting cells
Contains the lookup formulas that calcul...Pivot table bottom ten filter with conditions
is it possible to have the bottom ten item of a pivot table but
excluding specific value(s)
eg I have a pivot table with the bottom 10 of cost
item 1 -200 $
item 2 -300$
item 3 -0$
I would like to exclude item that have a cost = 0$ and have only
items greater than 0.
Excel 2007 PivotTable
Bottom 10, no zeros.
thank you for the spreadsheet.
...How to set form caption / text?
I use MFC Visual C++ 4.2.
I am developing an application with main window and a few childs window.
How to set each child window text / caption everytime I call DoModal? If I
set "Entry Form"
then the window I call will displayed with "Entry Form" caption.
You can use SetWindowText to set it. For a dialog, you should do it any time
after its created(IOW, not in constructor). OnInitDialog and after is fine.
"Landon" <Landon@discussions.microsoft.com> wrote in message
>I ...Not Print Zero values
I need to do one of two things:
1) My zero values are hidden but they span down 300 cloumns. Is there
away to hide these from printing?
2) The zero values above are a simple currency total between tw
fields. I have filled the incrementing formulas down about 300 rows. I
there a way to fill in the next incrementing formula [(b2)*(d2)] whe
the calulation is made? I want to keep from having 300 growin
formulas, I'd rather try to have them incrimentaly auto fill so m
print outs wont have 20 extra blank pages.
----------------------------------------------...Fixed Assets ACRS tables
Is there a way to update/change ACRS tables? Can new ones be added?
Our client here in sydney is reporting to their US headquarters, and they
are using depreciation rates table from Gap 20. The rates are not on the
ACRS tables of GP.
...Update multiselect field in table with code
Access 2007 under Vista
Using a form to select variables for a report. One of the fields is a
multi-value field in the underlying table. Have determined that following the
selection of required items from the multi-select combo box and the clicking
of the OK button, the underlying table is not immediately updated. The table
is however properly update once the form is closed. This does not meet my
purposes for the report generation, as exiting the form and returning is not
a reasonable approach.
I would like to use the After Update event from the multi-select combo box