VBA for multiple VLOOKUP/Searches

[ilyas9791]

I am a beginner to VBA and looking for some experts help here.
I am basically looking at writing VBA code to do multiple filter and provide final results.
I have following data in my Main Sheet

Date Week CalendarRun14-Oct 1 Run114-Oct 1 Run214-Oct 1 Run314-Oct 2 Run414-Oct 2 Run221-Oct 3 Run121-Oct 3 Run221-Oct 4 Run4</pre>For e.g. let say If Today is 14th Oct which may fall in week 1 then it should filter out on all the matches from "CalendarRun" column for Week 1. And based on this multiple matches it should further filter or search for the data in next sheet with data as follows -

CalendarRun DataRun1 Data1Run2 Data2Run2 Data3Run3 Data4Run3 Data5Run3 Data6Run4 Data7</pre>so based on the earlier result from "CalendarRun" Column, it should further filter all the data that matches it and could be multiple result. And this is the final result i am looking for.
Pls let me know if my query is unclear.
Many thanks,

 

[RickXL]

I have to say that your question was not completely clear. However, this is what I made of it. Perhaps we can now iterate to a solution.

I think your data looks like this:

Excel 2010

	A	B	C	D
1	Date	Week	CalendarRun	Showing
2	14-Oct	1	Run1	Run1
3	14-Oct	1	Run2	Run2
4	14-Oct	1	Run3	Run3
5	14-Oct	2	Run4	Run4
6	14-Oct	2	Run2	Run2
7	21-Oct	3	Run1	Run1
8	21-Oct	3	Run2	Run2
9	21-Oct	3	Run4	Run4

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Excel 2010

	A	B	C
1	CalendarRun	Data	Filter
2	Run1	Data1	1
3	Run2	Data2	2
4	Run2	Data3	2
5	Run3	Data4	3
6	Run3	Data5	3
7	Run3	Data6	3
8	Run4	Data7	4

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

I calculated a column D on the first sheet that contained the run id e.g. Run1 etc. The formula makes this value blank if that row is AutoFiltered. I then run a MATCH on sheet2 with the run id there and the filtered list in sheet1. Because the AutoFilter does not reapply itself in Sheet2 I have added a macro to reapply it when the sheet is activated.

The formula in Sheet1!D2 is: =IF(SUBTOTAL(3,Sheet1!A2)=1,Sheet1!$C$2:$C$9,"")

The formula in Sheet2!C2 is: =MATCH(A2,Sheet1!$D$2:$D$9,0)

The macro for Sheet2 is:

Private Sub Worksheet_Activate()
    With ActiveSheet.UsedRange
        .AutoFilter Field:=3, Criteria1:=">0"
    End With
End Sub

What does it do?
When you use the AutoFilter on Sheet1 you end up with some run ids showing. Sheet2 is then automatically filtered on these run ids when you activate it.

Even if it is not what you want I had fun doing it

 

[ilyas9791]

I have to say that your question was not completely clear. However, this is what I made of it. Perhaps we can now iterate to a solution.

I think your data looks like this:

Excel 2010

	A	B	C	D
1	Date	Week	CalendarRun	Showing
2	14-Oct	1	Run1	Run1
3	14-Oct	1	Run2	Run2
4	14-Oct	1	Run3	Run3
5	14-Oct	2	Run4	Run4
6	14-Oct	2	Run2	Run2
7	21-Oct	3	Run1	Run1
8	21-Oct	3	Run2	Run2
9	21-Oct	3	Run4	Run4

<TBODY>
</TBODY>

Sheet1​

Excel 2010

	A	B	C
1	CalendarRun	Data	Filter
2	Run1	Data1	1
3	Run2	Data2	2
4	Run2	Data3	2
5	Run3	Data4	3
6	Run3	Data5	3
7	Run3	Data6	3
8	Run4	Data7	4

<TBODY>
</TBODY>

Sheet2​

