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
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