Adding a Macro to a VLookup Function

In Excel 2000 -- I would like to create a summary spreadsheet (sheet2).
For each time that "Name" appears in sheet 1, row *, take information from 
the same row, but columns 2 and 6 and bring it over to sheet 2 in the 
designated area.
I know that you need to do a "VLookup" function and I got that to work.  I 
know that you also need a "Loop" statement, so that it will continue to 
perform the Vlookup and bring over the information for each time that the 
name appears.  I don't know how to write the formula so that the VLookup and 
the Loop are combined.
Example.  For each row that name "Marc" appears in sheet1 bring over the 
"organization" and "amount" information from that row to sheet2.  Continue to 
do this until you have every instance until you have all of them.  
0
Utf
8/15/2005 7:45:03 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
438 Views

Similar Articles

[PageSpeed] 37

Hope this is helpful

Go into Visual basic Editor and paste it in a module, the look it as a macro 
"ReturnValue"

Sub ReturnValue()

Dim x As Integer        ' Is a simple variable for counter
Dim iTotalRows As Integer
Dim iTotalFoundItems As Integer
Dim iColumnReturned As Integer

'----------Customize these variables----------

'   Define the number of rows it is going to make the loop
    iTotalRows = 10

'   Define the row it is going to start outputing the results
    iTotalFoundItems = 1

'   Define the column that is going to be returned if the value is found 
(take note that the value found is the First Column)
    iColumnReturned = 2
    
'----------Change the procedure if need it ----------
'   A simple way to do the loop
    For x = 0 To iTotalRows
    
'       Seeks if the current cell is the value searched for
        
        If ActiveCell.Value = "Marc" Then
            
'           You can customize the "A" letter for the column it is going to 
be printed, and copy the line to bring another value
            Sheets("Sheet2").Range("A" & iTotalFoundItems).Value = 
ActiveCell.Offset(0, iColumnReturned).Value
            
            iTotalFoundItems = iTotalFoundItems + 1
        
        End If
    Next


End Sub

-- 
Rgds,


Cosmic


"Wanda H." wrote:

> In Excel 2000 -- I would like to create a summary spreadsheet (sheet2).
> For each time that "Name" appears in sheet 1, row *, take information from 
> the same row, but columns 2 and 6 and bring it over to sheet 2 in the 
> designated area.
> I know that you need to do a "VLookup" function and I got that to work.  I 
> know that you also need a "Loop" statement, so that it will continue to 
> perform the Vlookup and bring over the information for each time that the 
> name appears.  I don't know how to write the formula so that the VLookup and 
> the Loop are combined.
> Example.  For each row that name "Marc" appears in sheet1 bring over the 
> "organization" and "amount" information from that row to sheet2.  Continue to 
> do this until you have every instance until you have all of them.  
0
Cosmic (8)
8/16/2005 7:37:05 PM
Reply:

Similar Artilces:

adding MIme for PDF
When I send a pdf file from Outlook 2003 (Exchange 2003 SP1) it recognizes the pdf file as an application\octet-stream. How can I change this so it recognizes the pdf as a pdf? This is causing issues because application\octet-stream are blocked and pdf's are allowed. Any thoughts? Internet Message Formats in Global Settings -- Bharat Suneja MCSE, MCT -------------------------------- "Bad Beagle" <maxwelli@nospam.postalias> wrote in message news:%23j21Ke8vFHA.2008@TK2MSFTNGP10.phx.gbl... > When I send a pdf file from Outlook 2003 (Exchange 2003 SP1) it recogn...

Remove AD
Dear all, What is the best way to remove AD services from an existing Exchange 2003 server? Thanks. You shouldn't. If Exchange is installed on a Domain Controller, it should not be demoted to a member server. You should install another Exchange server and move mailboxes, replicater public folders, et al. Then remove Exchange from the old server. Now you can demote it to member server. Install Exchange again and move stuff back. -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------- "KA Kueh" <kka@ksm.com.my> w...

Adding holidays #2
I added US Holidays to my calendar. Outlook added the holidays up to the year 2002 and to the current year or previous year. Any suggestions will be appreciated. Use Office Update to get the latest holidays added. -- Roady [MVP] www.howto-outlook.com Tips of the month: -Creating Signatures -Create an Office XP CD slipstreamed with Service Pack 3 ----- "A E" <selbe@netzero.net> wrote in message news:uDpUEEUREHA.1644@TK2MSFTNGP09.phx.gbl... >I added US Holidays to my calendar. Outlook added the holidays up to the > year 2002 and to the current year or previous yea...

Error when adding new user to CRM
Hello all, I need to add 5 new users into CRM. When I complete the first form for the user information such as Last name, First name, email address, etc, then click the save button I receive an error page and the following message is logged in the Application log. MSCRM Platform Error Report: -------------------------------------------------------------------------------------------------------- Error: The statement has been terminated. Error Message: The statement has been terminated. Error Details: Details on this error have not been provided by the platform. Source File: Not avail...

