Copying Value from the Wrong Cell

[JSnow]

I'm using Excel 2003. I was given the following formual but it has a problem
which I'll explain in a second:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo EndMacro
If Not Target.HasFormula Then
Set rng = Target.Dependents
If Not Intersect(Range("C6"), rng) Is Nothing Then
If Target.Value <> "" Then
Range("D6").Value = Target.Value
End If
End If
End If
EndMacro:
End Sub

What SHOULD happen: user selects from a dropdown in cell D4 which then
generates a random item in cell C6, which the value of C6 then gets pasted to
D6. (C6 contains a random formula so in order for the data to remain
constant I need to paste it to D6.)

What ACTUALLY happens: which ever item the user selects from the dropdown in
cell D4 is what is pasted to D6. I'm guessing that the targeting in the
above formula is where this is happening, but I know very little about vba.

 

[Mike H]

Hi,

C6 contains a random formula so in order for the data to remain
constant I need to paste it to D6.)

I don't understand this bit. can we see the formula that's in C6?

Mike

 

[JSnow]

Here's the formula in that cell, Mike H:
=IF(OR(D3="",D4=""),"",IF(RANDBETWEEN(1,G3+G4)>G3,D4,D3))
Until both dropdowns in D3 and D4 are selected, C6 will remain "".

 

[Mike H]

Hi,

Your problem is that C6 isn't the target, D4 is. Try changing this line
Range("D6").Value = Target.Value

to this
Range("D6").Value = Range("C6").Value

Mike

 

[JSnow]

Mike, that works but I have to select D4 twice. Here's what's happening:

I select D4 and the random data appears in C6, let's say, "Superman". D6 is
still blank. I select D4 again, and although C6 will fill with something
new, "Batman" for example, D6 now fills with "Superman".