Solved: VBA "Lookup"

[Nec11]

Hello,

Can somebody to let me know an VBA code for Vlookup formula.
I don't wont something like:

With rCell.Offset(0, 1)
.Value = "=VLOOKUP(RC[-1],Sheet3!R2C1:R10000C2,2,FALSE)"

because this form it write in the cell formula and then the calculations are made.
Best Regards,
Nec

[mdmackillop]

Use Evaluate if you only want the value
[VBA]With rCell.Offset(0, 1)
.Value =Evaluate( "=VLOOKUP(RC[-1],Sheet3!R2C1:R10000C2,2,FALSE)")[/VBA]

[Nec11]

tryed already, but.... not working

P.S attached example in first post

[mdmackillop]

[VBA] rcel.Offset(, 1).Value = Evaluate("=VLOOKUP(B" & rcel.Row & ",Sheet3!A1:B5000,2,FALSE)")
[/VBA]

[Nec11]

Tryed you code but... still not working

[sassora]

How about removing the = sign in the evaluate function?

[mdmackillop]

Event code deleted

[Nec11]

Every time after complete column "B" I receive an message" Object Required" ?! with or without"=" sign in evaluate function

If somebody fix that code can you please attach it.
Thousand of thanks.

[Nec11]

Event code deleted

Your code seams to be OK in this form, but as you can see mine original file was made to watch the last cell from column "B" and if an value appears vlookup that value and bring the necesary information.

[mdmackillop]

[VBA]Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Sheet1.Unprotect
lastln = (Sheet2.Cells(Sheet2.Rows.Count, 1).End(xlUp).Row)
Dim rChange As Range
On Error GoTo ErrHandler
Set rChange = Intersect(Target, Range("B:B"))

If Not rChange Is Nothing Then
Application.EnableEvents = False
If Target > "" Then
For i = 1 To 8
Target.Offset(, i).Value = Evaluate("=VLOOKUP(B" & Target.Row & ",Sheet3!A1:G500," & i + 1 & ",FALSE)")
Next
End If
End If

ExitHandler:
Set rChange = Nothing
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
Sheet1.Protect

End Sub[/VBA]

[Nec11]

GREAT!!!
WORK'S LIKE CHARM

THANK YOU SO MUCH!!!