Macro to find value in worksheet
I've been asked if I could come up with a macro or vba that I could
enter a list of names in the code and run the macro to find any matches
of the names in the open worksheet. I'm a newbie at this and I'm lost.
Is there anyone who could help me with this code? It seems like such a
simple task but I know better.
I'd have to enter about 100 names individually and do a find every day
to see if certain names were in this sheet. The sheet changes every day,
the names don't. I'd rather have the names hard coded in the macro and
just run it and have it pop up a message say...delete 0 values
Hi I am trying to delete cells with 0 values, even though i type in $0.00 in
find, it finds nothing.
i get the message find under values not formulas, but there is no option for
this in the find options.
If you ask find to look for $0.00, then it will search for the text
string "$0.00". If you want to find numeric zeros (regardless of
format), then just type 0 in find.
If that fails to find any, then I suspect that the values you see as
zero are not exactly zero (try formatting in scientific notation).
You can use the ROUND() function to force the results of ...Dlookup minimum value
I'm trying to use Dlookup to get the minimum date from a table.
I was trying to do the following command:
Somehow the code points an error on"Min", saying that "Sub or Function not
Is it possible what i'm doing? Is there any other way instead of looping
through all the records?
Try DMin() instead of DLookup()
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rat...Need to split XLS file based on value of field
I have an XLS file with several thousand records.
Several of the fields use a restricted drop-down list for entering dat
- the choices on the drop-down list being pulled from "hidden
worksheets. I say this because I think this means that the file has t
remain XLS and not CSV or that restricted trop-down won't work.
I now need to split this file into about a 100 files, based on th
value in one of the columns.
Is there a simple way to do this?
Message posted from http://www.ExcelForum.com
Try this set of macros. First, create a folder and put
only your main data file in ...How to make a form behave like msgbox
I have a 'Mass Email' button. When clicked, it brings up a modal popup
form. Use can read the certification paragraphs, scroll down and click
'OK' button which closes the form. Then, the rest of my code should
run. How to holt the code from running like msgbox after user clicked
OK button on the form? The form is based on a table with single memo
field and the form will be resued in other places as well.
Open the form like:
If you also need to use values of the modal popup in the calling code y...Filtering a MS Access Dataset
I am a newbie making a database app. My database is a MS Access
database. I have a startup form with a datagrid showing all the
records in table called Report.(The Datagrid automatically created a
dataset of the entire database.) When a report is selected on this
form, I want another form to open which has data aware text boxes
connected a Details table.(I have no problems creating the forms) Both
these tables have a RecordID and I want this form to display only the
records which have the same RecordID as row which was selected in the
Can someone please help me out on this?
...Crm 4 event log warnings. CRM Parameter Filter
I am looking for a solution on the following issue. Any suggestions will be
very welcomed! Thank you in advance.
Background: We just upgraded MS Crm 3 to Crm 4
Every time when a user tries to save an update of a record. A warning will
be logged into Event Viewer --> Application. It happens to all entities.
1. I am doing some updates on the contact form
2. Click save
3. I will find a warning message in Event Viewer --> Application
Exception information: ...Count if between date parameters
I need create a formula that show how many patients were available to be seen
on each day of the month. I would like to make the cut-off for changes of
dates as 3pm, meaning that one day is from 3pm - 3pm the next day.
e.g. - 12/30/09 available patients would be all those there from 3:01pm on
12/29/09 to 3:00pm on 12/30/09.
If they're available for the full day count them only. Using this logic with
the data below on 12/30/09 only 2 patients were available to be seen.
Name Request DT Res DT
P1 12/27/2009 1:26 12/31/2009 16:28
P2 12/27/2009 12:00 12/30/2009 7:20
P3 ...Crosstab Query
I have some data that I need to flatten by a particular field, but it is
duping up to 7 times due to one field. Unfortunately, the value of these
fields is different for each unique record, so a simple crosstab will not
work. Here's an example
Field to Flatten Duping Field
If there were only two dupes per "field to flatten" i could use "FIRST" and
"LAST" or "MI...Cdn Payroll Report
We have not been able to make an advice slip (pays stub) report works well,
especially the calculation for vacation days earned. Does anyone have a
report that they could share some advice on, preferably Canadian payroll
I have done one of these for a client, but I don't have anything
available to look at right now as I am not in the office. If you post
a contact, we can correspond off of the newsgroup.
On Wed, 22 Feb 2006 14:00:27 -0800, Doug J <Doug
>We have not been able to make an advice slip (pays stub) report work...Comparing input to numerous values
Is there a simple way in VBA to compare input from the user to a set
amount of constants? Example: Input box asks for a value, then I need
to compare that value to A,B,C,D. If the input does not equal one of
the four then an error msg pops up. Would rather not write four if
Thanks in advance,
mjack003's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=5141
View this thread: http://www.excelforum.com/showthread.php?threadid=494017
Maybe like...Unlock cells without values
I do many files where I have to lock the cells for users,
Is there any way to make excell unlock every cell that is empty and
lock every cell which contains data or formulas in ?
Press F5 (Edit ->Go to) -> Special -> Blanks
Now, without changing the selection, unlock cells.
Note: This will select only those empty cells in the "used range".
If you want to unlock ALL the blank cells in the sheet, then, first
select all, unlock
then using the go to special, select formulae & constants and lock them
Hope this helps
I have a problem with my report about Accounts Receivable. It is supposed to
show all the Accounts Receivable, regardless if whether they are paid or not.
Now, the problem is that in the textbox "Total", it should only include those
which are not paid,which could be determined by the checkbox "Paid" which is
also shown in the report. If there is a check on the box,it means that the
account has already been paid. The textbox "Total" sums the value from the
textbox "Amount". The values are from the same query, What criteria should
be in placed and wh...Get row values into column
I have a row (A:BI) with text or numbers in each cell. However, there are
some cells that are blank. B,D,F,H,J,... are the number cells (unless they
are blank) and A,C,E,G,I,K,... are the text cells (unless they are blank).
I would like to have all the cells that have text in them to be entered into
cell A5 and down. Same thing with the number cells except in cell B5 down.
A B C D E F
1 PHI 2.3 OTT 3.6
I would like the following:
A5 = PHI B5 = 2.3
A6= OTT B6 = 3.6
Anyone know how ...Query Parameter
I want to supply a parameter to a query to define the from date and to date
and retrieve data falling under this from and to date.
but, i do not want it to be prompted, as the same criteria is to be used in
more than one table.
so, i want to store the from date and o date in a temp table and related
this to the date field in different tables.
You could create a table (tblDateRange) with 2 date fields (datStart and
datEnd) and only one record. Then add tblDateRange to your query and don't
join it. Set the criteria under your date field to:
Between [datStart] and [datEnd]
...copying values/text, not formulas
Heya ... I've got a workbook that consists of a "main page" with several
columns of formatted text, and several columns of VLOOKUP'ed text drawn
from several other worksheets in the workbook. My boss wants only the
main page and not the others (too much space on his HD) ... I've been
trying to use "Paste Special" to do this, first copying the formats
over to a new page (looks exactly like what I have on the main page)
and then copying the values.
It works fine as long as I have only single cells selected. What goes
wrong is when I have merged cells selected...merging the values of a field in a subreport
helloi am trying to prepare route cards for travels. I am preparing theseroute cards in an access reports. the route points, the stops oftravel, has been provided in a routeno-stop table. I am trying tomerge the values for a particular travel. for example, the table is asfollowsroute no: stops1 museum1 sports centre1 tennis court2 zoo2 city hallwhat i want is merging these values asfor 1: museum-sports centre-tennis courtfor2 : zoo-city hallis it possible and how?...Link Tables in Report Writer
Im working with Report Writer in GP10 I read David articles and they are
interesting but my question is:
for exemple I want to add the "expiration date" to the "POP Receivings
Posting journal" report let's assume I found the table containing the
how do I get to it when creating table relationships Im new with GP and Im
having difficulties what tables to link to get the final table.
I really tried everything to figure it out but I didn't succeed I hope I was
clear explaning this.
thank you guys in advance
It sounds li...User Defined Function returning #Value!
I have a function that I created. When I test it in the Intermediate Window,
? Kountifs("Registered Nurse"), it returns a 12 which is correct.
I want to be able to use this function a my datasheet. I have included
basically the same function =Kountifs("Registered Nurse"). But, ont the
datasheet I receive a #Value! rather than the 12.
Does anyone have ideas why?
Why dont you post the UDF..
If this post helps click Yes
> I have a function that I created. When I test it in the Intermed...Values from PickLists on Reports
I have a picklist for my accounts screen. I want to create a custom report,
and have it be sortable by the picklist "name". However, when I look in the
AccountBase table, only the value is stored in the table (which, of course,
makes perfect sense.). How do I access the descriptive value, and not the
numeric one, for my report?
Picklist values are stored in the stringmap table.
>I have a picklist for my accounts screen. I want to
create a custom report,
>and have it be sortable by the picklist "name". However,
when I look in the
>...Publishing forms to Organizational Forms
When publishing a form to organizational forms I get the
following error message. There are no articles in the KB.
"an error occurred while going to this folder"
All permissions have been checked and are okay.
If there are any REAL ideas about what is going on in
Exchange, please post a reply.
Thanks to all!
...splitting the values from one column into two
I have spreadsheet that has a column for names. The names have been input as
follows: lastname, firstname". Is there a way to copy the first name to a
new column and the last name to a different column?
Data>Text to Columns and choose a delimiter of comma
"Elise" <Elise@discussions.microsoft.com> wrote in message
> I have spreadsheet that has a column for names. The names have been input
> follows: lastname, firstname". Is there a way to copy the...How to customize column width in Ressource Usage report ?
When printing the Workload, Ressource Usage report, some of the durations
are stated as #####.##. I tried to make the font smaller but it did'ent help.
How do I make the report readable?
I am using MS Project 2007 SP2.
If you goto Reports, Custom
you can see all of the built-in reports and you can see what they are made
of and you will see that they use a filter and a table (and other settings
The column width of each field is a property of the table.
Find the report that you are interested in, then find the table that it
uses, then e...Request: Checking XsltArgumentList for item presence
I would like to request a new method on the XsltArgumentList class, allowing
developers to check the presense of a key/value pair. If you try to add a
key/value pair that has already been defined in the list, an exception is
Rather than using the "GetParam" method, it would be usefull to have a
method called ".Exists" available on the XsltArgumentList. I think this
allows for cleaner and more readable code. Or, perhaps even better - an
overloaded "AddParam" that allows you to force an existing key/value pair to
be overridden with a new value.
Ima...Email fro FORM
Hi All, I need assistance with the following code am using to send an email
from my form.
rivate Sub Command21_Click()
Dim SendTo As String, MySubject As String, MyMessage As String
SendTo = Me.[Email]
MySubject = "New Complain"
MyMessage = Me.Complaint_No & vbCr & Me.Complaint_Type & vbCr & Me.CSR & vbCr
& Me.Customer_Name & vbCr & Me.Card_No & vbCr & Me.Card_Type & vbCr & Me.
CC_Department & vbCr & Me.Complaint
DoCmd.SendObject acSendNoObject, , , SendTo, , , MySubject, MyMessage, True
I need few changes if you can help.