Find() Function Look for Formula not Output

Question

Private Sub ListBox1_Click()
 asset = Me.ListBox1.Value
 ThisWorkbook.Sheets("AssetPurchase").Activate
 sl = Range("I7", "I500").Find(asset).Row

 Me.TextBox2.Value = Range("H" & Range("I7", "I500").Find(asset).Row).Value
  MsgBox (Range("I7", "I500").Find(asset).Row)
End Sub

I'm using the above sub to switch worksheets and then search a range for a string

The problem I'm having is that the cells in this range are calculated from the content of other cells and find() is searching the formulas in the range not the output of the formulas

I would like the output of the formulas within a range to match a string

Bond Bond · Accepted Answer · 2015-07-26T05:08:12

Find() has the following arguments:

Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

Use the third argument, LookIn, to specify whether you want to search the formula for text (xlFormulas) or the cell value (xlValues).

For example, to search for the first cell value that matches the text stored in asset, use:

sl = Range("I7", "I500").Find(asset, , xlValues).Row

Find() Function Look for Formula not Output

1 Answers