Worksheet Change question

B

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
 
W

witek

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

Barb Reinhardt

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

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)
 
T

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
 
T

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
 
B

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

Tom Ogilvy

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

Sub ResetEvents()
Application.EnableEvents = True
End Sub
 

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

Top