Using 'Find' and mvlookup or vlookup in a macro

[Bajr]

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

Sheets("sheet2").Select
Range("A2:E5").Select
Selection.Clear

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

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

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

Sheets("sheet2").Select
Range("A1").Select


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!
BAJ

 

[acw]

Hi

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.


Tony

 

[Bajr]

Hi Tony, Thanks for your help. I assume I use your code in place of my line:
Range(Cells(Rng.Row,1)....(RowNdx,1)
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..
BAJ

 

[acw]

Hi

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.


Tony

 

[Bajr]

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.

 

[Bajr]

It's working now... sorry!
Thanks a bunch for your help _ BAJ

 

[Bajr]

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.

 

[acw]

using find

Hi

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

Tony