Using 'Find' and mvlookup or vlookup in a macro


New Member
Feb 3, 2005
I'm trying to do a few things with one macro.. your help is greatly appreciated.

Basically I think I need to find text in a column using "Find" and "mvlookup" but don't know how exactly how it should be written..

the below is a simplification of 3 columns in my main worksheet :

a answer1 date1
b answer2 date2
a,b answer3 date3
c,a answer4 date4

I need to search for "a" (in this case 3 solutions) and return columns B and C in Sheet2, row2, column A and B.

If possible, I'd like this to refresh when I open the file (I don't know how to do) and with a button (I do know how to do). I think i'll need to clear the area before before running the macro since the main table will constantly be updated - thus will need it to clear most of the worksheet (except heading in row 1) prior to the new macro results.

Ok, I did find some info from another listing that I tried to work from but am having problems trying to add the "find". Please See below.

Sub Results()
Dim RowNdx As Long
Dim prod As Range
Dim idplus As Range
Dim Rng As Range

RowNdx = 2


Set prod = Range(Range("a2"), Range("a2").End(xlDown))

Set idplus = Sheets("sheet2").Range("a1")

For Each Rng In prod
If Rng = idplus Then
Range(Cells(Rng.Row, 1), Cells(Rng.Row, 3)).Copy Sheets("Sheet2").Cells(RowNdx, 1)
RowNdx = RowNdx + 1
End If


Next Rng

End Sub

I'm not partial to the above but my skills in vba are very limited..
A BIG thank you to anyone with help!

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

The following assumes:

1) In cell E1 of sheet 1 you have the heading the same as A1.
2) In cell E2, you have your selection (in this case, a).
3) In cells A1 and A2 of sheet2, you have the headings from B1 and C1 of sheet1.

Range("A1:C" & Range("c65536").End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"E1:E2"), CopyToRange:=Sheets("sheet2").Range("a1:b1"), Unique:=False

If you want this to run when you first open the workbook, then you can put it into the workbook_open event. To do this, reduce the sheet window until you can see the blue bar with the sheet name. Right click and select the view code option. It should default to the workbook_open event. Put the code there.

Otherwise you can run the code from a normal macro as required.

Upvote 0
Hi Tony, Thanks for your help. I assume I use your code in place of my line:
If this is incorrect, please let me know. when i run it I'm getting an error that says "cant execute code in break mode" with the error going to the middle of your code =Range( _"E1:E2")

thanks again..
Upvote 0

To fix the error, remove the underscore. The code was split over 2 lines in the VBE and it has been confused with the wrapping on the board.

However, this is not to replace the line, it will replace the entire code. Put it into a sub on its own, making the changes to the sheet structure as I suggested, then run the code.

Upvote 0
Sorry to be difficult here. I tried what I thought you were saying about replacing the sub with your text. I'm fairly new to vba...can you tell be specifically all of what should be in the macro (on a basic level..). I must be doing something wrong that is on a basic level. I appreciate the help.
Upvote 0
Ok, it is not working.. It returns lines that start with "a" but not contain "a"; it returned rows 1 and 3 but not 4.
Upvote 0
using find


Instead of putting in a, use wildcards in your search. So the entry in E2 would be *a*.

Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back