I calculated a column D on the first sheet that contained the run id e.g. Run1 etc. The formula makes this value blank if that row is AutoFiltered. I then run a MATCH on sheet2 with the run id there and the filtered list in sheet1. Because the AutoFilter does not reapply itself in Sheet2 I have added a macro to reapply it when the sheet is activated.

The formula in Sheet1!D2 is: =IF(SUBTOTAL(3,Sheet1!A2)=1,Sheet1!$C$2:$C$9,"")

The formula in Sheet2!C2 is: =MATCH(A2,Sheet1!$D$2:$D$9,0)

The macro for Sheet2 is:

Private Sub Worksheet_Activate()
    With ActiveSheet.UsedRange
        .AutoFilter Field:=3, Criteria1:=">0"
    End With
End Sub

What does it do?
When you use the AutoFilter on Sheet1 you end up with some run ids showing. Sheet2 is then automatically filtered on these run ids when you activate it.

Even if it is not what you want I had fun doing it

Hi Rick,

Sorry for not being very clear with my query. However, what you advised does help to a great extent, but it would still need some manual intervention. Can you also pls help to put this all into VBA, such as i just need to put the weeknum and everything gets filtered and the final output is in a seperate sheet. I am trying to setup an auto notification that does the autofilter and then sends the final output to the recipients with no manual intervention.

After lot of search and some trials I tried to put very crude code for my requirement as below -

Sub FilterCalendarData()

Dim LastRowInA As Long
Dim LastRowInB As Long
Dim LastRowInC As Long
Dim LngMyRow As Long
Dim FindString As String
Dim rnSearch As Range, rnLookup As Range, rnTemp As Range
Dim varArray As Variant
Dim lnIndex As Long
Dim strTemp As String


Sheets("Summary").Select 'This sheet will have all the filtered and matched final data

If ActiveSheet.Name = "Summary" Then
ActiveSheet.Name = "Summary_" & Format(Date, "dd-mm-yy") & "@" & Format(Time, "hhmm")
End If

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Summary"
Cells.Select

LastRowInA = Range("A" & Rows.Count).End(xlUp).Row
LastRowInC = Range("C" & Rows.Count).End(xlUp).Row
LngMyRow = Cells(Rows.Count, "A").End(xlUp).Row

FindString = Worksheets("Main").Range("A1").Value 'Here I put the weeknum to filter the calendar

Sheets("Calendar").Select

Dim sh As Worksheet
Dim rng As Range

Set sh = ActiveSheet

sh.AutoFilterMode = False

sh.Range("$A:$C").AutoFilter Field:=2, Criteria1:=FindString, Operator:=xlOr
LastRowInB = Range("B" & Rows.Count).End(xlUp).Row
If Application.WorksheetFunction.Subtotal(3, ActiveSheet.Columns(1)) > 1 Then

Range("C2:C" & LastRowInB).Select
Selection.Copy
Sheets("Summary").Select
Range("A1").Select
ActiveSheet.Paste

End If

LastRowInA = Range("A" & Rows.Count).End(xlUp).Row

Set rnSearch = Worksheets("Summary").Range("A1:A" & LastRowInA)
Set rnLookup = Worksheets("Data").Range("A1:B334") 'Set this to your lookup range (assume 2 columns)
varArray = rnLookup
For Each rnTemp In rnSearch
For lnIndex = LBound(varArray, 1) To UBound(varArray, 1)
strTemp = rnTemp.Value
If varArray(lnIndex, 1) = strTemp Then
If WorksheetFunction.CountIf(rnTemp.EntireRow, varArray(lnIndex, 2)) = 0 Then 'Check if value exists already
Worksheets("Summary").Cells(rnTemp.Row, rnTemp.EntireRow.Columns.Count).End(xlToLeft).Offset(0, 1).Value = varArray(lnIndex, 2)
End If
End If
Next
Next

End Sub

This does some work i expect but not exactly. It filters the data based on the weeknum i enter and then search for that relevant week data. However the last search it does for the week, it paste's the data in horizontal along side the item it searches for. Here's where i want to improve the search and would like it paste result vertically one after the other result it gets for each item it searches against.​

Many thanks in advance.