MATCH, LOOKUP, macro?
I have a list of 176 items and another list of 870 items. I need to
see if the items in the smaller list are on the bigger list. There is
no guarentee of any ascending or descending sort order. And I need to
match two values in the same row.
IF (A2 is in B2:B871) _
AND **in the same row** C2 matches the value in col D _
THEN return "Yes"
MATCH and LOOKUP seem to require a sort order. And I'm not sure how
to use the row number with those. I could cobble together a macro,
but I thought a built-in function might be faster and easier (not to
mention I might actuall...URGENT
I have two worksheets with a common unique identifier for each employee. One
worksheet has the date of employment, and the other worksheet has a list of
training courses that have been completed by each employee. I need to
identify employees hired within a date range and check to see if they have
all completed their courses. This would be fairly easy if I could merge the
spreadsheets and sort by date of employment. I've been told that the best way
to handle this is to do a comparative analysis using vlookup, but I can't
find any info on that process.
As...Excel 2003 - VBA
A quick question on the "Workbook Open" event: When I load up Excel and open
the workbook, this event triggers. If I then close the workbook, without
closing Excel, then open the workbook, it does not occur. Close down the
worksheet and Excel, then open it, it triggers.
Is this normal operation?
Could it have to do with the possibility the I had set
Application.EnableEvents = False, before exiting the workbook.
Also put in
Microsoft MVP Excel
"Craig Brandt" <brandtcraig...Macro to insert to move values of a cell to next cell in the same row
I would like to know the Macro code for Moving the Values of a cell to
next cell based upon values in a particular cell.
Col A(DATE) Col B(0-30 Days) Col C(30-60days) ColD(60-90day)
Based upon date in Col A i want move the value in col b and c to the
next cell.Basically this for aging the items in the work sheet
Can anyone pls give the code for this query.
...macro button on spreadsheet
I know how to create a macro and how to attach it to a button on the toolbar.
What I would like to do is place a button out on the worksheet to run the
macro. When I follow the directions in help it doesn't work. THANKS for the
help. I am using Excel 2002.
Can you be more specific about what you mean by "it doesn't work"? Are you
able to create the button? How did you create the button -- via the Forms
toolbar or the Controls Toolbox? Can you create it but don't know how to tell
it to run your macro?
On Thu, 28 Oct 2004 21:07:01 -0700, "SheriTingle"
<Sh...Need help creating macros
For whatever reason when our system compiles data into Excel 2003 it creates
a worksheet that looks like this (for example):
Purple Purple Purple Purple
Purple Green Green
Blue Blue Blue Blue Red Red Red
Yellow Yellow Yellow Yellow
Brown Brown Brown Brown Teal Teal Teal
Black Black Black Black Grey Grey ...Code to combine workbooks
I need to combine several workbooks into one workbook. Say, individual
workbooks are named A, B and C, the master workbook is Master. They all have
the same format and are stored in directory c:\Data. Please can anyone help
and send me the code
Dim varr As Variant
Dim rng As Range
Dim i As Long
Dim sh As Worksheet
Dim wkbk as Workbook
varr = Array("A.xls", "B.xls", "c.xls")
Set sh = Workbooks("Master.xls").Worksheets(1)
For i = LBound(varr) To UBound(varr)
Set wkbk = Workbooks.Open("c:\Data\" & var...activate macro
how do i automatically run a macro when i move from one worksheet to another.
ie select another worksheet, then automatically run a specific macro
Put this sub in the ThisWorkbook module. Sh will be the worksheet that was just activated.
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
' take out the line above and put in your code
Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets ...Workbook reference is reset to  in chart data source spec
Operating System: Mac OS X 10.5 (Leopard)
I try to create some dynamic charts to a worksheet using named variables and it works great as long as I keep the workbook open, but when I close it and re-open it the workbook reference has been reset to  and the chart is not dynamic anymore. What do I do wrong? <br><br>In detail, this is what I do: <br>
I define the names: <li> chtRange to =Variables!$B$4, where Variables!$B$4 contains =COUNTA(Pace!C3:CU3)<br><br><li> chtLen to =Variables!$B$3, where Var...Macro if criteria
Hi, i have two macros. (macro 1 and macro 2)
I need macro 1 to run if in A1 is "JOHN", in B1 is "MARY" and if C1 is empty
; and macro 2 to run if in A1 is "JIM", in B1 is "CRIS" and in C1 is "BOB".
Can this be done?
If Range("A1").Value2 = "JOHN" And _
Range("B1").Value2 = "MARY" And _
Range("C1").Value2 = "" Then
ElseRange("A1").Value2 = "JIM" And _
Range("B1").Value2 = &...writing my first Macro
Hello, I am trying to write a macro for the first time in
OUTLOOK 2000. I find this will help me cause I don't get
around very well. But am having trouble to write one in the VBA.
Can you show me how to simply write a few sentences that I can
"automatically" include in an email reply to someone. Or if you
can send me just a very simple sample or example of a macro
that has a small paragraph email. that would be great.
I have been looking on "google for hours" now, and all I could find
was this sample below, but all it does is put in the "subject l...excel
I am trying to write a macro to save a excel spreadsheet and use a cell that
contains text like a customer name.
Record your macro to include the File > SaveAs, then edit that line of code.
Instead of ... Filename:="\\FinancialReporting\FY2006" ..., you might use
.... Filename:="\\FinancialReporting\FY" & range("B2").value ...
"bossman tv" wrote:
> I am trying to write a macro to save a excel spreadsheet and use a cell that
> contains text like a customer name.
...transferring macros from one PC to Another
I have some macros on one PC. The machine is very slow and I want to
format it. But I want to transfer all my macros to a CD or another PC.
Please help me in doing that. Tell me how can I do it.
Suman, to move to another computer you can export a macro and save it, to do
this from your workbook, right-click the workbook's icon and pick View Code.
This icon is to the left of the "File" menu this will open the VBA editor,
click on the module you want to save and go to file and export file, then
save it to a CD or what ever you backup to, then import it to the...Macro to delete rows 03-17-10
I'd appreciate some help in creating a macro to delete rows.
Here's what I need:
1) Delete rows 1-13
2) Then find all rows that are highlited blue and delete them
3) Then find all rows that include the text "Distr" and delete them
Thanks in advance.
Tell us how the rows came to be blue...
Microsoft MVP Excel
"JeffF" <JeffF@discussions.microsoft.com> wrote in message
> I'd appreciate some help in creating a...Button fails to call macro when open an Excel via Intranet
Can someone help me with my with this problem.
I have an Excel file with a button to run a small macro: unhide and displys
worksheets. It works when the file is opened with Excel.
The macro is simple:
Sheets("FF").Visible = True
I need to open it from an intranet site. When user selects the file, the web
retrieves the file and responses to open an Excel file.
But on the intrenet I get the following error massage
"The macro "GenerateResource.aspx?Resour...Need help with Multi-workbook COUNTIF formula!!
I have monthly worksheets that use drop down boxes to pick criteria from
lists. The linked cell outputs a number based on what the chosen criteria was
in the drop down box. Currently I use this outputted number on a summary
sheet with a COUNTIF formula ie COUNTIF(Sept!$R$3:$R$200,2) to summarize
monthly info. I would like to generate yearly info instead without having to
generate monthly totals first. Is there a formula that can use both ranges -
Jan:Dec and $R$3:$R$200 - with the criteria of the linked cell ie. 2?
...How to insert a Macro within a macro?
I have a sub Format()
and I want another macro called: AddRows()
To run in the middle of the sub Format()Macro.
What I did doesn't work.
I had Sub Format()
How can I do this?
"Thrava" <firstname.lastname@example.org> wrote in message
> I have a sub Format()
> and I want another macro called: AddRows()
> To run in the middle of the sub Format()Macro...macro to change column to keep only last 5 characters
I'm not sure how I can use left or right or trim on this, but any
Assuming data in column A, in B1 enter =RIGHT(A1,5) and double-click fill
handle to copy down.
Macro method. You choose the activecell.
Dim rng As Range
Set rng = Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column).End(xlUp))
For Each cell In rng
cell.Value = Right(cell.Value, 5)
Gord Dibben MS Excel MVP
On Thu, 6 Dec 2007 13:33:51 -0800 (PST), S Himmelrich <email@example.com>
>I'm not sure how I can use left or right ...Run macro from if statement
Is there a way to run a macro from the results of an if statement? i.e. if(cell a2=1,"run macro",dont run
Any ideas? What I am trying to do ultimately is show a userform if the contents of a cell are changed
I can't find a way to run a macro, but where is your
userform that you want to show? It is possible to call
another worksheet or area in the formula. If your userform
isn't on an Excel worksheet you could paste it into one.
>Is there a way to run a macro from the results of an if
statement? i.e. if(cell a2=1,"run macro&q...macro to find phone number and label with text
Here's the situation: I have long lists of satellite phone bills that
I need to track by individual phone users and I want to build a macro
that seeks out a specific number (example, macro searches "882 156 445
4554" and in the column next to it, automatically spits out "Dan's
In one Excel file, I have a growing list of satellite phone numbers
(~400 numbers) that I'm constantly adding to that I'm hoping the macro
can draw from. In the other set of files, I have individual phone
bills for each phone (about 18 separate excel files per month) wi...excel workbook
I want to know why there are
blank lines after the 1300 lines I imported? I want the spreadsheet to just
cut off at , lets say, 1310 lines. How would that be accomplished?
I would also like the just have cells A thru H and no cells to the right of
that. How can that be done?
you can't delete these rows/columns. What you could do is hide them
(Format - Rows/Columns - Hide)
> I want to know why there are
> blank lines after the 1300 lines I imported? I want the spreadsheet
> to just cut off at , lets say, 1310 lines. Ho...Attachng a Macro to a worksheet/book
I have created a spreadsheet that contains a macro to perform certain
calcuations and functions. However, it ony works on my PC. If I send
it via email to someone else to use I get a message stating that "The
macros in this Project are Disabled"
However, when I go into Tools, Macros and Run Macro, I can see it, but
it will not run it? Any ideas?
Sounds like the security settings in the other person's computer are
set to high.
Tools > Macro > Security
Set to Medium (gives a choice on opening a book with macros in it)
Set to Low (allows all macros)
...is there anyway in an excel macro to reorder the sheet tabs from left to right?
is there anyway in an excel macro to reorder the sheet tabs from left to
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"Daniel" <firstname.lastname@example.org> wrote in message news:OctZdyBeFHA.3028@TK2MSFTNGP09.phx.gbl...
> is there anyway in an excel macro to reorder the sheet tabs from left to
...Save file macro
I have made a macro that saves a file with the word 'new' appended to the
end of the filename.
However examining the macro shows that it is therefore unique to that
How would I change it so that when I attach the new macro to a button (which
I can handle) it will attach 'new' to the existing file regardless of the
Or is there a better way of achieving my objective?
Brian Tozer wrote:
Return to your Xmas pudding.
...How to open a workbook into a new Excel application
I'm wondering if there a way to open a workbook into a new application by
default. So, when you double-click the excel workbook, it will start in a new
Excel application. I think about something in the Open event but I don't know
how to cancel the process and restart it into an other application.
Excel is an application. Normally Excel runs with only a single instance of
You can do OLE linking with programs like MS Word. This sort of runs Excel
I have seen multiple instances of Excel running a...