Lookup macro

roughleg

Board Regular
Joined
Jun 21, 2004
Messages
181
Hi! All,
I'm sure this has been addressed many times, but I can't seem to find it. Obviously I'm a novice. I know alot more than when I started, but that isn't very much.

I have several macro's that I'm using to up date prices based on several calculations. I simply want a macro that looks up a part number that I copy from one sheet ("Price List") and selects that number that is in the first column of a second sheet (part#s). From there I'll just call my other macros. I appreciate the help greatly.
Skip
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

roughleg

Board Regular
Joined
Jun 21, 2004
Messages
181
If no one wants to help me, could someone tell me why something this simple can't get a response???
If I'm doing something wrong, I'd like to know about it.
Thanks,
Skip
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
"I have several macro's that I'm using to up date prices based on several calculations. I simply want a macro that looks up a part number that I copy from one sheet ("Price List") and selects that number that is in the first column of a second sheet (part#s). From there I'll just call my other macros. I appreciate the help greatly. "

1st off did you read this?

If you "looks up a part number that I copy from one sheet ("Price List")" implies you have the part number?
Why look it up?

2nd you need to tell us what you need and give sample data locations:

Like: I need to use a part number that is in Cell: A1 of the active sheet to select that Part Number's cell in the first Column of Sheet "Parts"


I am guessing here as I cannot tell what it is you want?


Try:


Sub myPart()
'Standard module code, like: Module1.
Dim myBotttomRow&, myStartRow&
Dim Msg$, Title$, Default, myNum

On Error GoTo myEnd

Msg = "Enter a Part Number:"
Title = "Find Part!"
Default = "123456"

' Display message, title, and default value.
myNum = InputBox(Msg, Title, Default)

Sheets("Parts").Select
myStartRow = 4
myBottomRow = Sheets("Parts").Range("A65536").End(xlUp).Row

Set myRng = Sheets("Parts").Range(Cells(myStartRow, 1), Cells(myBottomRow, 1))

myRng.Select

With Selection
Set c = .Find(myNum, LookIn:=xlValues)
c.Select

If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Select
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

myEnd:
End Sub
 

roughleg

Board Regular
Joined
Jun 21, 2004
Messages
181

ADVERTISEMENT

What I want to do is what the find command from the edit menu does, I just want to incorporated into a macro.
Skip
 

roughleg

Board Regular
Joined
Jun 21, 2004
Messages
181
That is exactly what I want to do. I am picking a part number from an active Worksheet ("Usage") and I am wanting find that part number in Sheet ("part#s) and make that cell active.

Skip
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
See above for a custom solution I posted!

Here is how to open the Find Utility by code, as well:


Sub opnFind()

Application.SendKeys ("^f")

End Sub
 

roughleg

Board Regular
Joined
Jun 21, 2004
Messages
181
Joe,
Sorry. When I pulled up your post the first time, only the first part displayed. The code didn't show up. Don't know why. I really appreciate your help. Just what I needed.
Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,118,680
Messages
5,573,624
Members
412,539
Latest member
itchy00
Top