What am I missing?

[wroze27]

Can someone please explain to me what I did wrong/ what is missing here. I need this code to do a couple things. First search for a worksheet using variable 'Page' which is a cell on sheet1. Each Sheet is named 1-80 and the cell it references may be any one of those numbers.
Next I need it to search in a certain range for another variable. That variable, Sun, can also be found on sheet 1.

I was able to write (Sheets(Page).Select) and it would work. And (ActiveSheet.Range("A1:B5").Find(What:=Sun).Offset(0, 1).Select) would also work.
Why do they not work when combined?

Thanks guys

Dim Page As String
Page = Sheet1.Range("A4")
Dim Sun As String
Sun = Sheet1.Range("B1")

Sheets(Page).Range("A1:B5").Find(What:=Sun).Offset(0, 1).Select

 

[Rick Rothstein]

You are probably being tripped up by the Find method. Find has several options and it remembers the setting for them from the last time they were set either in previously run VBA code OR as set in the Find dialog box itself. It is best to provide values for those settings when you call Find so you know what Find is trying to find. For example, if the last time you used Find, you set it to look to match the entire cell's content within the searched range, it remembers that so the next time you use Find (either in VBA code or the dialog box), it will look to match what you are searching for in the entire cell's content within the searched range even if you wanted to find the searched for text in part of the cell's content.

 

[Fluff]

Along with Rick's comments, you can only select a cell on the active sheet. Try it like this

Dim Page As String
Page = Sheet1.Range("A4")
Dim Sun As String
Sun = Sheet1.Range("B1")
Dim Fnd As Range
Set Fnd = Sheets(Page).Range("A1:B5").Find(What:=Sun).Offset(0, 1)
If Not Fnd Is Nothing Then Application.Goto Fnd