Try this
Sub FindBundleSize()
'Sheets("Run Report").Select

Dim X As Long, Y As Integer, Z As Long, W As Long

Let X = Cells(11, 3).Value 'Cell with Number of Pages in it
Let Y = 2600 'Max Pages per bundle

With Sheets("Books Per Bundle") 'Sheet with the data table in it
Z = 2 'First row of the table with
page sizes in it.
'Range("A1").Select
Do While True
If .Cells(Z, 1).Value = X Then Exit Do
Z = Z + 1
Loop 'Z = row number with page count in it

W = 2 'First column to start looking for 2600 in Row Z

Do While True
If .Cells(Z, W).Value <= Y And .Cells(Z, W + 1).Value Y Then
BooksPerBundle = .Cells(1, W)
'Sheets("Run Report").Select
'Range("C2").Select
'======
'set a range here
ActiveCell.FormulaR1C1 = BooksPerBundle
'=======
Exit Sub
End If
W = W + 1
Loop
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don M." wrote in message
...
One more little request. I'd like this macro to run without all of the
sheet
switching back and forth. I start out on the sheet called Run Report and
I'd
like the screen to stay there while all of these loopig is going on. Would
I
use the With/End With to do this? How would I change the code then to
make
sure the macro is searching the correct data?

Here's what I've got working now:

Sub FindBundleSize()
Sheets("Run Report").Select

Dim X As Long, Y As Integer, Z As Long, W As Long

Let X = Cells(11, 3).Value 'Cell with Number of Pages in
it
Let Y = 2600 'Max Pages per bundle

Sheets("Books Per Bundle").Select 'Sheet with the data table in it

Z = 2 'First row of the table with
page sizes in it.
'Range("A1").Select
Do While True
If Cells(Z, 1).Value = X Then Exit Do
Z = Z + 1
Loop 'Z = row number with page count in it

W = 2 'First column to start looking for 2600 in Row Z

Do While True
If Cells(Z, W).Value <= Y And Cells(Z, W + 1).Value Y Then
BooksPerBundle = Cells(1, W)
Sheets("Run Report").Select
Range("C2").Select
ActiveCell.FormulaR1C1 = BooksPerBundle
Exit Sub
End If
W = W + 1
Loop

End Sub