Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transfer variable to user Form | Excel Discussion (Misc queries) | |||
Time inputted as minutes and seconds Excel | New Users to Excel | |||
I want a macro to save a workbook as a user inputted cell | Excel Discussion (Misc queries) | |||
Cells.Find error Object variable or With block variable not set | Excel Programming | |||
Calling variable from user form | Excel Programming |