1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

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


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

Search Sheet with VBA

Discussion in 'VBA Macros' started by Nu2Java, May 15, 2018.

  1. Nu2Java

    Nu2Java Member

    Hello, I am using some code here to search a part number in Column A, then find a reference match in Column D of the same row. This seems to work just fine, but now I have some random occasions that I am coming across where it will not find what is in Column D.

    For example... I have "LC50" in Column A and it is found ok.
    I have "60010" in Column D and it is not found.
    I thought maybe formatting, blank spaces etc but I cannot find anything.

    Code (vb):

    Sub TestFindAll()

    Sheets("SMT Component List").Activate
      Dim SearchRange As Range
      Dim FindWhat As Variant
      Dim FoundCells As Range
      Dim FoundCell As Range
      Dim myString As Variant
      Set SearchRange = Range("A1:A40000")
      FindWhat = Userform1.txtPartNum.Value
      Set FoundCells = FindAll(SearchRange:=SearchRange, _
      FindWhat:=FindWhat, _
      LookIn:=xlValues, _
      LookAt:=xlWhole, _
      SearchOrder:=xlByColumns, _
      MatchCase:=False, _
      BeginsWith:=vbNullString, _
      EndsWith:=vbNullString, _
      If FoundCells Is Nothing Then
      MsgBox "Part Number Not Found:  " & FindWhat & vbCrLf & vbCrLf & "**Contact MFG Engineer", vbExclamation, "Search Results"
      Exit Sub
      For Each FoundCell In FoundCells
      myString = FoundCell.Offset(0, 3)
      If Userform1.txtMfgPartNum = "" Then Exit Sub
      'If Userform1.txtMfgPartNum = Userform1.txtPartNum Or Userform1.txtMfgPartNum Like "*" & myString & "*" Then
     If Userform1.txtMfgPartNum = Userform1.txtPartNum Or Userform1.txtMfgPartNum = myString Then
      MsgBox "Match Found... Please Proceed!", vbInformation, ""
      Call Write_Pass
      Userform1.ListBox1.AddItem (FoundCell.Offset(0, 3))
      Userform1.Image1.Visible = True
      Userform1.Image2.Visible = False
      Exit Sub
      Userform1.ListBox1.AddItem (FoundCell.Offset(0, 3))
      End If
      Next FoundCell
      MsgBox "** WARNING **" & vbCrLf & vbCrLf & "No Match Found: " & Userform1.txtMfgPartNum, vbCritical, "ERROR"
      Userform1.Image1.Visible = False
      Userform1.Image2.Visible = True
      Call Write_MFG_Text
      Call Alert_Email
      End If

    End Sub

    ▬▬▬▬▬▬▬▬▬ Mod edit : thread moved to appropriate forum !

  2. Jessie_31

    Jessie_31 New Member


    Your code reads:
    Set SearchRange = Range("A1:A40000")
    and is only searching within column A. That would be why the value in Column D is not found.

    I hope this is the answer you are searching for!

    Marc L likes this.
  3. Nu2Java

    Nu2Java Member

    Thanks... I figured it out. It "was" actually a formatting problem. I had to perform "Text to Columns" in order to get it formatted correctly.

Share This Page