Excel VBA - Find User Inputted Variable
 

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

--
Message posted from http://www.ExcelForum.com

Excel VBA - Find User Inputted Variable
 

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

--
Regards,
Tom Ogilvy

"andibevan " wrote in message
...
Hi All,

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

I wish to search all of column B for a user inputted variable
"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 Log
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 it
successfully finds the Lookup_in variable, just will not recognise it
as a cell address in order to locate it.

Any help would be very gratefully received.

Thanks alot

Andi


---
Message posted from http://www.ExcelForum.com/

Excel VBA - Find User Inputted Variable
 

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



"andibevan " wrote in
message ...
Hi All,

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

I wish to search all of column B for a user inputted variable
"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 Log
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 it
successfully finds the Lookup_in variable, just will not
recognise it
as a cell address in order to locate it.

Any help would be very gratefully received.

Thanks alot

Andi


---
Message posted from http://www.ExcelForum.com/

Excel VBA - Find User Inputted Variable
 

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

--
Message posted from http://www.ExcelForum.com