Specifying the Sheet

[kirkm]

Hi, in the following -

Dim foundCell As Range
Set foundCell = Selection.Find(What:=Format(ChosenDate, "dd mmm
yyyy"), _
After:=Cells(rows.Count, 1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

whereabouts would you specify the Sheet Name ?

And does the command

foundCell.Select

Move to a different sheet, if that's where foundCell is ?

Many thanks - Kirk

 

[FSt1]

hi
unless otherwise specified, the find assumes you are searching the active
sheet. if you wish to search other or all sheets do this....
Sub mac1FindAll()
Dim c As String
Dim sh As Worksheet
Dim rng As Range
c = InputBox("Enter item to search for")
For Each sh In ActiveWorkbook.Worksheets
If c <> "" Then
Set rng = Nothing
Set rng = sh.Range("A1:IV65000").Find(what:=c, _
After:=sh.Range("A1"), _
LookIn:=xlFormulas, _
lookat:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End If
If Not rng Is Nothing Then
sh.Activate
rng.Select
MsgBox "Found on sheet " & sh.Name & " at cell " & _
rng.Address

regards
FSt1

 

[JLGWhiz]

To specifically answer your questions:
1. Fst1 is correct that the ActiveSheet is assumed by default in VBA. If
you want to specify another sheet, it must be done either by assigning a
variable to the Workbook("wbName").Sheets("shName") and then use the variable
where you now have Selection in the Find statement.
2. FoundCell.Select, if found, will place the cursor (cell highlight) on
the cell that was found and should be displayed on screen. However, you must
have first declared FoundCell as a range. Otherwise, you will get an error
message.

I suggest you open the VBA editor (Alt + F11) and use VBA help file to
review the Find Method. Just click help and type "Find Method" in the search
box.

 

[kirkm]

Thanks for the answers but sorry I couldn't get
what I wanted from either.

I know the sheet name, that piece of code I pasted currently works
on the active sheet but I may (depending on user input)
want to specify a differnet sheet.

Cheers - Kirk

 

[Doug Glancy]

Kirk,

Find takes as it's first argument a range. In the case of your code that
range is the Selection. Selection only applies to the active sheet. What
your other two answers are saying is that in order to to work on a sheet
other than the activesheet, you'll have to specify the sheet and the range.
So instead of

Set foundCell = Selection.Find(What:=Format(ChosenDate, "dd mmm yyyy")

you'd have something like:

Set foundCell = Worksheets("Sheet
2").Range("A1200").Find(What:=Format(ChosenDate, "dd mmm yyyy")

If you want to search the same cells as the current Selection but on a sheet
other than the Activesheet, I think you could do this:

Dim strSelectionAddress As String
strSelectionAddress = Selection.Address
Set foundCell = Worksheets("Sheet
2").Range(strSelectionAddress).Find(What:=Format(ChosenDate, "dd mmm yyyy")

hth,

Doug

 

[kirkm]

Kirk,

Find takes as it's first argument a range. In the case of your code that
range is the Selection. Selection only applies to the active sheet. What
your other two answers are saying is that in order to to work on a sheet
other than the activesheet, you'll have to specify the sheet and the range.
So instead of

Set foundCell = Selection.Find(What:=Format(ChosenDate, "dd mmm yyyy")

you'd have something like:

Set foundCell = Worksheets("Sheet
2").Range("A1200").Find(What:=Format(ChosenDate, "dd mmm yyyy")

If you want to search the same cells as the current Selection but on a sheet
other than the Activesheet, I think you could do this:

Dim strSelectionAddress As String
strSelectionAddress = Selection.Address
Set foundCell = Worksheets("Sheet
2").Range(strSelectionAddress).Find(What:=Format(ChosenDate, "dd mmm yyyy")

Thanks Doug, I'll have a play with that. I was initially trying to
make code that worked on the active sheet, also work on two other
sheets, depending on the date entered. Each sheet covers a decade.

I'm using Isdate and checking it's within my range. (LOL that's not
Excels range which I don't really understand) . If the date
doesn't find an absolute match, I calculate the nearest absolute Date
which might be another decade, meaning another sheet.

When you say "Selection only applies to the active sheet." possibly I
could use the code I have after figuring out how to make another sheet
the 'active sheet'.

Thanks -Kirk

 

[kirkm]

Hi Doug,

After much playing about ... all sorted !

Many thanks

Cheers - Kirk