PDA

View Full Version : Solved: Find



Alvinkiang
07-02-2006, 06:19 PM
my Current Program, thanks to ACW is working but has a bug... it was suppose to find what it has in a textbox and it does, but it find all the word that contain the letter... if i type SC in the textbox.. it will find all word that contain Sc... Is there anyway i can edit so that it only display SC item?



With Workbooks("XXX.xls").Sheets("JUN06")
.Activate

Set findit = .Range("D:D").Find(what:=TextBox1.Text)
If Not findit Is Nothing Then
firstadd = findit.Address
ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = findit
ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Value = findit.Offset(0, 4).Value
ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).Value = findit.Offset(0, 3).Value

i = i + findit.Offset(0, 3).Value
Set findit = .Range("D:D").FindNext(findit)

While findit.Address <> firstadd
ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = findit
ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Value = findit.Offset(0, 4).Value
ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).Value = findit.Offset(0, 3).Value
i = i + findit.Offset(0, 3).Value
Set findit = .Range("D:D").FindNext(findit)
Wend
End If
With Workbooks("XXX.xls").Sheets("sheet1")
.Activate
'ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = findit
' ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(0, 1).Value = i
TextBox1.Text = ""
TextBox1.SetFocus
End With


End With

lucas
07-02-2006, 07:18 PM
I may be out of school here but you might try something like:

Set findit = .Range("D:D").Find(what:=TextBox1.Text)(lookat:=xlWhole)
Not sure whether to use xlwhole or xlpart for your purpose.

Alvinkiang
07-02-2006, 07:31 PM
I may be out of school here but you might try something like:

Set findit = .Range("D:D").Find(what:=TextBox1.Text)(lookat:=xlWhole)
Not sure whether to use xlwhole or xlpart for your purpose.

thanks lucus, i managed to get it done with Xlwhole But is in this format:


Set findit = .Range("D:D").Find(what:=TextBox1.Text, lookat:=xlWhole)

thanks for your help

lucas
07-02-2006, 08:30 PM
Sorry, glad you got it worked out...

Alvinkiang
07-02-2006, 08:35 PM
no, i must thanks you for your help instead..
i do have another question i think you can help me.. regarding the code i have post jus now... if i have found the relevant item i keyed.. the next column item will be taken out as well...

now the Problem is.. if it is empty, when i key another item to search for.. it does not goes the next row to insert the value.. it will insert into the Empty cell in the above row.
and i end up having a excel which looks ok but with value flying everywhere

lucas
07-02-2006, 08:40 PM
any chance you could post the workbook?