Place the following in a general module. Lookup value is entered on
sheet 1, B2. Vlookup is created in C2. Address of found value is stored
in D2. Table is the lookup table on sheet2. Table1 is a single column
range of the search column in the vlookup

Sub Auto_Open()
Application.Calculation = xlCalculationAutomatic
Sheets("Sheet1").OnEntry = "Auto_Routines"
End Sub
Sub Auto_Routines()
Enter_Lookup
Replace_Value
End Sub

Sub Enter_Lookup() Create vlookup and cell address of found value.
If ActiveCell.Address = "$B$2" Then
ActiveCell.Offset(0, 1).Formula = "=VLOOKUP(B2,Table,2,FALSE)"
ActiveCell.Offset(0, 2).Formula = _
"=CELL(""Address"",INDEX(Table1,MATCH(C2,table1),1 ))"
ActiveCell.Offset(0, 2).Value = ActiveCell.Offset(0, 2).Value
Else
Exit Sub
End If
End Sub

Sub Replace_Value() 'Replaces vlookup formula with replacement value.
Dim MyAddress As String
If ActiveCell.Address < "$C$2" Then
Exit Sub
Else
MyAddress = ActiveSheet.Range("D2").Value
Range(MyAddress).Value = Range("C2").Value
MyAddress = Empty
End If
End Sub


mjj047s wrote:
THEIR IS?


davegb wrote:
mjj047s wrote:
Yep - you've basically answered it. I'm looking to over write it
without acually going to the cell.

There's lots of ways to do that.



davegb wrote:
mjj047s wrote:
Can I do a Vlookup for a set of data, and change the return data?
For example - When i type in "Apples" in A1, "Red" in returned in A2
Can I manually type "Green" into A2 or somewhere to have it change from
where it was pulling from????

If i need to explain more, please let me know.

If I'm interpreting you correctly, you have a vlookup table with Apples
in say C1, Red in D1, Oranges in C2, Orange in D2, etc. When you enter
Apples in A1, the vlookup function in A2 finds Apples in A1 and returns
Red. You want to know if, by changing A2 to Green, it will change the
table value in D1 to Green. The answer is no. Doesn't work in reverse.
You'd simply overwrite your formula.

Maybe if you give some more detail, we can help you solve the problem
some other way?

Hope this helps in your world.