This is a continuation of another thread. That was getting a bit unweildy.

I have the following code:

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

I'm not seeing anything being displayed in the adjacent cells. What have I
done wrong. It worked at one time (before I added the IF TARGET.value =
""), but now I can't even get it to work at all.

Thanks,
Barb Reinhardt

Are you sure that excel recalculated formula?
Try to add .Calculate before .Copy
or don't disable events.



Barb Reinhardt wrote:
This is a continuation of another thread. That was getting a bit unweildy.

I have the following code:

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

I'm not seeing anything being displayed in the adjacent cells. What have I
done wrong. It worked at one time (before I added the IF TARGET.value =
""), but now I can't even get it to work at all.

Thanks,
Barb Reinhardt

How do I get a debug.print statement to work within this. That would help
me figure out what's going on.

"witek" wrote:

Are you sure that excel recalculated formula?
Try to add .Calculate before .Copy
or don't disable events.



Barb Reinhardt wrote:
This is a continuation of another thread. That was getting a bit unweildy.

I have the following code:

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

I'm not seeing anything being displayed in the adjacent cells. What have I
done wrong. It worked at one time (before I added the IF TARGET.value =
""), but now I can't even get it to work at all.

Thanks,
Barb Reinhardt

Barb,

It worked fine for me, but here is a (changed for better readability IMO)
version with some Debug statements in it

Private Sub Worksheet_Change(ByVal Target As Range)
Const kFormula As String = _
"=VLOOKUP(RC1,DBExtract,MATCH(R1C,DBExtract!R1,0), FALSE)"

If Target.Cells.Count 1 Then Exit Sub

If Not Intersect(Target, Range("A1:A20")) Is Nothing Then
Debug.Print "1: " & Target.Address
Application.EnableEvents = False
With Target
If .Value < "" Then
Debug.Print "2: " & .Value
With .Offset(0, 1).Resize(1, 4)
.FormulaR1C1 = kFormula
.Value = .Value
End With
Else
Debug.Print "3: clear"
.Offset(0, 1).Resize(1, 4).Value = ""
End If
End With
Application.EnableEvents = True
End If
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Barb Reinhardt" wrote in message
...
How do I get a debug.print statement to work within this. That would
help
me figure out what's going on.

"witek" wrote:

Are you sure that excel recalculated formula?
Try to add .Calculate before .Copy
or don't disable events.



Barb Reinhardt wrote:
This is a continuation of another thread. That was getting a bit
unweildy.

I have the following code:

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

I'm not seeing anything being displayed in the adjacent cells. What
have I
done wrong. It worked at one time (before I added the IF
TARGET.value =
""), but now I can't even get it to work at all.

Thanks,
Barb Reinhardt

as stated in the previous thread, copying the code from there worked
flawlessly for me.

Perhaps your code disabled events, then crashed and the events are not even
firing.

If so, run this from a general module

Sub ResetEvents()
Application.Enableevents = True
End Sub

--
Regards,
Tom Ogilvy


"Barb Reinhardt" wrote:

This is a continuation of another thread. That was getting a bit unweildy.

I have the following code:

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

I'm not seeing anything being displayed in the adjacent cells. What have I
done wrong. It worked at one time (before I added the IF TARGET.value =
""), but now I can't even get it to work at all.

Thanks,
Barb Reinhardt

Why not slow down and wait for a response.

try turning on events as I suggested.

if you want a debug.print statement, just add it

Debug.Print Target.address

--
Regards,
Tom Ogilvy


"Barb Reinhardt" wrote:

How do I get a debug.print statement to work within this. That would help
me figure out what's going on.

"witek" wrote:

Are you sure that excel recalculated formula?
Try to add .Calculate before .Copy
or don't disable events.



Barb Reinhardt wrote:
This is a continuation of another thread. That was getting a bit unweildy.

I have the following code:

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

I'm not seeing anything being displayed in the adjacent cells. What have I
done wrong. It worked at one time (before I added the IF TARGET.value =
""), but now I can't even get it to work at all.

Thanks,
Barb Reinhardt

I'm only being notified of responses that are directed to me so I'm not
getting all of them and they take a while to show up here today. I did try
adding a debug.print statement and got NOTHING. Is that related to the
application.events issue?

"Tom Ogilvy" wrote:

Why not slow down and wait for a response.

try turning on events as I suggested.

if you want a debug.print statement, just add it

Debug.Print Target.address

--
Regards,
Tom Ogilvy


"Barb Reinhardt" wrote:

How do I get a debug.print statement to work within this. That would help
me figure out what's going on.

"witek" wrote:

Are you sure that excel recalculated formula?
Try to add .Calculate before .Copy
or don't disable events.



Barb Reinhardt wrote:
This is a continuation of another thread. That was getting a bit unweildy.

I have the following code:

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

I'm not seeing anything being displayed in the adjacent cells. What have I
done wrong. It worked at one time (before I added the IF TARGET.value =
""), but now I can't even get it to work at all.

Thanks,
Barb Reinhardt

Thank you one and all. This FINALLY works. I appreciate all of your help.

"Barb Reinhardt" wrote:

This is a continuation of another thread. That was getting a bit unweildy.

I have the following code:

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

I'm not seeing anything being displayed in the adjacent cells. What have I
done wrong. It worked at one time (before I added the IF TARGET.value =
""), but now I can't even get it to work at all.

Thanks,
Barb Reinhardt

As I previously posted, you have probably disabled events. Run this from a
general module

Sub ResetEvents()
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy


"Barb Reinhardt" wrote:

I'm only being notified of responses that are directed to me so I'm not
getting all of them and they take a while to show up here today. I did try
adding a debug.print statement and got NOTHING. Is that related to the
application.events issue?

"Tom Ogilvy" wrote:

Why not slow down and wait for a response.

try turning on events as I suggested.

if you want a debug.print statement, just add it

Debug.Print Target.address

--
Regards,
Tom Ogilvy


"Barb Reinhardt" wrote:

How do I get a debug.print statement to work within this. That would help
me figure out what's going on.

"witek" wrote:

Are you sure that excel recalculated formula?
Try to add .Calculate before .Copy
or don't disable events.



Barb Reinhardt wrote:
This is a continuation of another thread. That was getting a bit unweildy.

I have the following code:

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

I'm not seeing anything being displayed in the adjacent cells. What have I
done wrong. It worked at one time (before I added the IF TARGET.value =
""), but now I can't even get it to work at all.

Thanks,
Barb Reinhardt