Please Help, Code Locks up

[Ryan]

Can someone tell me what is wrong with this code? My attempt is what Cell C6
equals a specific object name; a range of other cells will equal the value of
another cell. I can get it to work; this issue is that is only works once.
When the program runs, it works as plan but locks up excel and I end up
having to go through "Ctrl+Alt+Delete" in order to have access to my computer
again.

Here is my code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If Range("C6") = "Object Name" Then
Range("H6").Value = Range(“Q6â€)
Range("L6").Value = Range(“R6â€)
Range("C8").Value = Range(“S6â€)
Range("H8").Value = Range(“T6â€)
Range("L8").Value = Range(“U6â€)
Range("C10").Value = Range(“V6â€)
Range("H10").Value = Range(“W6â€)
Else
Range("H6").Value = ""
Range("L6").Value = ""
Range("C8").Value = ""
Range("H8").Value = ""
Range("L8").Value = ""
Range("C10").Value = ""
Range("H10").Value = ""
End If
End With
End Sub

 

[Jim Thomlinson]

I see a couple of things with your code.
1. Your With Target statement does nothing since you never reference the
target so you can drop that line and the End With.

2. I like that you have referenced the Value on th eleft side of the equal
statements but you dont bother to do it on the right. While this is not wrong
(as Value is the default property of a range) it would be nice to see it on
the right side also.

3. The biggest thing is that your change code causes a change, which will in
turn fire the change event which will in turn cause the code to execute
ad-infinitum. You have a recursive loop. You need to disable events before
you make the change and then turn them back on again at the end.

Give this code a try...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo ErrorHandler
Application.EnableEvents = False

If Range("C6") = "Object Name" Then
Range("H6").Value = Range(“Q6â€).Value
Range("L6").Value = Range(“R6â€).Value
Range("C8").Value = Range(“S6â€).Value
Range("H8").Value = Range(“T6â€).Value
Range("L8").Value = Range(“U6â€).Value
Range("C10").Value = Range(“V6â€).Value
Range("H10").Value = Range(“W6â€).Value
Else
Range("H6").Value = ""
Range("L6").Value = ""
Range("C8").Value = ""
Range("H8").Value = ""
Range("L8").Value = ""
Range("C10").Value = ""
Range("H10").Value = ""
End If

ErrorHandler:
Application.EnableEvents = True
End Sub

 

[Ryan]

THank you for the help, that was really driving me nuts. There is still one
problem that I havent been able to get. There code

Range("H6").Value = Range("Q6").Value

doesnt work. It does work when I manually set the value.

Range("H6").Valvue = "123456"

It doesnt make since for it worked with my original formula. Please advice

 

[Ryan]

Nevermind...I figured it out.


I had to go a different way, I ended up using VLOOKUP but thank you again.