Vlookup from Input Box VBA

[Nase34]

Hello and thank you in advance.

I am looking for a way to do a vlookup from an input box using VBA.

Basically I need to be able to enter a part number into the input box have it do a vlookup on my spreadsheet and select three cells from the row it finds the PN in to copy.

So for example in the attached spreadsheet I would want to be able to have a pop up box come up and say "Enter Part Number", I would then enter PN 12360 in this case it would vlookup this part in my spreadsheet and select and copy the PART NO (A17 in this case), the HERE DATE (K17 in this case), and the INTERNAL COMMENTS (L17 in this case)

I just need these to be copied to the clipboard for now. I inserted column letters and row numbers below for reference, in excel my A1 would be PART NO

A

B

C

D

E

F

G

H

I

J

K

L

M

1

PART NO

UNIT $

ST TY

DESCRIPTION

REQ QTY

REQ DATE

PO NO

PO QTY

DUE DATE

SHIP DATE

HERE DATE

INTERNAL COMMENTS

2

12345

$0.0000

P

NUT,HEX #6-32 G8 ZN

2

06/01/17

12/31/30

04/30/17

Comment

3

12346

$1.0000

P

NUT,HEX #6-32 G8 ZN

3

06/02/17

12/31/30

04/30/17

Comment

4

12347

$2.0000

P

NUT,HEX #6-32 G8 ZN

4

06/03/17

12/31/30

04/30/17

Comment

5

12348

$3.0000

P

NUT,HEX #6-32 G8 ZN

5

06/04/17

12/31/30

04/30/17

Comment

6

12349

$4.0000

P

NUT,HEX #6-32 G8 ZN

6

06/05/17

12/31/30

04/30/17

Comment

7

12350

$5.0000

P

NUT,HEX #6-32 G8 ZN

7

06/06/17

12/31/30

04/30/17

Comment

8

12351

$6.0000

P

NUT,HEX #6-32 G8 ZN

8

06/07/17

12/31/30

04/30/17

Comment

9

12352

$7.0000

P

NUT,HEX #6-32 G8 ZN

9

06/08/17

12/31/30

04/30/17

Comment

10

12353

$8.0000

P

NUT,HEX #6-32 G8 ZN

10

06/09/17

12/31/30

04/30/17

Comment

11

12354

$9.0000

P

NUT,HEX #6-32 G8 ZN

11

06/10/17

12/31/30

04/30/17

Comment

12

12355

$10.0000

P

NUT,HEX #6-32 G8 ZN

12

06/11/17

12/31/30

04/30/17

Comment

13

12356

$11.0000

P

NUT,HEX #6-32 G8 ZN

13

06/12/17

12/31/30

04/30/17

Comment

14

12357

$12.0000

P

NUT,HEX #6-32 G8 ZN

14

06/13/17

12/31/30

04/30/17

Comment

15

12358

$13.0000

P

NUT,HEX #6-32 G8 ZN

15

06/14/17

12/31/30

04/30/17

Comment

16

12359

$14.0000

P

NUT,HEX #6-32 G8 ZN

16

06/15/17

12/31/30

04/30/17

Comment

17

12360

$15.0000

P

NUT,HEX #6-32 G8 ZN

17

06/16/17

12/31/30

04/30/17

Comment

18

12361

$16.0000

P

NUT,HEX #6-32 G8 ZN

18

06/17/17

12/31/30

04/30/17

Comment

19

12362

$17.0000

P

NUT,HEX #6-32 G8 ZN

19

06/18/17

12/31/30

04/30/17

Comment

20

12363

$18.0000

P

NUT,HEX #6-32 G8 ZN

20

06/19/17

12/31/30

04/30/17

Comment

21

12364

$19.0000

P

NUT,HEX #6-32 G8 ZN

21

06/20/17

12/31/30

04/30/17

Comment

22

12365

$20.0000

P

NUT,HEX #6-32 G8 ZN

22

06/21/17

12/31/30

04/30/17

Comment

23

12366

$21.0000

P

NUT,HEX #6-32 G8 ZN

23

06/22/17

12/31/30

04/30/17

Comment

24

12367

$22.0000

P

NUT,HEX #6-32 G8 ZN

24

06/23/17

12/31/30

04/30/17

Comment

<tbody>
</tbody>

 

[dphelps526]

Try this...

Sub inputBox()


    Dim strPart As String
    Dim strDate As Date
    Dim strInternal As String
    Dim i As Integer
    Dim lngLastRow As Long
    
    lngLastRow = Range("A2").End(xlDown).Row
    
    strPart = Application.inputBox("Select a part")
    
    For i = 2 To lngLastRow
        If Cells(i, 1).Value = strPart Then
            strDate = Cells(i, 11).Value
            strInternal = Cells(i, 12).Value
        End If
    Next i


    MsgBox "Part Number: " & strPart & " Date: " & strDate & " Internal Comments: " & strInternal
    
End Sub

 

[Nase34]

Wow that was fast! Amazes me how quick some of you guys can knock this stuff out, thanks!!

This seems to work great! The only thing is, and I probably wasn't clear in my vague description, is instead of spitting out those 3 results in a message box I was looking to have them copied to the clip board. The goal would be to be able to paste them into a different program I use.

Thanks!

 

[Llupo01]

Hello,

where do you want to copy to? Its not vlookup...the formula is following:

Sub test()
Dim thing As Variant

thing = InputBox("What are you looking for?", "Finding")

Cells.Find(thing).Select

Selection.Copy

End Sub

 

[Nase34]

Dphelps526 hit it pretty much on the head with his code, all I am saying is instead showing those values in a message box I would like the values of strPart, strDate, and strInternal copied to the clipboard so I could copy them to another program or even copy them into another spreadsheet

 

[dphelps526]

While you're in the VBA editor:
Click Tools>References
Check the box next to "Microsoft Forms 2.0 Object Library."

Then:

Sub inputBox()


    Dim strPart As String
    Dim strDate As Date
    Dim strInternal As String
    Dim i As Integer
    Dim lngLastRow As Long
    
    Dim clipboard As MSForms.DataObject
    Dim myData As String
    
    Set clipboard = New MSForms.DataObject
    
    lngLastRow = Range("A2").End(xlDown).Row
    
    strPart = Application.inputBox("Select a part")
    
    For i = 2 To lngLastRow
        If Cells(i, 1).Value = strPart Then
            strDate = Cells(i, 11).Value
            strInternal = Cells(i, 12).Value
        End If
    Next i
    
    myData = strPart & " " & strDate & " " & strInternal
    clipboard.SetText myData
    clipboard.PutInClipboard
    
End Sub

Then try pasting in a word document or whatever to see if it worked

 

[Nase34]

It works! Awesome thanks for the help!

 

[dphelps526]

No problem!