Macro to copy and past data

[UMAKEMESIK]

I have a work book with two sheets.

Sheet 1 is a look up query sheet.

sheet 2 has all the data


I would like to put a value into cell a1 of sheet 1 - for examples "Box1"

the macro would look in sheet 2 for a value of "box 1" in column A of sheet 2

the macro would then copy the data from sheet 2 in column B that corresponds to the Value of "Box 1"

The value from column b of sheet 2 would then be pasted into sheet 1 "B1"

so the result would look like on sheet 1:

an input value on sheet 1 in A1 with the corresponding info on sheet 1 b1

thanks in advance

 

[DSolisATX]

Do you need a macro? It sounds like a vlookup might be a better solution.

 

[UMAKEMESIK]

TO ALL,

I have this code. But this code runs after you put a value in sheet 1 a1 and then click on a lookup button.

I would like to enter a value in sheet 1 a1, hit enter and the code executes.

can this be modified?

thanks

Sub MacroForm1()
    Dim inputws As Worksheet
    Dim outputws As Worksheet
    Dim FormsWS As Worksheet
    Set inputws = ThisWorkbook.Sheets("data")
    Set outputws = ThisWorkbook.ActiveSheet
    Set FormsWS = ThisWorkbook.ActiveSheet
    Dim i As Integer
    Dim j As Integer
    Dim ItemNum As String
    
    ItemNum = FormsWS.Cells(4, 1)
    
    If ItemNum = "" Then
        MsgBox "Item number can not be empty"
        Exit Sub
    End If
    i = 2
    j = 4
    Application.ScreenUpdating = False
    outputws.Unprotect
    outputws.Range("B4:F4").ClearContents
    
    While Trim(inputws.Cells(i, 1)) <> ""
        If Trim(inputws.Cells(i, 1)) = ItemNum Then
            
            outputws.Cells(j, 2) = inputws.Cells(i, 5)
            outputws.Cells(j, 3) = inputws.Cells(i, 6)
            outputws.Cells(j, 4) = inputws.Cells(i, 7)
            outputws.Cells(j, 5) = inputws.Cells(i, 8)
            outputws.Cells(j, 6) = inputws.Cells(i, 9)
            outputws.Protect Scenarios:=True, userinterfaceonly:=True
            Application.ScreenUpdating = True
            Exit Sub
        End If
        i = i + 1
    Wend
    Application.ScreenUpdating = True
    outputws.Protect Scenarios:=True, userinterfaceonly:=True
    MsgBox "Item Number not found"
End Sub

 

[UMAKEMESIK]

I am looking for the shortest route.

if a v lookup is an eaiser solution to what I have posted
please let me know.

thanks

 

[Michael M]

Maybe change it to a Change event macro
Paste this in the sheet module where you want the change to take place

Private Sub worksheet_Change(ByVal Target As Range)
    Dim inputws As Worksheet
    Dim outputws As Worksheet
    Dim FormsWS As Worksheet
    Set inputws = ThisWorkbook.Sheets("data")
    Set outputws = ThisWorkbook.ActiveSheet
    Set FormsWS = ThisWorkbook.ActiveSheet
    Dim i As Integer
    Dim j As Integer
    Dim ItemNum As String
    
    ItemNum = FormsWS.Cells(4, 1)
    
    If ItemNum = "" Then
        MsgBox "Item number can not be empty"
        Exit Sub
    End If
    i = 2
    j = 4
    Application.ScreenUpdating = False
    outputws.Unprotect
    outputws.Range("B4:F4").ClearContents
    
    While Trim(inputws.Cells(i, 1)) <> ""
        If Trim(inputws.Cells(i, 1)) = ItemNum Then
            
            outputws.Cells(j, 2) = inputws.Cells(i, 5)
            outputws.Cells(j, 3) = inputws.Cells(i, 6)
            outputws.Cells(j, 4) = inputws.Cells(i, 7)
            outputws.Cells(j, 5) = inputws.Cells(i, 8)
            outputws.Cells(j, 6) = inputws.Cells(i, 9)
            outputws.Protect Scenarios:=True, userinterfaceonly:=True
            Application.ScreenUpdating = True
            Exit Sub
        End If
        i = i + 1
    Wend
    Application.ScreenUpdating = True
    outputws.Protect Scenarios:=True, userinterfaceonly:=True
    MsgBox "Item Number not found"
End Sub

 

[UMAKEMESIK]

ACTUALLY I GET AN ERROR:

"OUT OF STACK SPACE"

On this line of code

outputws.Range("B4:F4").ClearContents

 

[DSolisATX]

Hopefully, the macro solution works. If you are at all interested in trying a vlookup, here is the formula:

=VLOOKUP(A1,Sheet2!A:B,2)

Paste this formula into the cell where you'd like the query result to return. The first argument is the lookup value, or the cell with the value that will be queried(A1). The second argument is the lookup array(Sheet2!A:B), or the range of cells that will be search. The first column of your array should be the value to match(A). The second column of the array is the resulting information you want returned(B). The last argument indicates that the second column is the information that should be returned from the lookup(2). When you type your value into cell A1, vlookup will search Sheet 2, column A for that value. When/if it finds a match, it will bring back the value from column B and display it in the cell where the vlookup formula resides. Very important - the lookup array (Sheet2!A:B) must be sorted in alpha/numeric order on column A. You probably know all this, but I'm just being thorough.

If you still have any issues, google "excel vlookup" and you will find numerous tutorials and walk-throughs.

hth.

 

[UMAKEMESIK]

Hopefully, the macro solution works. If you are at all interested in trying a vlookup, here is the formula:

=VLOOKUP(A1,Sheet2!A:B,2)

Paste this formula into the cell where you'd like the query result to return. The first argument is the lookup value, or the cell with the value that will be queried(A1). The second argument is the lookup array(Sheet2!A:B), or the range of cells that will be search. The first column of your array should be the value to match(A). The second column of the array is the resulting information you want returned(B). The last argument indicates that the second column is the information that should be returned from the lookup(2). When you type your value into cell A1, vlookup will search Sheet 2, column A for that value. When/if it finds a match, it will bring back the value from column B and display it in the cell where the vlookup formula resides. Very important - the lookup array (Sheet2!A:B) must be sorted in alpha/numeric order on column A. You probably know all this, but I'm just being thorough.

If you still have any issues, google "excel vlookup" and you will find numerous tutorials and walk-throughs.

hth.

thanks for that.

works great except for one thing.


I have evey list item on sheet two in first Alpha order then within the alpha order they are numeric order.

ie....

r1
r2
r34
r56
r332
r445
r556
tw1
tw2
tw25
tw546

I put in R98 and it gives me the last item in the list of R items before it gets to the tw items.

Is there some sort of error correcting syntax I can add so that it will not sub and only look for r98.

thanks for your help

 

[UMAKEMESIK]

I got it,

just added a False after the number 2 in the v lookup.

=VLOOKUP(A1,Sheet2!A:B,2,false)

 

[DSolisATX]

Ah! Let's add one more argument the formula:

=VLOOKUP(A1,Sheet2!A:B,2,FALSE)

This will force an exact match. This argument is optional, but I should've included it for you.