Range question

B

Barb Reinhardt

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
 
T

Tom Ogilvy

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

Ardus Petus

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

HTH
 
B

Barb Reinhardt

What do I need to change so that B2:E2 is in the same row as the data that's
been selected? Let's say I select cell A10. I want the range to be
B10:E10.

Thanks,

Tom Ogilvy said:
Range("B2:E2").FormulaR1C1 = _
"=VLOOKUP(RC1,DBExtract,MATCH(R1C,DBExtract!R[-1],0),FALSE)"

--
Regards,
Tom Ogilvy


Barb Reinhardt said:
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
Click to expand...
Click to expand...
 
B

Bernie Deitrick

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
 
T

Tom Ogilvy

Cells(ActiveCell.Row,2).Resize(1,4).FormulaR1C1 = _
"=VLOOKUP(RC1,DBExtract,MATCH(R1C,DBExtract!R[-1],0),FALSE)"

will work for the cells to contain the formula. Whether the formula needs
to be adjusted or not, I wouldn't know since I don't know your data or
intent.

--
Regards,
Tom Ogilvy

Tom Ogilvy said:
Range("B2:E2").FormulaR1C1 = _
"=VLOOKUP(RC1,DBExtract,MATCH(R1C,DBExtract!R[-1],0),FALSE)"

--
Regards,
Tom Ogilvy


Barb Reinhardt said:
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
Click to expand...
Click to expand...
 
B

Barb Reinhardt

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 said:
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 said:
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
Click to expand...
Click to expand...
 
T

Tom Ogilvy

forgot you are using an event, so

Cells(Target(1).Row,2).Resize(1,4).FormulaR1C1 = _
"=VLOOKUP(RC1,DBExtract,MATCH(R1C,DBExtract!R[-1],0),FALSE)"

--
Regards,
Tom Ogilvy

Tom Ogilvy said:
Cells(ActiveCell.Row,2).Resize(1,4).FormulaR1C1 = _
"=VLOOKUP(RC1,DBExtract,MATCH(R1C,DBExtract!R[-1],0),FALSE)"

will work for the cells to contain the formula. Whether the formula needs
to be adjusted or not, I wouldn't know since I don't know your data or
intent.

--
Regards,
Tom Ogilvy

Tom Ogilvy said:
Range("B2:E2").FormulaR1C1 = _
"=VLOOKUP(RC1,DBExtract,MATCH(R1C,DBExtract!R[-1],0),FALSE)"

--
Regards,
Tom Ogilvy


Barb Reinhardt said:
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
Click to expand...
Click to expand...
Click to expand...
 
T

Tom Ogilvy

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

--
Regards,
Tom Ogilvy


Barb Reinhardt said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
B

Bernie Deitrick

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("A1:A20")) Is Nothing Then Exit Sub

Application.EnableEvents = False
With Target.Offset(0, 1).Resize(1, 4)
.FormulaR1C1 = _
"=VLOOKUP(RC1,DBExtract,MATCH(R1C,DBExtract!R1,0),FALSE)"
.Value = .Value
End With
Application.EnableEvents = True
End Sub

HTH,
Bernie
MS Excel MVP


Barb Reinhardt said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
B

Barb Reinhardt

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 said:
Put you code in the Worksheet_Change event rather than the selection change
event.

--
Regards,
Tom Ogilvy


Barb Reinhardt said:
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 said:
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


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
Click to expand...
Click to expand...
Click to expand...
 
B

Bernie Deitrick

Put your code in the Worksheet_Change event rather than the selection change
event.
Click to expand...

But with a few changes... selection.copy, move the enableevents = true, etc...

HTH,
Bernie
MS Excel MVP

 
T

Tom Ogilvy

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 said:
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 said:
Put you code in the Worksheet_Change event rather than the selection change
event.

--
Regards,
Tom Ogilvy


Barb Reinhardt said:
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


:

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


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
Click to expand...
Click to expand...
Click to expand...
 
B

Barb Reinhardt

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 said:
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 said:
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 said:
Put you code in the Worksheet_Change event rather than the selection change
event.

--
Regards,
Tom Ogilvy


:

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


:

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


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
Click to expand...
Click to expand...
Click to expand...
 
T

Tom Ogilvy

If it's any consolation, the code (copied from your post) works perfectly for
me.

My dbExtract range starts in column 1.

--
Regards,
Tom Ogilvy


Barb Reinhardt said:
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 said:
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 said:
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
:

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

--
Regards,
Tom Ogilvy


:

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


:

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


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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Worksheet Change question 8
create a macro with the match lookup_array is changing everyday 3
"=TEXT(RC[-1],"MMM")" 4
Word macro that can call cells out of Excel for a find and replace 0
Vlookup in vba - how to use absolute rows not relative 1
Insert columns and values programmatically with IF statement 9
FormulaR1C1 Help 3
Macro 3

Top