Worksheet Change question

[Barb Reinhardt]

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

 

[witek]

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

 

[Barb Reinhardt]

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

 

[Bob Phillips]

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)

 

[Tom Ogilvy]

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

 

[Tom Ogilvy]

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

 

[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?

 

[Barb Reinhardt]

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

 

[Tom Ogilvy]

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

Sub ResetEvents()
Application.EnableEvents = True
End Sub