Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specifying the Sheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specifying the Sheet
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 "kirkm" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specifying the Sheet
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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specifying the Sheet
On Sun, 21 Oct 2007 13:22:05 +1300, kirkm wrote:
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specifying the Sheet
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("A1:D200").Find(What:=Format(ChosenDa te, "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(C hosenDate, "dd mmm yyyy") hth, Doug "kirkm" wrote in message ... On Sun, 21 Oct 2007 13:22:05 +1300, kirkm wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specifying the Sheet
On Sat, 20 Oct 2007 20:47:03 -0700, "Doug Glancy"
wrote: 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("A1:D200").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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specifying the Sheet
On Sun, 21 Oct 2007 18:21:36 +1300, kirkm wrote:
Hi Doug, After much playing about ... all sorted ! Many thanks Cheers - Kirk |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel sheet bootom half sheet goes behind top part of sheet | Excel Worksheet Functions | |||
Duplicate sheet, autonumber sheet, record data on another sheet | Excel Worksheet Functions | |||
Export sheet store sheet import sheet. | Excel Programming | |||
relative sheet references ala sheet(-1)!B11 so I can copy a sheet. | Excel Discussion (Misc queries) | |||
Inserting a row in sheet A should Insert a row in sheet B, removing a row in Sheet A should remove the corresponding row in sheet B | Excel Programming |