Adding a Macro to a VLookup Function

[Guest]

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.

 

[Guest]

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