I need to copy autofilter rows from one worksheet to another (within
the same workbook).
My autofilter code is:
Selection.AutoFilter Field:=1, Criteria1:="TRUE"
I'm not sure how to do the following:
1) code to copy the rows found to the other worksheet
2) how to cater for the situation if no rows are found.
I've researched but all the help Google returns is rather confusing.
Can someone please advise.
On Feb 16, 2:43=A0pm, kazzy <kazm...@gmail.com> wrote:
> Hi VBAers,
> I ne...If statement- formula
Well I'm stumped again.
If close!ag2:ag19999 contains "xyz" enter contents of close!ag2:ag19999,
otherwise enter contents of close!ae2:ae19999
what do you mean with 'enter contents'?. Do you want to add them? If
> Well I'm stumped again.
> If close!ag2:ag19999 contains "xyz" enter contents of
> close!ag2:ag19999, otherwise enter cont...Sub to check and report any formula returned error (eg: #REF!)
I need a sub to run a quick check through a list of sheets (using their
codenames) filled with a ton of formulae, and pop up an all clear msg if
there are no errors (eg: #REF!) returned in any formula cell. If there are
errors, msg will list the affected codenames. Thanks
hi, Max !
> I need a sub to run a quick check through a list of sheets (using their codenames) filled with a ton of formulae
> and pop up an all clear msg if there are no errors (eg: #REF!) returned in any formula cell.
> If there are errors, msg will list the affected codenames. Thanks
try with som...Copy Userform ListBox AND TextBox values
How can I copy the values from ListBox1 and TextBox2 from the same Userform
into the same cell on a worksheet?
I have the code to copy either one to the correct location but I need to
merge the values from both, seperated by a slash (/). I have tried using the
following but it will paste "FALSE" into the appropriate cell:
.Value = ListBox1.Value & " / " & .Value = TextBox2.Text 'copies data
to column B
Traa Dy Liooar
Ignore last - I have managed to do it.
Traa Dy Liooar
...Autofill of row or column
Let's say I have a min, a max, and an increment, all decimal values. I
want to autofill or autopopulate a row or column -- without manually
dragging cels -- over the range [min,max] incrementing by the increment
value each cell. Can this be done somehow? Also, what if you don't
know how many cells you will need initially (you return some variable
integer from a calculation)--can you tell Excel to fill only that
number of cells each time? For example, let's say I compute some
number and get a 7 one time and a 10 the next, and I want 7 cells
filled with data and then ...Absolute Values and Column Insertions
I've created a formula in column A that adds up some cells in column D. Ex:
=sum(D3:D6). If I insert one column in front of column D, the formula changes
to =sum(E3:E6). This happens even if I change the formula to =sum($D$3:$D$6).
I also tried creating a range name called TEST referring to the original
$D$3:$D$6 range and the column insertion changed the TEST range definition as
Is there a way to keep a formula from NOT adjusting to column insertions or
deletions? Thanks in advance!
Please don't multi-post
See answer in other newsgroup
Mic...uninstalling microsoft office 2001 value pack
I already have office 2001 on my machine, and recently
installed the value pack. i noticed word became notably
slower and so for now i just wish to unistall the value
pack. how can i do that cleanly.
any ideas why my machine would slow down like that. i
still do have 1.2 Gig of hard disk space available.
...Copy a file with only values no formulas
Is there an easy way to copy a workbook so that it only has the values and
not the formulas?
Right click on a sheet tab and select all sheets. Then select all cells on
the active sheet. Do a copy, then Edit, paste special, values. Right click
on a sheet tab and select ungroup sheets. Save the workbook under a
Productivity add-ins and downloadable books on VB macros for Excel
"Lee Coleman" <firstname.lastname@example.org> wrote in message
news:OIOx4$FeHHA.4020@TK2MSFTN...How to average a column, but exclude zero AND negative values?
I used the formula of =AVG(IF(A1:A10)<>0,A1:A10)) to exclude just the zero
values, but what about zero AND Negative?
Still array entered.
> I used the formula of =AVG(IF(A1:A10)<>0,A1:A10)) to exclude just the zero
> values, but what about zero AND Negative?
...IF String value the....
I am trying to calculate a formula based on the right most character in a
For instance if a cell had a entry like Baker T. I want to calculate a
formula if the T is there and another if the T is missing.
Thanks in advance
=IF(RIGHT(A1,1)="T","do something","something else")
> I am trying to calculate a formula based on the right most character
> in a cell.
> For instance if a cell had a entry like Baker T. I want to calculate
> a formula if the T is there and another if the...Request To Shorten A Formula
Hello, don't want to be pest on my Duplicate Questions, but I was hoping
someone could give me a shorter formula for the one I have here.
I have 31 sheets representing 31 days (DAY1 - DAY31), of the calender
in which I check for dupes in the Phone Number Column E6-E35 in all
sheets, This is the Formula that works, but I want it shorter if
Possible.. Thanks for any Help:
----------------------------------...Automatic Calculation of Table Formulas
Is there a way to set Word so that a target cell automatically re-calculates
whenver any of the cells used in the formula is changes?
Only if the components of the formula are provided from the results of form
fields in a protected form and you have the calculate on exit check box
properties of the fields checked. Otherwise you would have to force an
update of the fields in the calculation. You can do that with a macro, such
as that used as an example at http://www.gmayor.com/installing_macro.htm
Alternatively you can insert an Excel table and have the full panoply of
Excel fu...How to Link to the next blank row after the last entry in a column?
Cell BM6 Sheet1, has a formula that gives me a numeric total.
The total belongs in Column D1 Sheet2 after the last entry, and there
will be cells that will remain blank in that column before the last
Sheet1 is a work sheet, which will be saved with a new file name at the
end of the month after the totals have been sent to Sheet2. Sheet2 is a
permanent, ongoing record.
The numbers on original Sheet1 will then be erased and the worksheet
reused for the new month.
:confused: How do I get the values into the appropriate row of Sheet2
and have them remain there when I reuse the workshee...automatically numbering rows
I wish to have rows automatically numbered in column "A" according to the
content in column "B". If column "B" has text or numbers in a cell I do not
want to number that row in column "A". If column "B" is blank in a cell I
want to number that row in column "A". I found the following formula that
works the opposite of what I want "=IF(B1<>"",COUNTA($B$1:B1)&".","")" .
What would the formula be if I wanted to numerically count the blank cells
in "B" and skip the conte...print column labels with one row of data at a time.
I am a teacher. I have created my gradebook in excel. (I don't have
access). I have 7 worksheets (one for each class that I teach). The
worksheets are not exactly the same.
I would like to be able to print out the top two rows (column labels) with
each students data (row). This way the student can see row 1 (name of
assignment), row 2 (points), their row (their personal data for each
File > Page Setup > Sheet
Then in the "Rows to repeat at top" highlight the label headings row(s).
London...Use query for Row Source?
I attempted to use a combo-box to look-up records on a form. The RowSource
query that is generated by the wizard included 2 data fields. I wanted to
show only a distinct list of the second, non-key data element (col1). When I
changed the RowSource query to SELECT distinct Col1, the combo-box displays
nothing. I then created a stored query with the SELECT distinct Col1. They
query runs correctly, but when I put in in the RowSource, it displays nothing.
Any clue what I could be doing wrong?
On Fri, 26 Oct 2007 12:17:03 -0700, JHC wrote:
> I attempted to use a combo-box to look-u...How do you find windows shared folders?
I am writing a piece of software to back up a shared folder on one
computer to another computer.
I need a list of the shared folders available on the network I am
connected to by ethernet.
The computers are both windows machines... One is a Windows 98 computer
and the other (the one that is doing the query) is windows XP.
I am writing in VB6 and Visual C++ (I do OCXs in C++)
I am comfortable writing socket level code to broadcast a discovery
packet if that is necessary,
and parse the response results, but if there is already an API for this
then of course that would be better.
B...Copy/Paste with Hidden Rows/Columns Excel 2007
I am working with rather large & detailed spreadsheets. I have hidden rows
and columns with supporting details that feed into the viewable results
I need to take the “zipped up” data and copy/paste to a new worksheet. When
I paste the data, it appears with all of the hidden columns and rows visible
Is there any way to paste the zipped up data without the reappearance of the
previously hidden rows and columns?
Can you paste a link without all of the hidden data reappearing?
Select range to copy which includes hidden rows and columns.
F5>Special...Paste Formulas Only
How can you copy a range and then paste cell contents only if the cell
contents is a formula (cell contents begins with "=")?
Pick up only the formulas
select the range to copy, then do Edit=>Goto =>Special and select formulas
then do your copy.
"Brett" <Brett@discussions.microsoft.com> wrote in message
> How can you copy a range and then paste cell contents only if the cell
> contents is a formula (cell contents begins with "=")?
Isn't it so that it ...Formula #32
I need a formula for this:
If C1 is blank and B1 is less than 4/03/2008 count as 1.
A B C
1 Almond Joy 04/01/2008
2 Baby Ruth 04/02/2008
3 MARS 03/28/2008 04/01/2008
4 Mounds 04/10/2008 04/15/2008
5 Reeses 05/01/2008
6 Starburst 03/25/2008 03/31/2008
Adjust the ranges to suit.
Hope this helps.
On Sep 2, 10:08=A0pm, Accesscrzy <Accessc...@discussions.microsoft.com>
> I need a fo...dragging formula
I have a small formula that wont drag down
the column. The answer of the formula is what is
dragging. How do I fix this so I have the correct answer
in each cell?
Are you getting an error? What happens when you try and do it? Are the cells
blank afterwards? Is the worksheet/cell protected?
The formula looks fine. If you are dragging by the little black cross in the
bottom right hand corner of the cell, there should be no problems.
"mary kay dougherty" <email@example.com> wrote in message
I got the error 6298 below every 5 minutes on my Sharepoint server (MOSS
Tried couple of things to no avail.
Your help is greatly appreciated.
Log Name: Application
Source: Windows SharePoint Services 3
Date: 12/11/2009 11:00:04 AM
Event ID: 6398
Task Category: Timer
The Execute method of job definition
2d1ee7c5-0ee5-46b9-9047-b35bf78afda2) threw an exception. More information
is...Excel XP automatic equals sign when formula bar clicked
In Excel 2000, you could click on a cell, then click on the
formula bar and an equals sign would pop up in anticipation
of a formula to be written. How can you make Excel 2002
duplicate this behavior?
This was removed from Excel 2002 onwards.
The closest you'll get is:
View > Toolbars > Customize
Under the Insert list you'll find "="
Drag it to a toolbar near the formula bar.
Norman Harker MVP (Excel)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
&q...dafault value for date not working with Date() but Now() is workin
Got a table with existing data, with simple date field on a form that I want
to default to todays date. I have tried inputting the Date() into the table
default value for the date, but I get an error that says "Unknown function in
'Date' in validation expression or default value on 'Data.date'. Data is the
table name. If I enter Now() into the default value for date, it works just
fine. But, the reports that are generated, don't show the records with the
date and time in them. I can go back to the table and manually delete the
time from those records...Display nothing in a cell if value = 0
I have a range of cells formatted to display numbers in currency format.
They have a formula applied to them so if the value of the Cell is nothing I
get �0.00 showing.
I would rather that nothing was shown in these instances. How is this
Couple of ways,
use conditional formatting and display with a white font if the value is
change the display option to suppress zeroes, Tools>Options>General and
uncheck the Zero Values option.
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mail...