Where would I define a specific column

Hi all,

I have this bit of code which works for what I need, but I can't find where to specify a certain column.

If I only want it to search column D for the required value, where would I specify this?

 

ub LookforText()
Dim bk1 As Workbook, sh1 As Worksheet, cell As Range
Dim bk2 As Workbook, sh2 As Worksheet, r As Range

Set bk1 = Workbooks("Credit Hire Review Assistant 3.10.xlsm")
Set sh1 = bk1.Worksheets("Settlement")
Set cell = sh1.Range("F5")    ' this remembers the cell where the search string is
  ' and can be used to produce the value on demand

Set bk2 = Workbooks.Open("S:\Claims\Credit Hire\Credit Hire Spreadsheet\C Hire MI Sept 2011-.xlsx")
Set sh2 = bk2.Worksheets("Sheet1")
Set r = sh2.Cells.Find(What:=cell.Value, _
  After:=ActiveCell, _
  LookIn:=xlValues, _
  LookAt:=xlWhole, _
  SearchOrder:=xlByRows, _
  SearchDirection:=xlNext, _
  MatchCase:=False)
If r Is Nothing Then
 MsgBox "Not found"
Else
 Application.Goto Reference:=r
End If

End Sub

Change this line

Set r = sh2.Cells.Find(What:=cell.Value, _

as

Set r = sh2.Range("D:D").Find(What:=cell.Value, _

Hi,

Thanks for the reply, I just tried amending as you suggest but now when it runs the script, it comes up with a "Run-time error '13' and says Type mismatch.

Any ideas?

Thanks again

You Pl Try This.

Change this

Set r = sh2.Cells.Find(What:=cell.Value, _
  After:=ActiveCell, _
  LookIn:=xlValues, _
  LookAt:=xlWhole, _
  SearchOrder:=xlByRows, _
  SearchDirection:=xlNext, _
  MatchCase:=False)

By

Set r = sh2.Range("D:D").Find(What:=cell.Value)

Or by

Set r = sh2.Range("D:D").Find(What:=cell.Value, _
  After:=Range("D1"), _
  LookIn:=xlValues, _
  LookAt:=xlWhole, _
  SearchOrder:=xlByRows, _
  SearchDirection:=xlNext, _
  MatchCase:=False)

The error is due to activecell is not in D column.

That is absolutely perfect, it speeds up the search by about 10 seconds - which is quite a long time when you are staring at a screen! Many thanks.

As an aside, I'm trying to also put a

Rows(ActiveCell.Row).Select

in there after it has found the correct cell but it comes up with a 1004 error, I don't suppose you would also know where this would need to go?