VBA Lookup and Input in Seperate Sheet

[DDREW]

1. Would somone be kind enought to help me with a VBA code. What I need is the following:
2. In Worksheet "Sheet1" I would enter in a number in a cell range of B15:B80. ( All cells in this range would be blank until I ented in number).
3. For refrence I enter in "5" in cell B15
4. Upon entering "5" lookup based on look up key in adjacet cell column A15:A80.
5. In example lookup key is A15.
6. Lookup would run in "Sheet2".
7. Down Column A in the Excel Table named "Tabel1" range A4:R211.
8. Table range is dynamic (may increase or decrease in size) currently it is A4:A211.)
9. Lookup would input value "5" in Column R (18 columns over)
10. In example would input "5" into R135
11. Once completed the orginal input value "5" on "Sheet1" would be cleared.
    
    My goal is anytime I enter in a value in "Sheet1" VBA would input value based on lookup in "Sheet 2"
    
    Thank you in advance, Kindly D

 

[Bhawani]

Please share the sample file for more clarification on data structure and requirement.
as per my understanding you will enter number in B15 in sheet1 and the corresponding value to B15 will be in A15 in sheet1.
Upon entering number you require value of A15 in sheet2 - R135.
Is it same.

 

[DDREW]

Bawani, thank you for reply. Attached is sample file. I highlighted cell in red where I would like to input on Sheet1 as well as were it should be inputed to on sheet2. The lookup keys are in Column A on Sheet 1 and Sheet 2.

 

[DDREW]

Also I would only like the VBA code to perform lookup when I enter in value in sheet 1. The reason is the keys on sheet 1 will be changing based on other inputs in workbook so I believe lookup should only be done when I activate cell.

 

[Bhawani]

Its still not clear upon entering 5 in sheet1 B1 you want value of sheet2 A1i.e. 135 in which sheet?cell? where you will get it.
i mean if 5 is input value of columnB where will be output and what will be it.
Is "LOOKUP KEY" column A header only word or it has something linked with your output

 

[Bhawani]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Changed As Range
Set Changed = Range("B15:B80")
    If Not Intersect(Target, Changed) Is Nothing Then
        col_A_val = Target.Offset(0, -1).Value
            Dim j As Integer
            j = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row
            For Each C In Sheets(2).Range("a4:a" & j)
                On Error Resume Next
                If C.Value = col_A_val Then
                C.Offset(0, 17).Value = Target.Value
                End If
            Next
    End If
End Sub

Please look into the file.
Hope it will work for you finely

 

[DDREW]

my apologies for the confusion I am not sure lookup is correct way to do this.

In Sheet 1 I would enter "5" in column B and column A would have a key
In Sheet 2 Column A all keys, vba would run down column A to match key, move over 18 columns and input "5"

 

[DDREW]

Works perfect thank you.....

 

[DDREW]

Bhawani - After I run code above I would like the input in Sheet 1, column B cleared ( not sheet2) as well as a pivot table refreshed. Can you advise?