Excel VBA .find issue

Question

I am a new member of this forum but have done some searching for this particular problem. I haven't found anything yet.

I have a need for a VBA script which searches for a value located in a cell I7 in the first workbook wb1, and reference that entry to a column A:A in a sheet called "Codes" in another workbook wb2, if it finds the value in Cell I7 in Column A:A it will put the text "Value Found" into Cell I7. If it doesn't find that value in the column range, it will copy and paste the old value from the old sheet to the new one as it is.

Excel seems to be executing the code, but the problem I'm getting is that if the value is not located in the columns that are referenced, the code doesn't seem to pick it up, therefore the code just puts the "Value Found" into the target cell no matter if the value is found in the referenced columns, am I missing something here?

Dim wb1 as Workbook, wb2 as Workbook, 
Dim Sh1 as Worksheet
Dim WBS as Range

Set Sh1 = wb1.Sheets("Codes").Range("A:A")
Set WBS = Sh1.Columns(1).Find(What:=wb2.Sheets("Summary").Range("I7:I7").Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,     MatchCase:=False)
If WBS Is Nothing Then
wb1.Sheets("Summary").Range("I7:I7").Copy
wb2.Sheets("Summary").Range("I7:I7").PasteSpecial Paste:=xlPasteAll
Else
wb2.Sheets("Summary").Range("I7:I7").Value = "'Value Found"
End If

I don't understand why your posted code does not --> errors. You never set wb1 to anything; you are setting a Worksheet object (SH1) to a range (should --> Type Mismatch). Also, note that Range("I7") is sufficient; no need for Range("I7:I7"). — Ron Rosenfeld

whytheq whytheq · Accepted Answer · 2014-04-22T09:50:06

As Ron has commented your declarations and instantiations need some amendments. Where do you Set wb1 and wb2 in your code?

This simplified version works fine:

Sub simplified()

Dim rn1 As Range
Dim WBS As Range

Set rn1 = Range("A:A")
Set WBS = rn1.Find( _
        What:=Range("I7").Value, _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False)

If WBS Is Nothing Then
    Range("I7").Copy
    Range("J7").PasteSpecial Paste:= Excel.xlValues
Else
    Range("J7").Value = "'Value Found"
End If

End Sub

Results are simplified to just one sheet in a single workbook:

enter image description here

Excel VBA .find issue

1 Answers