Solved: Find

[Alvinkiang]

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]

I may be out of school here but you might try something like:
[VBA]
Set findit = .Range("D").Find(what:=TextBox1.Text)(lookat:=xlWhole)
[/VBA]Not sure whether to use xlwhole or xlpart for your purpose.

[Alvinkiang]

I may be out of school here but you might try something like:
[vba]
Set findit = .Range("D").Find(what:=TextBox1.Text)(lookat:=xlWhole)
[/vba]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:

[vba]
Set findit = .Range("D").Find(what:=TextBox1.Text, lookat:=xlWhole)
[/vba]
thanks for your help

[lucas]

Sorry, glad you got it worked out...

[Alvinkiang]

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]

any chance you could post the workbook?