Lookup Function in user form
I have a combo box (cboitem1) and a text box (txtprice1). I am using the code below to have txtprice1 fill with the corresponding price the worksheet: Private Sub Cboitem1_Change() If cboItem1 <> "" Then txtprice1.Value = Excel.WorksheetFunction.VLookup(cboItem1.Value, Sheets("Pizzas").Range("A1:B65536"), 2, False) End If End Sub It works perfectly when you enter an existing value in cboitem1. However if 'the user' accidently types a letter that is not the first letter of one of the existing items VB debugging pops up with error &qu...

Track in CRM function
Hello. Upon selecting the “Track In CRM” button from the CRM line in Outlook I have two questions based on situation: 1) If the contact exists in CRM it appears to attach the email but provides no notice unless you try to attach it again? True? 2) If the contact does not exist it attaches the email where? In v1.2 it opened a window and made the user create a record. I think it is attaching or tracking the email in CRM somewhere because trying to do it again, I'm asked if you want to attach/track it again. Does this make sense? By the way, we are using CRM 3.0 Thanks. After c...

SBS Exchange 2003: Create new user in AD but no exchange mailbox #3
I'm using SBS 2003. Exchange server is SP2. When I create a new user using Active Directory, it prompts me about creating a mail box. It says it's creating a mailbox, then it's done. But no mailbox appears. In fact, if I use the Exchange tasks wizard, I can create, delete, and recreate the mailbox--all say successful without an error message. And yet, no mailbox. This is even after I reboot the PC and sent email to the address. Any ideas what's going on? mike.aes@gmail.com wrote: > I'm using SBS 2003. Exchange server is SP2. > > When I create a new user ...

Macro Question Again
I'v never had any dealings w/ macro before, so this is all new to me. I have a windows 98 computer that is running office 2000 and there is an excel macro on this pc that was created by someone years ago. I have to get this 98 machine of the network and replace it w/ a 2000/XP platform also running either office 2000/xp. So far i'v copied the macro and am able to run it, but its not gathering the correct info. It is collecting the correct info on the 98 machine, just not the one im replacing it w/. It looks like some of the fields are supposed to be populated w/ info in a text d...

Tracked changes function misbehaving
I have a simple workbook (2002) in which the Track Changes feature is enabled. End users have reported periodic problems with the contents of cells "mysteriously" changing. In process of investigating these, have found that when tracked changes are highlighted, the comments that describe changes are not always associated with the correct cell. Example, comment on cell B114 states "Changed cell B114 from '<blank>' to '12345'". In fact, contents of cell B114 is "67890" and contents of cell B104 is actually "12345". Has anyone...

Macro looping?
How do I code a macro so that it loops until it goes through all of the information on the excel file? This way I do not have to rewrite the same code for 20 lines one day and then 30 lines the next day. sub loopit() lastrow=cells(rows.count,"a").end(xlup).row for i= 1 to lastrow cells(i,1)=?? next i end sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "klh84" <klh84@discussions.microsoft.com> wrote in message news:7E2B2C07-1D80-4D28-9D05-4BA024BF8669@microsoft.com... > How do I code a macro so that it loops...

Outlook macro question
I have two email accounts in Outlook 2003. I know about the Accounts drop list in new emails. But is it possible to create a few macros so that I end up with two buttons where each runs a macro that starts a new email using a specific account? One button would start a new email using account 1 and the other button would start a new email using account 2. If so, could someone please give me some pointers? Thanks. Outlook doesn't provide any direct way to change the account for an = outgoing message in versions before Outlook 2003. See=20 http://www.outlookcode.com/codedetail.aspx?id=3D88...

Adding a VCard to an e-mail using Outlook 2000 and Outlook 2003.
Hello. I have a question. Does anyone here know how to add a VCard to an e-mail in Outlook 2000 as well as in Outlook 2003? I want to be able to do this after I click new and I have a new blank message on my screen. Any and all help would be greatly appreciated. Thank you. -- darylakagod Outlook provides no way to add a vCard .vcf file in this scenario. The built-in way to do it is to start with a contact, not a message, and choose Forward as vCard on the contact's Actions menu. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Admin...

Adding Hyperlink to multiple values within a cell
My spreadsheet contains a list of people. The cell next to each nam contains multiple numeric values for identifying a specific piece o information. I would like to be able to click on one of those number (value) and a comment window pop up with the information associate with it, or be hyperlinked to the information further down th speadsheet. I want to avoid using multiple cells for this. Is this possible? Thank -- t2tru ----------------------------------------------------------------------- t2true's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=387 View this...

Adding additional Public Contact & Calendar Features
Any quick guide to adding additional Contact lists and public features to the public folders for separating to different clients/permissions? 2003 Mike Is there a better Exchange group to ask this? Mike "Michael Gregg" <mike@nospam.alyrica.net> wrote in message news:%23N1DuxJNEHA.556@TK2MSFTNGP10.phx.gbl... > Any quick guide to adding additional Contact lists and public features to > the public folders for separating to different clients/permissions? > > 2003 > > Mike > > ...

