Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Seaching an excel sheet
I currently am using the following code to search my ecel sheet
Dim Prompt As String Dim UserResp As String Dim UR As Single Dim i As Integer Dim MyText As String, List, myTotal As Double Dim Cell As Range Set cmt = ActiveSheet.Comments Set rng21 = Range("target") Selection.SpecialCells(xlCellTypeComments).Select For Each Cell In Selection Cell.Select myTotal = 0 MyText = UCase$(Cell.Comment.Text) If InStr(MyText, UCase$(rng21)) Then Cells(Cell.Row, 15).Value = rng21 Cells(Cell.Row, 16).Value = Cell.Address End If Next Cell I would like to be able to input the search data in a TEXTbox and do the same search. Can anyone help with this Thanks and Happy New year |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Seaching an excel sheet
pcorcele wrote:
I currently am using the following code to search my ecel sheet [snip] I would like to be able to input the search data in a TEXTbox and do the same search. Can anyone help with this Put this in the sheet's class module and change "TextBox1" to the name of the textbox: Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _ ByVal Shift As Integer) If 13 = KeyCode Then Dim UserResp As String Dim MyText As String Dim Cell As Range UserResp = TextBox1.Text Selection.SpecialCells(xlCellTypeComments).Select For Each Cell In Selection MyText = UCase$(Cell.Comment.Text) If InStr(MyText, UCase$(UserResp)) Then Cells(Cell.Row, 15).Value = UserResp Cells(Cell.Row, 16).Value = Cell.Address End If Next Cell End If End Sub (This is mostly just your code.) If you want the textbox cleared after every search, add this immediately before "End If": TextBox1.Text = "" -- Mr. Notlob, there's nothing wrong with you that an expensive operation can't prolong. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seaching Ex cel for text styles | Excel Programming | |||
Seaching a range | Excel Discussion (Misc queries) | |||
Seaching a Word Document from Excel Part ii | Excel Programming | |||
Seaching with no results | Excel Discussion (Misc queries) | |||
Seaching across the top | Excel Programming |