Search a value in a table from input box and return multiple values

Question

I have a table in which the cells C10:H10 contain different names of items and each column has 10 numerical values. I need to create a macro in which the user inserts the name of the item that he wants, and the macro is going to copy all the numerical values of that column and paste them in another table.

I was thinking of doing that with HLOOKHUP but I don't know how to do that. I tried with the following code but it doesn't do what I want:

Sub copy()

 Dim answer As Variant
    answer = InputBox("Insert the name")

Dim i As Integer, a As Range
i = 0

For k = 0 To 5
    Cells(10, 3 + k).Select
    If ActiveCell = answer Then
        a = Activecell
        For x = 1 To 10
            a.Offset(x, 0).Select
            Selection.Copy
            Range("K1").Select
            ActiveCell.Offset(i, 0).Select
            ActiveSheet.Paste
             i = i + 1
       Next

    End If
Next

End Sub

Can you confirm which cells have the name of the item, and where you would like to paste the values — PuravTheGreat, Dec 08 '19 at 22:20
In the cells from C10 to H10 there are the names of the items. — Tom4, Dec 08 '19 at 22:37
I want to paste the numerical values starting from the cell K1 to K10 — Tom4, Dec 08 '19 at 22:38
Tip: Use `Long` instead of `Integer`. https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long/51689021#51689021 — pgSystemTester, Dec 08 '19 at 22:50
Tip: avoid select statements: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — pgSystemTester, Dec 08 '19 at 22:50

pgSystemTester · Answer 1 · 2019-12-08T22:59:31.703

There's a lot of room for improvement here (don't worry, we all started there). Here's a couple quick tips...

Try to avoid using select
Try to avoid copy/paste
Try to do all of your "modifications" to a worksheet at the same time (I'm not going to include this in example).
Use Long instead of Integer

See below. Note comments.

Sub copy()

Dim answer As Variant ' why not make it a string? Is it anything other than text?
    answer = InputBox("Insert the name")

Dim i As Long, a As Range, k as Long

i = 0 'you probably want to set this down below...

For k = 0 To 5

    Set a = Cells(10, 3 + k)

    If a.Value = answer Then
        'also careful with upper case since you're having user enter. 
        'You might consider:
        'If UCASE(a.Value) = UCASE(answer) then

        i = 0 'You weren't resetting this before.

        For x = 1 To 10

                Range("K1").Offset(i, 0).Value = a.Offset(x, 0).Value
                i = i + 1

                'This next line would be a better option.
                'Save you a varaible and a couple lines of code
                'Range("K1").offset(x-1,0).value = a.Offset(x, 0).Value

        Next x 'always good to define what's next

    End If
Next k

End Sub

PuravTheGreat · Answer 2 · 2019-12-08T23:02:20.597

Follow @PGSystemTester's tips. Here's my go at your problem.

I don't use counter variables such as For i = 1 to 10, I instead just use the For Each type of For loop, which I think is easiest to pick up.

Sub copy()

    Dim answer As Variant
    Dim cell As Range, cell2 As Range
    Dim wb As Workbook, ws As Worksheet

    Set wb = ThisWorkbook
    Set ws = wb.ActiveSheet

    answer = InputBox("Insert Name")

    With ws 'Makes things down below a little nicer (not required if you don't need to refer
    to sheet all the time.
        For Each cell In .Range("C10:H10")
            If LCase(cell.Value) = LCase(answer) Then
                For Each cell2 In .Range("K1:K10")
                    cell2.Value = .Cells(cell.Row + cell2.Row, cell.Column)
                Next cell2
            End If
        Next cell
    End With

End Sub

@PGSystemTester - They're good tips and it's crucial to start following them from the get-go so you're not always thinking about `.Select` and `.Copy / .Paste` in your logic thinking! — PuravTheGreat, Dec 08 '19 at 22:59

score 0 · Answer 3 · answered Dec 08 '19 at 22:46

Sub copy()

    Dim vAnswer As Variant
    Dim iCol As Integer


    vAnswer = InputBox("Insert the name")

    With Range("C10:H10")
        Set c = .Find(vAnswer, LookIn:=xlValues)
        If Not c Is Nothing Then
            iCol = c.Column
            Range("K1:K10").Value = Range(Cells(11, iCol), Cells(20, iCol)).Value
        Else
            Exit Sub
        End If
    End With

End Sub

Search a value in a table from input box and return multiple values

3 Answers3