Search Sheet with VBA

[Nu2Java]

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.

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, _
  BeginEndCompare:=vbTextCompare)
  
  
  If FoundCells Is Nothing Then
  MsgBox "Part Number Not Found:  " & FindWhat & vbCrLf & vbCrLf & "**Contact MFG Engineer", vbExclamation, "Search Results"
  Exit Sub
  Else
  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
  Else
  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 !

​

 

[Jessie_31]

Hello,

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!

Jessie

 

[Nu2Java]

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.