Why does this not work? Search row 1 report colum 2

[wmtsub]

I want to search a colum on another workbook for a variable "VND" and if found return colum b from that locatin, and if nothing is found default to "MTS".

I get errors on set b.

Workbooks.Open ("\\JAXentitive.xlsm")  'open book name with vendors
      lr = Range("A1").End(xlDown).Row ' Last Row
      With ("A1:A" & lr)
      Set b = .Find(what = vnd, lookat:=xlWhole, LookIn:=xlValues)
      If Not b Is Nothing Then
      sname = Cells(b.Row, "B").Value
      Else
      sname = "Mts"
      End If
      End With

 

[Fluff]

Your missing the word "Range" from your with statement.

 

[wmtsub]

Thank you, that cleard the error. But my code is not returning the correct results. If it finds the match it returns null, if it is missing it errors out. Is there a better way to do this?

 

[Fluff]

What returns Null if a value is found & what errors out is nothing is found?

 

[wmtsub]

Hey Fluff,
Thanks for following up on this for me. I kept try into figure this out sand I found a formatting error on the other workbook. Corrected that and now it is working.
Thanks again.

 

[Fluff]

You're welcome & thanks for the feedback