Excel VBA - Find User Inputted Variable

A

andibevan

Hi All,

I am a bit new to this VBA business and am struggling to get a piece o
code right.

I wish to search all of column B for a user inputted variabl
"Lookup_in" and then for that cell to be selected

I have been trying:-

Public Sub Display_Temp()

Dim Lookup_ref

Range("a1").select

Lookup_in = Inputbox("Record to Look up?", "Project Log Lookup", 63
1)

Lookup_ref = Worksheets("Project Lo
Form").Range("B8:B10000").Find(Lookup_in)

Range(Lookup_ref).select

End Sub

The program gets stuck on the last line, which I find confusing as i
successfully finds the Lookup_in variable, just will not recognise i
as a cell address in order to locate it.

Any help would be very gratefully received.

Thanks alot

And
 
T

Tom Ogilvy

Public Sub Display_Temp()
Dim Lookup_ref as Range
Dim Lookup_In as String

Range("a1").select

Lookup_in = Inputbox("Record to Look up?", _\
"Project Log Lookup", 63,1)

set Lookup_ref = Worksheets("Project Log Form") _
.Range("B8:B10000").Find(Lookup_in)

If not Lookup_ref is nothing then
Lookup_ref.Select
Else
msgbox Lookup_In & " was not found"
End if

End Sub
 
C

Chip Pearson

You need to use the SET keyword to set the range variable
Lookup_ref to the result reference returned by Find.

Set Lookup_ref = Worksheets("Project Log
Form").Range("B8:B10000").Find(Lookup_in)



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
A

andibevan

Just wanted to say thanks to both of you.

It is absolutely awesome to be able to use this forum to increase m
knowledge. I can't imagine how long this simple thing would have take
to work it out on my own.

: )

Thanks

And
 
Top