Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
VLOOKUP Function | Excel Discussion (Misc queries) | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
Array Function with VLOOKUP | Excel Worksheet Functions | |||
Function Macro for Nested IF | Excel Worksheet Functions |