Lookup set value, with multiple results.

[robbo0224]

Hi all,

I need help with a project i'm working on.
I have a sheet where I need to find and print worksheets based on weather that PPM is needed to be completed that month.
So for example, to Print all ppm's to be completed in week 6. I'd enter week 6 in the box and it looks up this week number in a row, then finds all occurrences of the number '1' in the column (there will be multiple) then look left to find the hyperlink to the sheet and print.

I need help with the lookup and looping it to find every entry.
Any help would be awesome. I'd attach the workbook, but i don't know how

 

[sheetspread]

Why not just filter?

 

[robbo0224]

The filter will only show the ones that need printing, i'm writing code that will print them automatically from a button click. So being able to use code to find the matches will tie in nicely. There are over 50 separate sheets, so printing manually takes a long time.

 

[robbo0224]

Here's a piece of the table, and some code I've come up with so far.
The idea is that the user would enter the week number in the input box, then look up the week (below example Week 1) find the 'X' showing that PPM is needs to be printed. Then use's the hyperlink in the Area column to go to that sheet, print and more on to next entry.

[TABLE="class: grid, width: 966"]
<tbody>[TR]
[TD]Ref:[/TD]
[TD]AREA[/TD]
[TD]Week 48[/TD]
[TD]Week 49[/TD]
[TD]Week 50[/TD]
[TD]Week 51[/TD]
[TD]Week 52[/TD]
[TD]Week 1[/TD]
[TD]Week 2[/TD]
[TD]Week 3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sifters[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PPMNPT1[/TD]
[TD]Silo 1[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PPMNPT2[/TD]
[TD]Silo 2[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PPMNPT3[/TD]
[TD]Silo 3[/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PPMNPT4[/TD]
[TD]Silo 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Flour Transfer[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PPMNPT6[/TD]
[TD]Rotary Sifter 1[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PPMNPT7[/TD]
[TD]Rotary Sifter 2[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PPMNPT8[/TD]
[TD]Rotary Sifter 3[/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PPMNPT9[/TD]
[TD]Rotary Sifter 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Mixing Area[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sub test1()    Dim PPMSchd As Range
    Dim Rng As Range
    Dim Lookup As String
    Dim Week As Integer
    
    Set PPMSchd = Sheets("Schedule").Range("A4:AC123")
    
    Week = InputBox("Please enter Week number:")
    Lookup = "Week " & Week
    
    Set Rng = PPMSchd.Find(Lookup)
    If Rng Is Nothing Then
        Msgbox Lookup & """ wasn't found.", vbInformation, "Invalid search"
    Else
        Rng.Find ("X")
        
        
    
End Sub

As you can see the code isn't finished as at this point i'm stuck.

 

[sheetspread]

One way is to return the column of Week 1 via the Match function, loop through each row, wherever an X appears print the corresponding Area.

 

[robbo0224]

Ok, the match function i can do but how do i loop though each row of that column? an find the area? would it be an embedded hlookup?

 

[sheetspread]

Something like:

LastRow = Cells(Rows.Count, 1).End(xlUp).Row 'change 1 to whichever column is Area

for i = 3 to LastRow 'change 3 to the first row #

if cells(i,WeekColumn) = "X" then sheets(cells(i,1).value).printout 'change 1 to whichever column is Area

 

[robbo0224]

Thank you, I've gotten a step closer but i can't end the 'for' statement. Here's my code so for;

Sub test1()    Dim PPMSchd As Range
    Dim Rng As Range
    Dim Lookup As String
    Dim Week As Integer
    
    Set PPMSchd = Sheets("Schedule").Range("A4:AC123")
    
    Week = InputBox("Please enter Week number:")
    Lookup = "Week " & Week
    WeekColumn = PPMSchd.Find(Lookup)
    LastRow = Cells(Rows.Count, 2).End(xlUp).Row
    For i = 6 To LastRow
    
    If Cells(i, WeekColumn) = "X" Then Sheets(Cells(i, 2).Hyperlinks(1).Follow).PrintOut
             
    Next
    Msgbox = ("Print Complete")
    
End Sub

 

[sheetspread]

Try an end if after the if statement

 

[sheetspread]

See if you can alter the ranges here to fit your data:

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	Ref:	AREA	Week 48	Week 49	Week 50	Week 51	Week 52	Week 1	Week 2	Week 3
2		Sifters
3	PPMNPT1	Silo 1	X				X
4	PPMNPT2	Silo 2		X				X
5	PPMNPT3	Silo 3			X				X
6	PPMNPT4	Silo 4				X				X
7		Flour Transfer
8	PPMNPT6	Rotary Sifter 1	X				X
9	PPMNPT7	Rotary Sifter 2		X				X
10	PPMNPT8	Rotary Sifter 3			X				X
11	PPMNPT9	Rotary Sifter 4				X				X
12		Mixing Area
Sheet2

Sub printhyperlinks()
Dim Week%, Col%, LastRow%
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
Week = InputBox("Please enter Week number:")
Col = Application.Match("Week " & Week, Range("C1:J1"), 0) + 2
For i = 1 To LastRow
If Cells(i, Col).Value = "X" Then
Sheets(Cells(i, 2).Value).PrintOut
End If
Next
End Sub