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?

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?

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?

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?