Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Find filling a listbox vba
Hi,
I've got a simple problem (I think) I would like to search within a selection using the find method. Then transfer the found cells to a listbox. The problem is that the code I'm using loops a "few times to many". Does anyone know what's wrong with it? Here's the code Private Sub CommandButton2_Click() Dim sh As Worksheet Set sh = ThisWorkbook.Worksheets("sheet1") sh.UsedRange.Activate cntend = LastRow(sh) Selection.Find(What:="cash", After:=ActiveCell, LookIn:=xlValues, Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False).Activate ActiveCell.Select counter = 1 Do While counter < cntend Cells.FindNext(After:=ActiveCell).Activate ListBox1.AddItem ActiveCell.Value counter = counter + 1 Loop End Sub Help Appreciated, Farmer |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Find filling a listbox vba
hi,
instead of using "cntend = LastRow(sh)" try this instead... lastrow = cells(rows.count,1).End(xlup).row -----Original Message----- Hi, I've got a simple problem (I think) I would like to search within a selection using the find method. Then transfer the found cells to a listbox. The problem is that the code I'm using loops a "few times to many". Does anyone know what's wrong with it? Here's the code Private Sub CommandButton2_Click() Dim sh As Worksheet Set sh = ThisWorkbook.Worksheets("sheet1") sh.UsedRange.Activate cntend = LastRow(sh) Selection.Find(What:="cash", After:=ActiveCell, LookIn:=xlValues, Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False).Activate ActiveCell.Select counter = 1 Do While counter < cntend Cells.FindNext(After:=ActiveCell).Activate ListBox1.AddItem ActiveCell.Value counter = counter + 1 Loop End Sub Help Appreciated, Farmer . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Find filling a listbox vba
On Mon, 11 Oct 2004 08:52:18 -0700,
wrote: Hi, Thanks for your reply but it's still not working Farmer hi, instead of using "cntend = LastRow(sh)" try this instead... lastrow = cells(rows.count,1).End(xlup).row -----Original Message----- Hi, I've got a simple problem (I think) I would like to search within a selection using the find method. Then transfer the found cells to a listbox. The problem is that the code I'm using loops a "few times to many". Does anyone know what's wrong with it? Here's the code Private Sub CommandButton2_Click() Dim sh As Worksheet Set sh = ThisWorkbook.Worksheets("sheet1") sh.UsedRange.Activate cntend = LastRow(sh) Selection.Find(What:="cash", After:=ActiveCell, LookIn:=xlValues, Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False).Activate ActiveCell.Select counter = 1 Do While counter < cntend Cells.FindNext(After:=ActiveCell).Activate ListBox1.AddItem ActiveCell.Value counter = counter + 1 Loop End Sub Help Appreciated, Farmer . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Find filling a listbox vba
There's a nice example in VBA's help for .find that shows how to search until
you loop around to the first found cell. Option Explicit Private Sub CommandButton2_Click() Dim sh As Worksheet Dim FoundCell As Range Dim FirstAddress As String Dim Counter As Long Set sh = ThisWorkbook.Worksheets("sheet1") Counter = 0 Me.ListBox1.Clear With sh.Range("a:a") 'what was selected?? Set FoundCell = .Find(What:="cash", After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ Lookat:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=True, SearchFormat:=False) If FoundCell Is Nothing Then 'not found on the sheet Else FirstAddress = FoundCell.Address Do Me.ListBox1.AddItem FoundCell.Value Counter = Counter + 1 Set FoundCell = .FindNext(FoundCell) Loop While Not FoundCell Is Nothing _ And FoundCell.Address < FirstAddress End If End With End Sub I'm not sure what Counter was used for and I figured the listbox was on the same worksheet as the commandbutton2. And watchout. SearchFormat was added in xl2002. If you share your workbook with people who use xl2k or before, remove this portion. farmer wrote: Hi, I've got a simple problem (I think) I would like to search within a selection using the find method. Then transfer the found cells to a listbox. The problem is that the code I'm using loops a "few times to many". Does anyone know what's wrong with it? Here's the code Private Sub CommandButton2_Click() Dim sh As Worksheet Set sh = ThisWorkbook.Worksheets("sheet1") sh.UsedRange.Activate cntend = LastRow(sh) Selection.Find(What:="cash", After:=ActiveCell, LookIn:=xlValues, Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False).Activate ActiveCell.Select counter = 1 Do While counter < cntend Cells.FindNext(After:=ActiveCell).Activate ListBox1.AddItem ActiveCell.Value counter = counter + 1 Loop End Sub Help Appreciated, Farmer -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Find filling a listbox vba
Hi,
Thanks a lot, Its just what I wanted Greetings, Farmer On Mon, 11 Oct 2004 19:38:30 -0500, Dave Peterson wrote: There's a nice example in VBA's help for .find that shows how to search until you loop around to the first found cell. Option Explicit Private Sub CommandButton2_Click() Dim sh As Worksheet Dim FoundCell As Range Dim FirstAddress As String Dim Counter As Long Set sh = ThisWorkbook.Worksheets("sheet1") Counter = 0 Me.ListBox1.Clear With sh.Range("a:a") 'what was selected?? Set FoundCell = .Find(What:="cash", After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ Lookat:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=True, SearchFormat:=False) If FoundCell Is Nothing Then 'not found on the sheet Else FirstAddress = FoundCell.Address Do Me.ListBox1.AddItem FoundCell.Value Counter = Counter + 1 Set FoundCell = .FindNext(FoundCell) Loop While Not FoundCell Is Nothing _ And FoundCell.Address < FirstAddress End If End With End Sub I'm not sure what Counter was used for and I figured the listbox was on the same worksheet as the commandbutton2. And watchout. SearchFormat was added in xl2002. If you share your workbook with people who use xl2k or before, remove this portion. farmer wrote: Hi, I've got a simple problem (I think) I would like to search within a selection using the find method. Then transfer the found cells to a listbox. The problem is that the code I'm using loops a "few times to many". Does anyone know what's wrong with it? Here's the code Private Sub CommandButton2_Click() Dim sh As Worksheet Set sh = ThisWorkbook.Worksheets("sheet1") sh.UsedRange.Activate cntend = LastRow(sh) Selection.Find(What:="cash", After:=ActiveCell, LookIn:=xlValues, Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False).Activate ActiveCell.Select counter = 1 Do While counter < cntend Cells.FindNext(After:=ActiveCell).Activate ListBox1.AddItem ActiveCell.Value counter = counter + 1 Loop End Sub Help Appreciated, Farmer |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
problem filling in blank cells with data above - | Excel Discussion (Misc queries) | |||
Where do I find a template for filling out 1099-MISC forms? | Excel Discussion (Misc queries) | |||
Filling down problem in VB | Excel Programming | |||
filling a two column listbox from a two column recordset | Excel Programming | |||
Filling a listbox depending on font index | Excel Programming |