searching

Posted by Kevin B. on February 19, 2001 8:48 AM

How do I take a cell value then in VB, search another file for the first couple of letters of the cell value in a particular column?

Posted by Celia on February 20, 2001 3:34 AM

Kevin
Check whether this does what you need :-

Sub FindCell()
Dim sourceCell As Range, searchColumn As Range, found as String

Set sourceCell = Workbooks("Workbook1").Sheets("Sheet1").Range("A1")
Set searchColumn = Workbooks("Workbook2").Sheets("Sheet1").Range("A:A")

found = searchColumn.Find(What:=Left(sourceCell, 2), After:=searchColumn(65536, 1), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Address

MsgBox found

End Sub

Celia

Posted by Celia on February 20, 2001 3:46 AM

OR :-
Sub FindCell()
Dim sourceCell As Range, searchColumn As Range, found As Range

Set sourceCell = Workbooks("Custom Workbook1").Sheets("Sheet1").Range("A1")
Set searchColumn = Workbooks("Custom Workbook2").Sheets("Sheet1").Range("A:A")

Set found = searchColumn.Find(What:=Left(sourceCell, 2), After:=searchColumn(65536, 1), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

MsgBox found.Address
MsgBox found.Row
MsgBox found.Column

End Sub

Celia