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