List of kernel mode compatible functions
Hi, Is there a list of kernel mode compatible C/C++ functions (preferably for WDK 7600)? Thanks, Jan Wouldn't that be the WDK Help file? Thomas F. Divine "Jan M" <jan_m@jerseymail.co.uk> wrote in message news:e7fhXcanKHA.1548@TK2MSFTNGP02.phx.gbl... > Hi, > > Is there a list of kernel mode compatible C/C++ functions (preferably for > WDK 7600)? > > Thanks, > > Jan > Your right of course. I just didnt look hard enough. Jan ...

Set Out of Office (or a rule) to only function on weekends
Hello, I am trying to set up so that every weekend my internal e-mail gets forwarded to my home(smartphone) account. I can only find options to run a rule or out of office assistant to run for specific date ranges. How do I set up either a rule or the out of office assistant to 'push' mail to me only on weekends? HCA.CWT.RN wrote on Fri, 02 April 2010 09:04 > Hello, I am trying to set up so that every weekend my internal e-mail gets > forwarded to my home(smartphone) account. I can only find options to run a > rule or out of office assistant to run for spec...

Adding BMPs to a form
I use Access 2003. I have a table set up with a field for pictures, this table sits behind a form to add BMPs, which display on the form and print on reports. In the past, when I have had to reinstall Access for any reason, the pictures instead of displaying fully in the form, only show as thumbnails with the file name. I have in the past resoved this by removing Microsoft Picture manager from MS Office. I don't know how I figured that out or why it works but it does. However, I had to reinstall Office yesterday, removed MS Pictrue Manager as usual and it's NOT working ...

adding holidays to a new calendar
I have set up a new calendar that I want to share with my department and I'm trying to add legal holidays which were already on my personal calendar. Even though I'm viewing the new calendar, the holidays got added to the personal calendar. I tried deleting all the holidays and then, with both calendars open, putting the holidays back in, but again they were added only to the personal calendar. Anyone know what to do? Add them to the personal calendar and copy (or move) them to the other calendar. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-t...

List Box no displaying added choices
I have several drop-down list boxes in a few forms that I am having trouble with. When I originally set up the database, I created the tables and entered a few items to be included in the drop down list in the lookup section of the table (design view). I then created the forms and inserted those fields. I have since added several new choices to the drop-down list of the table, and those new selections are available in the Table, but they are not available in the Form. The only items available in the drop down list on the Form are those choices that were originally there when I set up the fo...

Using a OR() like function in an IIF statement
Hello, I am trying to create an IIF statement to test if the first character in a field is a 1,2,8 or 9. Something like the following: IIf(Left([possible_SO_match],1)="1 or 2 or 8 or 9",[Possible_SO_Match],"No Match") Is there a way to create it without going to a 4 level nested IIF statement? Thanks, Kerry -- Message posted via http://www.accessmonster.com kkulakow via AccessMonster.com wrote: > Hello, I am trying to create an IIF statement to test if the first > character in a field is a 1,2,8 or 9. > Something like the following: > > IIf(Left([possib...

FORECASTING SALES (please let this be a worksheet function)
Hi I have recently been given the task by my boss to forecast one of ou key customers monthly usage until monthly until end of 2006, I am starting with this customers indidual branches monthly usage fo the past 3 years, Some branches will have opened and closed during thi time, And i am looking to be able to predict monthly usage for the nex 18 months Has anyone got any idea's on the best way to forcast within excel, I a currently using trend (fomulae given to me on this sight) but people ar saying this is not the best way Exponentionally has been mentioned by a few of my collegues but...

Adding User Name to footer
I have spreadsheets that go out to a number of users, and they make various changes and print out for themselves. The problems is that no-one knows which version to pick up from a central printer. Can I add the user name to the footer. I also have the path and file in the same area. I would like to end up with : User Name printed 'path/file' on 'date' ...

HELP! Record Macro in TOOLS > MACRO is gone!
I was in Customize function trying to enable the Stop Recording toolbar and now in TOOLS > MACRO the Record Macro option is gone. I'm working on a project and I need this back. Can someone help? I'm using Excel 2003. Thanks, Joe M. Try this: In the customize window > click the Options tab > push the reset toolbars & menus button. Exit excel and re-load it Micky "Joe M." wrote: > I was in Customize function trying to enable the Stop Recording toolbar and > now in TOOLS > MACRO the Record Macro option is gone. I'm working on a ...

macro warning
I have a workbook that consists of 15 worksheets. One for each month and then a summary page for the year plus a couple of worksheets containing charts. Every time I open it I'm prompted that the workbook contains macros. I haven't created any macros. I go to tools>macros and bring up the window no macros are shown. Anybody have any ideas why this is happening? Not really a show stopper but rather irritating. gls858 One possibility is that you need to delete any empty modules that may have housed macros. Hit ALT+F11 and this will open the VBE (Visual Basic Editor) Top left you wi...

macro to generate next number
Hi, i need a macro to look in sheet 2 column A and look at the last filled cell. In the last filled cell i have a number. When i run macro, i need to generate (in sheet1 A1) the number from the last filled cell +1. EX: sheet 2 last filled cell = 29 sheet1 after macro i need to have in A1 = 30 Can this be done? Thanks! Hi, Yuo can have a macro if you want but you don't need one, try this =OFFSET(Sheet2!A1,COUNTA(Sheet2!A:A)-1,0) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still suffici...