Find Next Row With No Value In It
I've got this
nextrow = Cells.Find(what:="*", searchdirection:=xlPrevious,
searchorder:=xlByRows).Row + 1
which will find nextrow with nothing in it and select cell in Col A,
but now I've got formulas in the cells, so instead of finding the next
row with nothing in it, I want to find the next row with no value -
even if it's got a formula in it. Can I do this by amending what's in
the what:="*" part of this code?
It's always better to include all the parms for .find(). If you don't, then
you're at ...Need VB code to edit cell values
I need some VB to do the following please (My VB is not good enough yet)
Starting from the active cell (call it the StartCell)
In the cell to the left..
ThreeChars = Value.right(3) (the last three chars)
Value = Value - last three characters (assume value is text & at least 3
value = ThreeChars (as text, overwrite any contents of StartCell)
move active cell one down from StartCell
Examples of before/after
Microsoft MVP Excel
"Stephen Ford" <Stephen_Ford_no@spam_uwclub....Inserting background clip art
I use Publisher 2000. I have read about people wanting to change a
background image from tiling to stretch to fit the page. My question is just
the opposite: how to tile the images to fill the background. I have not
found a way to do this in my current version and wonder if the newer
Publisher programs support this. If it is possible in P2000, please forward
Divide your page with the arrange menu to however many tiles you want, insert, new
drawing, create a rectangle the size of your desired tile, fill, fill effects,
picture tab, browse to your image. Use...How to combine two identically structured tables to query them
I am sure there is a way that you can have a query that effectively takes
two separate tables that have the identical column layout and add them
together and then perofrm a query on the combined set of data. I just can't
remember how to do it.
My reason for need in this is that I have a table called "SellerNumbers"
that has 4 million rows of data in it and what I want to do to improve
performance is archive the oldest 2 years worth of data to an identical table
called "SellerNumbersArchive", however the users want to be able to
occasionaly run quer...Advanced find
In advance find a customer wishes to know how many of each product an account
has ordered. The Order lines, Quote Lines, Invoice Lines are not available
as output entities. Therefore no reporting on advanced find is avaiable.
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...insert worksheet #2
The default for inserting a new worksheet is to the left.
Is there a way to have worksheets inserted to the right?
in VBA you could use the following macro:
you may apply a button to this macro.
> The default for inserting a new worksheet is to the left.
> Is there a way to have worksheets inserted to the right?
put this macro in your personal.xls file and attach it to a keyboard
shortcut or toolbar button.
Public Sub InsertSheetToRight()
...Export to Text Files Based on Field Value
I have an Access table with personal information (e.g., first name, last name,
birth date, etc.) for thousands of people. The table is sorted by state. I
want to be able to run a macro on that table that will create separate pipe
delimited text files for each state with all fields included.
Any help would be appreciated?
Message posted via AccessMonster.com
Instead of exporting the table, export a query based on the table. You will
have to have some code that loops through the states and modifies the quer...Inserting the email
Hi There I have a data base of users at our company and wish to know how to
insert "@(emailaddress)" after every user in all 5000+ cells and is it
possible to import this list to outlook ?
Are they all @thesame.com?
If your data is in one column, I'd insert a new column and use a formula:
Copy|paste special|values and toss the original column.
And if you save the file as a .csv (comma separated values), then you can
specify that inside Outlook for importing.
(I might even export the existing contacts as a ....Pivot table #11
I asked this yesterday but I am having difficulty getting
to my original post. Page 1 of this news group keeps
brining me to feb 17th.... anyways this is what i asked
Is there a way I can set up a pivot table with sub
categories? i.e. Type of Contact is my heading and under
it I have 4 categories (phone, walk-in, etc...)
I got this reply:
If you add Type of Contact to the row area, you should see
all of the
categories. Can you describe the fields in your data
table, and how
you'd like them to appear in the pivot table?
My answer to this is:
I would like...matrix and sorting with running value
I have a matrix which has 1 row grouping and 1 column grouping. The
column is a date. The matrix has 1 field which is doing a running sum.
I want the date column sorted in descedning order. I changed the sort
of the column group and the dates do appear to sort in desc order.
However the runningsum still appears to sort as if the date column is
in asc order.
...Auto Populate a form field
I have two tables with 1:1 relationship. Both have same key field (SS Number).
There are two forms, one for each table.
When I open Form A there is a button to open Form B. When Form B opens, I
want SS Number field to equal the SS Number on the current record in Form A.
How do I do this?
Thank you in advance.
Well, IMO the easiest way would be to make Form B a subform of A, and linking
via the common key. Or you could make Form A a tabbed form, and A would be
the first tab and B would be the second.
Just a couple of random thoughts...
"A Bit Frustrated&quo...Connecting tables
Is there any way to connect tables in Publisher similiar to the way you can
connect text frames for overfill.
> Is there any way to connect tables in Publisher similiar to the way you can
> connect text frames for overfill.
Ed Bennett - MVP Microsoft Publisher
Is there any other way I can do it? Any suggestions?
"Ed Bennett" wrote:
> kpappano wrote:
> > Is there any way to connect tables in Publisher similiar to the way you can
> > connect text frames for overfill.
> E...how do you formula's but leave the value.
hi,how do you formula's but leave the value.
if a1=1 and b1=2 and you have a code in c1 that says
c1 value would = 3
but how do you then delete the formula so that is stays
i hope you understand what i mean.
copy (don't cut) C1 and then click on C1 again and choose Edit / Paste
now choose values
and click Ok
this should give you what you want
"craig" <email@example.com> wrote in message
> hi,how do you formula's but leave the value.
> if a1=1 ...is there a way to automatically insert and print random pictures
I am trying to create greeting cards using a variety of different pictures on
the cover. Is there a way to automatically insert them from a file so each
card has a different picture?
You could setup a data base with the pictures, use the catalog merge.
Create a catalog merge
"judy" <firstname.lastname@example.org> wrote in message
>I am trying to create greeting cards using a variety o...Using VBA to find a value and select a range
I am trying to write a macro that will let me find a specific vale in a
specific column, then select part of the row that the value is in, beginnign
with a column 31 cells away from the column that the specific value is in.
Someone suggested to me that I use an IF statement, but this is all I know
IF [value in column B] is “Grand Total” Then select from column AF to the
end of the array in that row and copy it and paste a transpose of the values
starting in cell I9 of worksheet X.
What is the best way for me to accomplish this goal?
Does this macro do what you...Insert / delete ?
Hi, im trying to first insert a node in the xml file and save it.
XmlDocument mDoc = new XmlDocument();
XmlNode newNode = mDoc.CreateNode(XmlNodeType.Element, "page", "");
XmlAttribute mAttr = mDoc.CreateAttribute("id");
singleNode = mDoc.SelectSingleNode("//page[@id=\"" + pageid.Value +
if (singleNode == null)
Response.Write("no node found");
mAtt...Create Form Dynamically in VBA
Without going into alot of details I need to create thru code and dynamically
a FORM in Datasheet View with a recordsource is an exisiting query. Can this
be done in code on the fly. I also need code that deletes or purges the same
thanks for any help.
It is not a good idea.
Perhaps if you described what you want to do, we can help with a how.
Dave Hargis, Microsoft Access MVP
> Without going into alot of details I need to create thru code and dynamically
> a FORM in Datasheet View with a recordsource is an exisiting query. Can th...Look up table?
I am sure this is simple, but I dont know how to achieve it
I have 2 drop down lists the produce values 1-3 each
and a table A B C
1 500 550 600
2 450 500 550
3 400 450 500
How do I get the value at the intersection of the row/coloum, so if the drop
downs come up with 1 & b the answer should be $550
>I have 2 drop down lists the produce values 1-3 each
>if the drop downs come up with 1 & b
If both drop downs have selections of 1,2 3 then how can the drop down...Inserting a hyperlink when using Smart Art
Is it possible to insert a hyperlink when using smart art??
What table does the "More addresses" section use? The "More addresses" is
found on the side when you are filling out a contact.
"GDaxon" <GDaxon@discussions.microsoft.com> wrote in message
> What table does the "More addresses" section use? The "More addresses" is
> found on the side when you are filling out a contact.
Check Customeraddressbase. I hope this will help u.
"GDaxon&qu...OnCurrent of Form - Make not visible on condition
I currently have the following code under the onCurrent of my form:
Private Sub Form_Current()
Dim strSQL As String
Dim db As DAO.Database
If Me.ID < 0 Then
Set db = DBEngine(0)(0)
strSQL = "INSERT INTO LastVisitedRecord ( lvCompanyID ) " _
& " VALUES ( " & Me.ID & " ) "
Set db = Nothing
However, I'd like to add something to it, but have not had success.
I'd like the form to evaluate if the NOTES field is empty (which is
there but not visible) and if so, make other c...Split Database, Can't Append to Table
I have a table to which I daily upload financial reports into one table, and
run an append query to add those records not already listed in the master
table. I recently split the database because of some other random conflicts
we began to have once two users began to access the database. I read that
splitting might help. Now I am unable to append to the master table using
the append query. I get a message that there is a violoation regarding an
index, primary key, or relationship. I've also tried to manually cut & paste
the records from the results of the append query with n...Organizational Forms access in Exchange 2003 OWA
There is an HTML convert tool available for Org Forms in Exchange 5.5, but I
am unable to find a way to view our Org Forms in our new Exchange 2003 OWA.
Does anyone know of a way to view organizational forms in Exchange 2003 OWA?
...set value in subform
I want to use the set value macro to set a control in a subform based on a
control in another form.
I have three forms: FormA, FormB, FormC. FormC is a subform within FormB.
What I want is to be able to enter a value in FormA and then have FormC
Currently, if I open FormA and FormC I am able to use the SetValue macro to
However, if I open FormA and FormB (recall that FormC is a subform within
FormB) and try to run the macro, Access gives me an error. I'm assuming that
it is because Access does not think FormC is open (even though it is op...How do I insert a row (space) between bulleted (#) paragraphs?
I would like to insert a row or space between numbered paragraphs. When I
press Enter 2 times then a I get several blank rows (spaces) between the
paragraphs and when I try to delete the extra rows to get just one row
between the paragraphs, it automatically goes back to where it was before, no
row or space between the numbered paragraphs. I would appreciate your help in
figuring this out. Thank you.
Apply some Spacing After (or Spacing Before) to the bulleted paragraphs. You'll
find that setting in the Paragraph dialog box.
To open the dialog box:
- Click Format | Para...