FIND fuction in a cell formula

[dschroeder]

Is it possible to create a cell formula in Excel to reproduce the following macro? The items to find are account numbers and then the offset selects the dollar value for the found accounts. Thanks for any help-David

Windows("data file.xlsx").Activate
Columns("C:C").Select
Selection.Find(What:="21823", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 2).Select
A = ActiveCell

Windows("data file.xlsx").Activate
Columns("C:C").Select
Selection.Find(What:="21825", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 2).Select
B = ActiveCell


Range("E1") = A + B

 

[mvptomlinson]

Since your OFFSET is looking at column E, you probably don't want to put the formula within column E. If you really, really do want to put it in E1, adjust the formulas below to be a range rather than full columns, e.g. C2:C.. and E2:E..

=INDEX(E:E,MATCH("*21823*",C:C,0))+INDEX(E:E,MATCH("*21824*",C:C,0))

This assumes the data in column C is text, not numeric. (I assumed this because you're looking for a partial match in the VBA code (xlPart).

 

[dschroeder]

This workded perfect. Thanks for quick response. I am also using Excel 07