Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Range question
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,355
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default 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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,355
Default 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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,441
Default 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


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default 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
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,355
Default 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



Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default 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
Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default 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



Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,441
Default 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





Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,355
Default 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



Reply With QuoteReply With Quote
  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,441
Default 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


Reply With QuoteReply With Quote
  #13   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default 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



Reply With QuoteReply With Quote
  #14   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,355
Default 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



Reply With QuoteReply With Quote
  #15   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default 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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Range question Jock Excel Worksheet Functions 3 March 19th 08 04:36 PM
Range Question N.F[_2_] Excel Discussion (Misc queries) 5 July 28th 07 01:05 AM
Range Question / error 1004: method Range of object Worksheet has failed Paul Excel Programming 3 April 7th 05 02:56 PM
Range question mnewnam Excel Programming 0 October 28th 04 06:51 PM
Range.Formula and Range question using Excel Automation [email protected] Excel Programming 0 September 19th 03 04:53 AM


All times are GMT +1. The time now is 09:05 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement