Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying Value from the Wrong Cell
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying Value from the Wrong Cell
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" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying Value from the Wrong Cell
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" wrote: 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" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying Value from the Wrong Cell
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" wrote: 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" wrote: 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" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying Value from the Wrong Cell
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". "Mike H" wrote: 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" wrote: 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" wrote: 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" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert Calculated Field (wrong Qty*Price = wrong Amount) | Excel Discussion (Misc queries) | |||
Autofilter/Sorting Copying Wrong records | Excel Discussion (Misc queries) | |||
Wrong Cell | Excel Discussion (Misc queries) | |||
Paste is is copying in formula, but display is wrong. | Excel Discussion (Misc queries) | |||
Paste is is copying in formula, but display is wrong. | Excel Worksheet Functions |