Search Function

[mrkambo]

Hi

Im struggling to understand why this doesn't work, could someone take a look and advise please

On Error GoTo error
Dim SearchString As String
Dim Lastrow As Long
Dim ans As String
Dim SearchRange As Range
Dim Lastrowa As Long


Const prefix As String = "COLL"
ans = TextBox1.Value
SearchString = prefix & ans
Lastrow = Sheets("Source").Cells(Rows.Count, "J").End(xlUp).Row
Lastrowa = Sheets(ActiveSheet.Name).Cells(Rows.Count, "A").End(xlUp).Row + 1


Set SearchRange = Sheets("Source").Range("J7:J").Find(SearchString)
Sheets(ActiveSheet.Name).Range("A" & Lastrowa).Value = SearchRange.Offset(, -2).Value
Sheets(ActiveSheet.Name).Range("B" & Lastrowa).Value = SearchRange.Offset(, -3).Value
Sheets(ActiveSheet.Name).Range("C" & Lastrowa).Value = SearchRange.Offset(, -5).Value
Sheets(ActiveSheet.Name).Range("D" & Lastrowa).Value = SearchRange.Offset(, -1).Value
Sheets(ActiveSheet.Name).Range("E" & Lastrowa).Value = SearchRange.Offset(, -9).Value
Sheets(ActiveSheet.Name).Range("F" & Lastrowa).Value = SearchRange.Offset(, 6).Value

 

[steve the fish]

Doesnt work is a bit vague but this line is definately wrong:

Set SearchRange = Sheets("Source").Range("J7:J").Find(SearchString)

Id say its:

Set SearchRange = Sheets("Source").Range("J7:J" & Lastrow).Find(SearchString)

You shouldnt really use find like this either. Find, i believe, remembers its last used settings so could produce unintended results. You should give it it's parameters to be sure.

 

[mrkambo]

Doesnt work is a bit vague but this line is definately wrong:

Set SearchRange = Sheets("Source").Range("J7:J").Find(SearchString)

Id say its:

Set SearchRange = Sheets("Source").Range("J7:J" & Lastrow).Find(SearchString)

You shouldnt really use find like this either. Find, i believe, remembers its last used settings so could produce unintended results. You should give it it's parameters to be sure.

I completely missed that! Thanks, ill go back into it and see where its hiccuping

Update:

Works an absolute treat