VBA - Code to search entries in another spreadsheet - need help

[hariprasadcp]

Hi,
I am searching for specific text values in another spreadsheet, if it finds I must write in the adjacent column as Available. unfortunately this is not working as expected, What is wrong in the below code? or is there any other code which I could try .

ex:
DC35
DC36
DCU20680
GC070648

Option Explicit

Sub test()

Dim wkbOpen As Workbook
Dim wks As Worksheet
Dim Cell As Range
Dim FoundCell As Range
Dim Wkb As Variant

Set Cell = Application.InputBox( _
Prompt:="Please a cell containing the desired value.", _
Title:="Select a Cell", _
Type:=8)

If Cell Is Nothing Then Exit Sub

Wkb = Application.GetOpenFilename(FileFilter:="Excel Files (*.xlsx), *.xlsx", FilterIndex:=1, Title:="Select a Workbook", MultiSelect:=False)

If Wkb = False Then Exit Sub

Application.ScreenUpdating = False

Set wkbOpen = Workbooks.Open(FileName:=Wkb)

With wkbOpen

For Each Cell In UserRange

Set FoundCell = Cells.Find(What:=Cell.Value, _
LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False) 'Adjust the parameters, accordingly
If Not FoundCell Is Nothing Then
Cell.Offset(, 1).Value = Available
Exit Sub
End If
Next Cell

End With

Application.ScreenUpdating = True


End Sub

 

[AlphaFrog]

Try something like this. It searches the 1st sheet of the selected workbook.

[color=darkblue]Sub[/color] test()
    
    [color=darkblue]Dim[/color] wks     [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] Cell    [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] FoundCell [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] Wkb     [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
    [color=darkblue]Set[/color] Cell = Application.InputBox( _
               Prompt:="Please a cell containing the desired value.", _
               Title:="Select a Cell", _
               Type:=8)
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] 0
    [color=darkblue]If[/color] Cell [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    
    Wkb = Application.GetOpenFilename(FileFilter:="Excel Files (*.xlsx), *.xlsx", FilterIndex:=1, Title:="Select a Workbook", MultiSelect:=False)
    [color=darkblue]If[/color] Wkb = [color=darkblue]False[/color] [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    
    Application.ScreenUpdating = False
    
    [color=darkblue]With[/color] Workbooks.Open(Filename:=Wkb)
    
        [color=darkblue]Set[/color] FoundCell = .Sheets(1).Cells.Find(What:=Cell.Value, _
                                              LookIn:=xlValues, _
                                              LookAt:=xlWhole, _
                                              MatchCase:=False)    [color=green]'Adjust the parameters, accordingly[/color]
        [color=darkblue]If[/color] [color=darkblue]Not[/color] FoundCell [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
            Cell.Offset(, 1).Value = "Available"
        [color=darkblue]Else[/color]
            MsgBox Cell.Value, , "No Match Found"
        [color=darkblue]End[/color] [color=darkblue]If[/color]
        
        [color=green]'.Close SaveChanges:=False[/color]
    
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    Application.ScreenUpdating = [color=darkblue]True[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[hariprasadcp]

Thanks for the Quick response, it is not working as expected

I feel something is wrong with the IF condition not sure what that is, it is not returning what it searches in the other workbook

Set FoundCell = .Sheets(1).Cells.Find(What:=Cell.Value, _
                                              LookIn:=xlValues, _
                                              LookAt:=xlWhole, _
                                              MatchCase:=False)    'Adjust the parameters, accordingly
        If Not FoundCell Is Nothing Then
            Cell.Offset(, 1).Value = "Available"
        Else
            MsgBox Cell.Value, , "No Match Found"
        End If

 

[AlphaFrog]

What is the exact value you are searching for as seen in the formula bar?

What is the exact value of the expected match as seen in the formula bar from the 1st worksheet of the selected workbook file?

 

[hariprasadcp]

If the value is found in the other spreadsheet it must write in the adjacent column as "available" ,. if not then "not found".
Right now the issue is it writes the value as available even though the value is not found.
Need your inputs on this.

 

[AlphaFrog]

If the value is found in the other spreadsheet it must write in the adjacent column as "available" ,. if not then "not found".
Right now the issue is it writes the value as available even though the value is not found.
Need your inputs on this.

I understand what the code is suppose to do.

The problem is likely what the cell values are on the two sheets. That's why asked the two questions in post #4.

 

[hariprasadcp]

The cell values are alike, infact I have removed any additional spaces from the text and kept the same format