• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to Find

Nipendra.dhiman

Nipendra.dhiman

Member
Hi experts,

let suppose i have a database in excel sheet1 and a userform with in the same spreadsheet, i have to update database every day now it’s a very heavy amount of data.
the challenge is i am getting whenever i am going to enter some new values it gets duplicate some time because of repeated entry. so i have decided to use a vba form to search then save.
"
Private Sub CommandButton1_Click()
Dim i As Integer, ValueToFind As String
Dim copyrng As String
ValueToFind = TextBox1.Text
For i = 1 To 2539
If Cells(i, 1).Value = ValueToFind Then

Sheet1.Activate
copyrng = "A" & i & ":" & "H" & i
ListBox1.RowSource = Range(copyrng).Address
MsgBox ("Found value on row No. " & i)

Exit Sub
End If
Next i

MsgBox ("Value not found!")
End Sub


currently i am going with this code but here i am getting some challenges because this is trying to find whole string and need to find like CTRL+F.
Example if i am finding "nipendra" and values exist in database then i am getting the same but when i am trying to find "nip" ro "nipend" then it’s not working.
this code need to work like CTRL+F does work.

Any help or suggestion
thanks in advanced
Nipendra
 
Hi Nipendra,
instead of
If Cells(i, 1).Value = ValueToFind Then
Click to expand...
use.. below line
Code: 
If Cells(i, ).Value Like "*" & ValueToFind & "*" Then

PS: Isn't it looks nice and easy to read.. with a [Code=vb]... [/code]
please use Code tag for code section..
 
Code: 
Private Sub CommandButton1_Click()
Dim i As Integer, ValueToFind As String
Dim copyrng As String
ValueToFind = UCase(TextBox1.Text)
For i = 1 To 2539
If UCase(Cells(i, 1).Value) Like "*" & ValueToFind & "*" Then
    Sheet1.Activate
    copyrng = "A" & i & ":" & "H" & i
    ListBox1.RowSource = Range(copyrng).Address
    MsgBox ("Found value on row No. " & i)
    Exit Sub
  End If
Next i
MsgBox ("Value not found!")
End Sub
or faster (and a bit more robust):
Code: 
Private Sub CommandButton1_Click()
Dim ValueToFind As String, copyrng As Range
ValueToFind = TextBox1.Text
With Sheet1
  'Set copyrng = .Columns(1).Find(ValueToFind, after:=.Columns(1).Cells(.Columns(1).Cells.Count), LookIn:=xlFormulas, lookat:=xlPart, MatchCase:=False, searchformat:=False)
  'the line above if you're searching the whole column, the line below instead if your searching a smaller range.
  Set copyrng = .Range("A1:A2539").Find(ValueToFind, after:=.Range("A2539"), LookIn:=xlFormulas, lookat:=xlPart, MatchCase:=False, searchformat:=False)
  If Not copyrng Is Nothing Then
    Set copyrng = copyrng.Resize(, 8)
    .Activate
    ListBox1.RowSource = copyrng.Address
    MsgBox ("Found value on row No. " & copyrng.Row)
  Else
    MsgBox ("Value not found!")
  End If
End With
End Sub
 
thats super perfect dear

but i want to add all the searched data in to below listbox which is i defined on the user form..
how will it done..

my code is..
ListBox1.RowSource = range(copyrng).Address

but getting some error "method range object failed"

Please help
 
Regarding the error, which macro did you use? copyrng in one is a string, in the other it's a range.
Otherwise try:
Code: 
Private Sub CommandButton1_Click()
Dim i As Long, j As Long, ValueToFind As String, copyrng As Range
ValueToFind = UCase(TextBox1.Text)
With Sheet1
  For i = 1 To 2539
    If UCase(.Cells(i, 1).Value) Like "*" & ValueToFind & "*" Then
      .Activate
      Set copyrng = .Cells(i, 1).Resize(, 8)
      ListBox1.AddItem copyrng.Cells(1).Value
      For j = 1 To 7
        ListBox1.List(ListBox1.ListCount - 1, j) = copyrng.Cells(j + 1).Value
      Next j
      MsgBox ("Found value on row No. " & i)
    End If
  Next i
End With
If ListBox1.ListCount = 0 Then MsgBox ("Value not found!")
End Sub
 
i have tried it its working great
but I need to pick all the column values in listbox currently it is showing only the the column where i am searching i have 8 column a,b,c,d,e,f,g,h.
 
I thought you'd already set the number of columns at design time. This does it at run time:
Code: 
Private Sub CommandButton1_Click()
Dim i As Long, j As Long, ValueToFind As String, copyrng As Range
ListBox1.ColumnCount = 9
ListBox1.ColumnWidths = "30"
ValueToFind = UCase(TextBox1.Text)
With Sheet1
  For i = 1 To 2539
    If UCase(.Cells(i, 1).Value) Like "*" & ValueToFind & "*" Then
      .Activate
      Set copyrng = .Cells(i, 1).Resize(, 8)
      ListBox1.AddItem i
      For j = 1 To 8
        ListBox1.List(ListBox1.ListCount - 1, j) = copyrng.Cells(j).Value
      Next j
    End If
  Next i
End With
If ListBox1.ListCount = 0 Then MsgBox ("Value not found!")
End Sub
 
As of now I am able to find the record only in a particular column if I want to make it dynamic means over all the sheet then I think I need to use usedrange but how can you please suggest something

Regards
Nipendra
 
A few changes to the code in msg#6
1. to search all the used range
2. if the sought string is to be found more than once on a given row, that row will only appear once in the listbox
Code: 
Private Sub CommandButton1_Click()
Dim i As Long, j As Long, k As Long, ValueToFind As String, copyrng As Range
ListBox1.ColumnCount = 9
ListBox1.ColumnWidths = "30"
ValueToFind = UCase(TextBox1.Text)
With Sheet1
  For i = 1 To .UsedRange.Rows.Count
    For k = 1 To .UsedRange.Columns.Count
      If UCase(.Cells(i, k).Value) Like "*" & ValueToFind & "*" Then
        .Activate
        Set copyrng = .Cells(i, 1).Resize(, 8)
        ListBox1.AddItem i
        For j = 1 To 8
          ListBox1.List(ListBox1.ListCount - 1, j) = copyrng.Cells(j).Value
        Next j
        Exit For
      End If
    Next k
  Next i
End With
If ListBox1.ListCount = 0 Then MsgBox ("Value not found!")
End Sub
 
You must log in or register to reply here.
Back
Top