ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Range question (https://www.excelbanter.com/excel-programming/366831-range-question.html)

Barb Reinhardt
Range question
 
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

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

--
Regards,
Tom Ogilvy


"Barb Reinhardt" wrote:

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

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

HTH
--
AP

"Barb Reinhardt" a écrit dans le
message de news: ...
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



Barb Reinhardt
Range question
 
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" wrote:

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

--
Regards,
Tom Ogilvy


"Barb Reinhardt" wrote:

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

Bernie Deitrick
Range question
 
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



Tom Ogilvy
Range question
 


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" wrote:

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

--
Regards,
Tom Ogilvy


"Barb Reinhardt" wrote:

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

Barb Reinhardt
Range question
 
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




Tom Ogilvy
Range question
 
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" wrote:



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" wrote:

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

--
Regards,
Tom Ogilvy


"Barb Reinhardt" wrote:

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

Tom Ogilvy
Range question
 
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




Bernie Deitrick
Range question
 
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" wrote in message
...
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

Barb Reinhardt
Range question
 
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




Bernie Deitrick
Range question
 
Put your code in the Worksheet_Change event rather than the selection change
event.

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

HTH,
Bernie
MS Excel MVP


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

Tom Ogilvy
Range question
 
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




Barb Reinhardt
Range question
 
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




Tom Ogilvy
Range question
 
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" wrote:

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





All times are GMT +1. The time now is 02:55 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com