Loop to retrieve value in worsheet A and put into worksheet B
I am hoping someone out there cyber-world can help me with some code that I
need. I have a workbook from an outside source. I need to loop through
Worksheet named "Details", column A to find the characters "C:" and return
the value from Worksheet "Details" , column B into Worksheet "New Details",
Column A. There are blank rows in the data and the only designation between
what would be considered a new record is a series of dashes (14 dashes to be
exact). Is Anyone able to help with this? Anything is greatly appreciated.
Is there an...query error
Hi, I am running a query based on 3 queries
q1 has this
PurchDoc Item Rel Vendor Vendor name Order Type PO Date
Material Short Text MRPCn Del Date Deliv Date PO Quantity GR
Quantity QtyRed RemQty PD release Reschdate CC Created on Exc Net
Order Value ID
46353228 190 F 10000019 RA LALLI NB 11/6/2006
109-0718-13-101 PASSACINGHIA AK7 6/13/2013
44 42 0 0 1/2/2010 11,396.00
q2 has this
PurchDoc Item Rel Vendor Vendor name Order Type PO Date
Material Short Text MRPCn Del Date Deliv Date PO Quanti...Create Modeless Dialog Boxes Query?
I have an SDI explorer type application with a tree view and a list view.
In the listview i show the thumbnails of all the bmp files in a specific
Now when the user double clicks on any thumbnail i want to dynamically
create a modeless dialog box.
Now is it possible to specify the width & height of the modeless dialog box
at the time of creation, say i want the dialog box to be of the size
If this is possible how can this b dun?
Waiting for comments
"Snav" <email@example.com> skrev i meddelandet
...querying excel spreadsheets like database
I'm making a payroll journal, it has a column for the names, and the
the cheque amt, cheque no., and some other stuff.
In a separate sheet I need to pull up individual information pe
employee and total.
Name: cheque amt: cheque no: date:
jane doe 2000.00 0001 01/01/01
john doe 1500.00 0002 01/01/01
jane doe 1800.00 0003 15/01/01
Name: cheque amt: cheque no: date:
jane doe 2000.00 0001 0...Run Macro If Cell value = "Other"
Alright, I have a very simple macro at the moment that unhides a column
and adjusts the print area to suit.
What I need is a maco that will run this macro if the cell F4 = "Other"
from a drop down menu.
Seems painfully simple I just can't find where it is.
If Range("F4").Value = "Other" Then
(replace xxxx in the email address with gmail if mailing direct)
<firstname.lastname@example.org> wrote in message
> Alright, I have a ...OTHER options for chart not plotting null ("") or zero values
I've read the above suggestions for a chart not plotting zeros or null
values. This worked....however, then I got errors because of other
calculations (averages) that use these cells as references (and #NA just
doesn't go over well when it's being used in another formula).
Because of the many other formulas (in visual basic and in the exel file)
that would need to be changed if i created an extra column, or did some of
the other options I would REALLY prefer to not do any of those. Isn't there
some way within the chart's options to exlude these values?
Or is my best ...Advanced Find 'Set' queries
Is it possible to use advanced find to perform any type of set query? Example:
have setup a m-m relationship to a contact type lookup table (tried using v4
m-m feature and older technique (xref table and m-1 relationships to contact
and lookup table). The issue of course is that you can't ask a question such
as 'give me all contacts that are contact type A AND contact type B since you
need to somehow define the set of records to look at instead of evaluating
them one by one. One approach that seems to work is to use the marketing
list functionality to add all contacts with co...Query results where gaps exist Min & Max do not work
I was using a min and max on the specimen id here to show the first
and last specimen worked on, however; that doesn't work when a gap in
the specimen id exists.
Here is an example of the data:
Year SampleID SpecimenID
2009 200999137 001
2009 200999137 002
2009 200999137 003
2009 200999137 004
2009 200999137 005
2009 200999137 006
2009 200999137 007
2009 200999137 008
2009 200999137 009
2009 200999137 010
2009 200999137 011
2009 200999137 012
2009 200999137 013
2009 200999137 014
2009 200999137 015
2009 200999137 016
2009 200999137 017
2009 200999137...How do i get the product of max. price in access2007 query?
in query i want to get product ID with the Max. or Min. price
The SQL statement for a query to do that would be
WHERE Price = DMax("Price","SomeTable")
SELECT TOP 1 ProductID
ORDER BY Price DESC
IF you need something more specific post more details on what you are
attempting to do.
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
> in query i want to get product ID with the Max. or Min. price
...Linking parameter query from Access to pivot table in Excel
I am trying to link an Access query that prompts for several criteria to an
Excel pivot table. When I refresh the pivot table, I want it to open the
database and run the query to update the data in the pivot table
automatically. When I try to link to the data source, when I get to the end
of the "Query Wizard", it gives me the error of "Not enough parameters.
Expected 4". But it never prompts me for the parameters. Please help. Is
there a setting I need to change or do I link the query differently to avoid
"Microsoft Query" tool?
You can't cr...Find cell which returned maximum value in formula
De, try this, =CELL("address",INDEX(A:A,MATCH(MAX(A:A),A:A,0)))
will show the first one if there are more than one
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
"De" <De@discussions.microsoft.com> wrote in message
Thanks, Paul, but I found this hard to follow. Excel kept returning that I
had disabled macros but yo...Setting a cell to not go below an X value, or above a Y value
This may be very easy to do, but I just don't have a clue, I've look a
most excel variables and they just seem to choose MIN and MAX fro
columns or tables.
What I want to do is I've got the sum adding up say cells A2 and A3 i
I want cell A4 to never go below 80, or above 200. (Formula in cell A
would be =SUM(A2:A3))
Anyone think they can help
Snakey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2785
View this thread: http://www.excelforum.com/showthre...Getting value from a picklist
I'm trying to use the value in a select picklist (based on the
Contacts table) in some SQL to get further data from the CRM database.
I'm looking at something along the lines of (AIR CODE!):
var oDataValue = crmForm.all.new_enduserid.DataValue;
var oSQL = "SELECT * FROM whateverTable WHERE TableID = " +
However, it doesn't work - I'm unable to get the value of the selected
item in the picklist.
This line of code:
while attempting to get SelectedText returns "undefined".
What am I doing wrong?
...access compare values and select higher of two
In Access database I want to compare the values in two fields in a form and
then select the higher value, insert it into another field and then use in a
E.g. Value 1 = 500 Value 2 = 600, 600 to be inserted into another field
and then be multiplied.
On 11 apr, 21:26, Captain Turtle <Captain
> In Access database I want to compare the values in two fields in a form a=
> then select the higher value, insert it into another field and then use i=
> E.g. Value 1 =3D 500 =A0 Value 2 =3D 60...Microsoft Query #3
I am trying to create a query in Microsoft Query that gets
data from an Access Database. I click on New Query, which
brings up a box for me to Choose Data Source. I select MS
Access Database, then I select the the actual database.
Next, a box titled Add Tables comes up, but there is
nothing to select. I have done this successfully in the
past, but I cannot figure out what is wrong.
...Specific cell values
i need to acheive the following but i'm not sure how to do it. i have a cell
(K49), the cell needs to collect specific data from a range ( D7:D32 ) on the
same worksheet. it needs to look in the range and if the cell value is CNC,
then add the figure form the same row in column E. Example:
D E F
glass £200 26 August 05
metal £650 26 August 05
wood £300 26 August 05
CNC £1000 ...UNION query changes results after close and reopen
I have a UNION query, and the first field is the Client id field. If I run
the queries separately they return the correct client id. If I delete the
UNION, run the query, then add the UNION back in, the correct client is
retrieved. If I then save and close the query, then rerun it, all the client
ids return as 1. This occurs even if I move the field to a different
position or rename the field.
If you like to paste the sql for the union query to this newsgroup, we could
make some suggestions.
"Jason Christian" <Jason Christian@discussio...Default Value for new Table Row
Is there a way to specify a "default" value for a field when a new row is
created in a table?
I have a simple table with the date as the first column. When I add a new
entry to the table I would like the date to default to the current day. I
tried browsing the Excel objects but could not find any objects or events
related to tables. I could develop a form, but that would be a lot of work
just to set a simple default. It seems seems like there should be an
"on_new_table_row" or "on_table_row_add" event where you could set a default
with just a line or two o...Acccess 2007 Runtime and Queries
While I've created MDE files before, I've never used the Runtime for Access.
I was wondering if the runtime limits you to only running existing queries,
or can you modify them using the runtime too?
On Thu, 15 Apr 2010 11:51:01 -0700, MChrist
The runtime hides the objects window, so queries are not accessible.
You can see what the runtime will do by creating a shortcut like this:
path_to_msaccess.exe path_to_your.accdb /runtime
Microsoft Access MVP
>While I've created MDE files before, ...IF statement that yields a value based on a formula
Help! If cell B12 >= 0, then I need the answer to be calculated based on the
formual "B12 * .40". If cell B12 < 0, then the answer is zero.
How do I do this?
Click yes if helped
"barbara h" wrote:
> Help! If cell B12 >= 0, then I need the answer to be calculated based on the
> formual "B12 * .40". If cell B12 < 0, then the answer is zero.
> How do I do this?
> barbara h
...Last transaction in a query?
I have a table with 5 fields
ID PersonID EDate Amount and TransType
I want a query with the last record of each person based on date and
want the results like this
PersonID LastOfEDate Amount TransType
if a person has tow transactions in the max date I want only one of
How can i do that?
You must have a "main" table with a single list of PersonID.
Lets assume it is called tblPeople, PK = "ID"
So, use that table to provide a unquite list of PersonID.
The query can be like:
select FirstName, Lastname, WorkPhone,
PersonID, EDate, Amount, Tr...Crosstab Query by Week with Dates as Headers
I am trying to write a crosstab query in Access that will give me the totals
by week for each person.
My data contains a list a people with dates and amounts. Here is a small
sample of the data over five weeks for one person. I tried to put it in as a
table but it wouldn't let me so spaces are breaking the columns:
Date Name Supervisor Manager Amt
03/08/2010 Agent1 Supv1 Mgr1 3
03/07/2010 Agent1 Supv1 Mgr1 5
03/04/2010 Agent1 Supv1 Mgr1 8
03/03/2010 Agent1 Supv1 Mgr1 5
03/01/2010 Agent1 Supv1 Mgr1 9
02/28/2010 Agent1 Supv1 Mgr1 1
02/15/2010 Agent1 Supv1 Mgr...replacing values in cells from a pre-designed table
I am working with an Excel Spreadsheet. I have, in column C, a different value on each row: 1.2 or 0.7 or 2.5, etc. In column D I want to have Excel automatically place, in each row, a new value based on a reference table I would create.
If column C value is Then column D value on same row will b
...How do I get an automatic reminder with a certain cell value? #2
Is there a way to receive a pop-up comment or text box when a certain thing
is typed into a cell? For example, each time I type "new instrument", I want
a reminder to pop-up like a comment would that says "Call Property
Accounting". Is there a function like this in Excel?
Assuming data entry cells are col. A, right-click the
worksheet tab, choose "View Code", copy in the code
below, and press ALT+Q to close VBE.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim sMsg As String
If .Column > 1 Then Exit Sub
If .Count > 1 Then Exit...Insert spaces between pasted values #2
Is there a way to insert spaces between values that are pasted? For example, i have in rows 1,2,3 values a,b,c and i want to paste them into another list in rows 1,3,5 respectively (with a single-row space between pasted values).
I presume you have more than just 3 rows. A general approach would be
to use a helper column next to your data and to fill a simple sequence
down that column for as much data as you have. Then copy all the
numbers that make up that sequence and paste them immediately below
the sequence in the same column, so that if you have 200 numbers for