I'm so frustrated with this. I now have this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub

If Not Intersect(Target, Range("A1:A20")) Is Nothing Then
Application.EnableEvents = False
If Target.Value < "" Then
Target.Offset(0, 1).Resize(1, 4).FormulaR1C1 = _
"=VLOOKUP(RC1,DBExtract,MATCH(R1C,DBExtract!R1,0), FALSE)"
Target.Offset(0, 1).Resize(1, 4).Copy
Target.Offset(0, 1).Resize(1, 4).PasteSpecial xlValues
Else
Target.Offset(0, 1).Resize(1, 4).Value = ""
End If
Application.EnableEvents = True
End If
End Sub

It's not doing the lookup or copy. ARGH!

Thanks,
Barb Reinhardt

"Tom Ogilvy" wrote:

if target.value = "" then


--------------------------

target.offset(0,1).Resize(1,4).copy
target.offset(0,1).Resize(1,4).Pastespecial xlValues


--
Regards,
Tom Ogilvy


"Barb Reinhardt" wrote:

Tom,

Thanks. I still have a couple of questions:

1) I want to do something if the target is blank. How would I code the IF
statement?
2) How would I select the range that has the vlookups for a subsequent
copy/paste?

Regards,
Barb Reinhardt
"Tom Ogilvy" wrote:

Put you code in the Worksheet_Change event rather than the selection change
event.

--
Regards,
Tom Ogilvy


"Barb Reinhardt" wrote:

I have this now. In A1:A20 I have a selection list that's been defined by a
named range. Whenever I select one of the cells, the macro appears to run.
I want it to run only if the cell values change. What do I need to modify.
Also, I want to copy/paste :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub

'Range("...") identifies that range that will be checked for a change
If Not Intersect(Target, Range("A1:A20")) Is Nothing Then
Application.EnableEvents = False
Target.Offset(0, 1).Resize(1, 4).FormulaR1C1 = _
"=VLOOKUP(RC1,DBExtract,MATCH(R1C,DBExtract!R1,0), FALSE)"
Application.EnableEvents = True
'need to select cells that had VLOOKUP in them and copy/paste special
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End If
End Sub


"Bernie Deitrick" wrote:

Barb,

Perhaps:

Application.EnableEvents=False
Target.Offset(0,1).Resize(1,4)..FormulaR1C1 = _
"=VLOOKUP(RC1,DBExtract,MATCH(R1C,DBExtract!R[-1],0),FALSE)"
Application.EnableEvents=True

HTH,
Bernie
MS Excel MVP


"Barb Reinhardt" wrote in message
...
I am using a worksheet selectionchange event and want to enter some values in
adjacent cells based on the entry in the selected cells. I know what I want
to enter, but need to know the code to select the offset cells. The selected
cells are in column 1. What would I need to change below to get this to be
more elegant?

I have this:

Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC1,DBExtract,MATCH(R1C,DBExtract!R[-1],0),FALSE)"
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC1,DBExtract,MATCH(R1C,DBExtract!R[-1],0),FALSE)"
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC1,DBExtract,MATCH(R1C,DBExtract!R[-1],0),FALSE)"
Range("E2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC1,DBExtract,MATCH(R1C,DBExtract!R[-1],0),FALSE)"

Thanks,
Barb Reinhardt