Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i use find in VB
Dear sir,
I am trying to use VB to find data, not in the same worksheet but in whole workbook. I programed in VB as the following: Sub Button1_Click() Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate End Sub Well, it works to find data at the active worksheet but I need to find data at different worksheets(all workbook) I tried to use Xlsearchwithin.Xlwithinworkbook but it keeps giveing an error. Please could you help me. Yours Taufik |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i use find in VB
You can use somethjing like this to get every worksheet.
For Each MyWorksheet In Workbooks(Summary_Spreadsheet).Worksheets If StrComp(PLOTDATAWORKSHEETNAME, MyWorksheet.Name) = 0 Then SheetExist = True Exit For End If Next MyWorksheet "tofy" wrote: Dear sir, I am trying to use VB to find data, not in the same worksheet but in whole workbook. I programed in VB as the following: Sub Button1_Click() Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate End Sub Well, it works to find data at the active worksheet but I need to find data at different worksheets(all workbook) I tried to use Xlsearchwithin.Xlwithinworkbook but it keeps giveing an error. Please could you help me. Yours Taufik |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i use find in VB
Sub Button1_Click()
Dim sh as Worksheet Dim rng as Range Dim s as String s = "*" for each sh in worksheets set rng = sh.Cells.Find(What:=s, _ After:=sh.Range("IV65536"), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) if not rng is nothing then application.Goto rng, true msgbox "Found at: " & rng.Address(0,0,xlA1,true) end if Next End sub -- Regards, Tom Ogilvy "tofy" wrote: Dear sir, I am trying to use VB to find data, not in the same worksheet but in whole workbook. I programed in VB as the following: Sub Button1_Click() Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate End Sub Well, it works to find data at the active worksheet but I need to find data at different worksheets(all workbook) I tried to use Xlsearchwithin.Xlwithinworkbook but it keeps giveing an error. Please could you help me. Yours Taufik |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i use find in VB
from a post by Julian Milano
Sub SearchBook() Found = False what = InputBox("What do you want to find? ") ' Ask what the user wants to find For Each ws In Worksheets Set foundcell = ws.Columns.Find(what) ' Go thru each worksheet and find 'what' If Not (foundcell Is Nothing) Then 'I found it! Found = True MsgBox "The word was found in cell " & ws.Name & foundcell.Address ' Show me! ws.Activate ' Activate the sheet it was found on.... ws.Range(foundcell.Address).Select ' And go to that cell End If Next ws If Not Found Then MsgBox "The word was not found" Else End If End Sub -- Don Guillett SalesAid Software "tofy" wrote in message ... Dear sir, I am trying to use VB to find data, not in the same worksheet but in whole workbook. I programed in VB as the following: Sub Button1_Click() Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate End Sub Well, it works to find data at the active worksheet but I need to find data at different worksheets(all workbook) I tried to use Xlsearchwithin.Xlwithinworkbook but it keeps giveing an error. Please could you help me. Yours Taufik |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find First Non blank cell than find column header and return that value | Excel Worksheet Functions | |||
Despite data existing in Excel 2002 spreadsheet Find doesn't find | Excel Discussion (Misc queries) | |||
find and delete duplicate entries in two columns or find and prin. | Excel Programming | |||
find and delete text, find a 10-digit number and put it in a textbox | Excel Programming | |||
backwards find function to find character in a string of text | Excel Programming |