Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Help
Hi everyone
I wonder if I could ask for some help on efficient coding? I have a spreadsheet that is set out much like a table. I have some code in a module that I call to search the spreadsheet for a value/text. I have often wondered if there is a better way of coding this as I am using a looping statment. Example: I want to look up a part code No. I call the following routine and pass the public variables (y, x, nam & mylook) to it: ------------------------------------------ Public Sub LookUP() On Error Resume Next '---just incase---- Do While Check = True y = y + 1 nam = Cells(y, x).Value If nam = mylook Then '---if my value/text is found Check = False End If Loop End Sub --------------------------------------------- This code searches cell by cell, looping. There must be a better way? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Help
Please try this ...
============================ Public Sub lookup1() On Error Resume Next With Cells .Find("Abc").Select End With End Sub ============================ Replace "Abc" with your variable. Please rate me. "scottfoxall" wrote: Hi everyone I wonder if I could ask for some help on efficient coding? I have a spreadsheet that is set out much like a table. I have some code in a module that I call to search the spreadsheet for a value/text. I have often wondered if there is a better way of coding this as I am using a looping statment. Example: I want to look up a part code No. I call the following routine and pass the public variables (y, x, nam & mylook) to it: ------------------------------------------ Public Sub LookUP() On Error Resume Next '---just incase---- Do While Check = True y = y + 1 nam = Cells(y, x).Value If nam = mylook Then '---if my value/text is found Check = False End If Loop End Sub --------------------------------------------- This code searches cell by cell, looping. There must be a better way? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Help
Thankyou Naveen, That works great!
"Naveen" wrote: Please try this ... ============================ Public Sub lookup1() On Error Resume Next With Cells .Find("Abc").Select End With End Sub ============================ Replace "Abc" with your variable. Please rate me. "scottfoxall" wrote: Hi everyone I wonder if I could ask for some help on efficient coding? I have a spreadsheet that is set out much like a table. I have some code in a module that I call to search the spreadsheet for a value/text. I have often wondered if there is a better way of coding this as I am using a looping statment. Example: I want to look up a part code No. I call the following routine and pass the public variables (y, x, nam & mylook) to it: ------------------------------------------ Public Sub LookUP() On Error Resume Next '---just incase---- Do While Check = True y = y + 1 nam = Cells(y, x).Value If nam = mylook Then '---if my value/text is found Check = False End If Loop End Sub --------------------------------------------- This code searches cell by cell, looping. There must be a better way? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Help
Then,
Please *rate* my answer ... "scottfoxall" wrote: Hi everyone I wonder if I could ask for some help on efficient coding? I have a spreadsheet that is set out much like a table. I have some code in a module that I call to search the spreadsheet for a value/text. I have often wondered if there is a better way of coding this as I am using a looping statment. Example: I want to look up a part code No. I call the following routine and pass the public variables (y, x, nam & mylook) to it: ------------------------------------------ Public Sub LookUP() On Error Resume Next '---just incase---- Do While Check = True y = y + 1 nam = Cells(y, x).Value If nam = mylook Then '---if my value/text is found Check = False End If Loop End Sub --------------------------------------------- This code searches cell by cell, looping. There must be a better way? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|