Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro for a one time triggered copy of cell value
I have hit a brick wall with my spreadhseet, due mainly to my lack of
experience with Macros (I didn't even know they existed until last Friday). My requirements are as follows: I have a spreadsheet that generates a random number once certain requirements are met in other cells. This I have managed to create with no real problems, but every time the spreadsheet refreshes, it changes the value of every random number in the form. I require a Macro, that triggered by the entry of a "Y" into cell A1, runs a one time Macro, that doesn't repeat, to copy the entry in Cell B1, and paste the value only into cell C1. I have tried many approaches to this issue, and none have worked in the way I want it to. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro for a one time triggered copy of cell value
Hi,
Right click your sheet tab, view code and paste this in Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Target.Address = "$A$1" Then If UCase(Target.Value) = "Y" Then Application.EnableEvents = False Range("B1").Copy Range("C1").PasteSpecial Paste:=xlPasteValues Application.EnableEvents = True End If End If End Sub Mike "Azrael" wrote: I have hit a brick wall with my spreadhseet, due mainly to my lack of experience with Macros (I didn't even know they existed until last Friday). My requirements are as follows: I have a spreadsheet that generates a random number once certain requirements are met in other cells. This I have managed to create with no real problems, but every time the spreadsheet refreshes, it changes the value of every random number in the form. I require a Macro, that triggered by the entry of a "Y" into cell A1, runs a one time Macro, that doesn't repeat, to copy the entry in Cell B1, and paste the value only into cell C1. I have tried many approaches to this issue, and none have worked in the way I want it to. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro for a one time triggered copy of cell value
The following code must be placed in the VBA sheet where you hav ethe data.
What do you mean by doesn't repeat? You can test if cell C1 is empty and only move the data when C1 is empty. You can clear cell C1 with a worksheet open. What I would dois generate the random number in the macro. Sub worksheet_change(ByVal target As Range) Set isect = Application.Intersect(target, Range("A1")) If Not isect Is Nothing Then If target.Value = "Y" Then Range("C1").Value = Range("B1").Value End If End If End Sub "Azrael" wrote: I have hit a brick wall with my spreadhseet, due mainly to my lack of experience with Macros (I didn't even know they existed until last Friday). My requirements are as follows: I have a spreadsheet that generates a random number once certain requirements are met in other cells. This I have managed to create with no real problems, but every time the spreadsheet refreshes, it changes the value of every random number in the form. I require a Macro, that triggered by the entry of a "Y" into cell A1, runs a one time Macro, that doesn't repeat, to copy the entry in Cell B1, and paste the value only into cell C1. I have tried many approaches to this issue, and none have worked in the way I want it to. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro for a one time triggered copy of cell value
Thank you for both of your suggestions, but for some reason neither seem to
work. Allow me to clarify a bit more, as I have a sneaking suspicion that the formula in A2 may have an effect on the macro if they are "firing" at different times. Cell A2 has the formula =if(E2="Y",randbetween(100,999),"") The intention is for the Y in E2, which is created by another formula connected to several other cells, to trigger teh random number in A2, and then for the value to be copied across into C2, preventing refreshing from affecting the number in that cell. (unless the Y in E2 dissapears and then reappears, indicating an adjustment) When I mentioned earlier, "without repeating" i meant that when I had tried a macro earlier, it found the Y, pasted the value, adn then started again, because the Y was there. I only want the Macro to run once the value arrives in A2. As a side note, am I approaching this from the wrong angle? Would the result be the same if the appearance of teh random number in cell A2 was the trigger for the macro? Or would this be activated every time the number changed? "Joel" wrote: The following code must be placed in the VBA sheet where you hav ethe data. What do you mean by doesn't repeat? You can test if cell C1 is empty and only move the data when C1 is empty. You can clear cell C1 with a worksheet open. What I would dois generate the random number in the macro. Sub worksheet_change(ByVal target As Range) Set isect = Application.Intersect(target, Range("A1")) If Not isect Is Nothing Then If target.Value = "Y" Then Range("C1").Value = Range("B1").Value End If End If End Sub "Azrael" wrote: I have hit a brick wall with my spreadhseet, due mainly to my lack of experience with Macros (I didn't even know they existed until last Friday). My requirements are as follows: I have a spreadsheet that generates a random number once certain requirements are met in other cells. This I have managed to create with no real problems, but every time the spreadsheet refreshes, it changes the value of every random number in the form. I require a Macro, that triggered by the entry of a "Y" into cell A1, runs a one time Macro, that doesn't repeat, to copy the entry in Cell B1, and paste the value only into cell C1. I have tried many approaches to this issue, and none have worked in the way I want it to. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro for a one time triggered copy of cell value
Remove the number of C1 then run this macro. I'm not using the random number
in B1. Sub worksheet_change(ByVal target As Range) Set isect = Application.Intersect(target, Range("A1")) If Not isect Is Nothing Then If target.Value = "Y" Then Range("C1").Value = Rnd() End If End If End Sub "Joel" wrote: The following code must be placed in the VBA sheet where you hav ethe data. What do you mean by doesn't repeat? You can test if cell C1 is empty and only move the data when C1 is empty. You can clear cell C1 with a worksheet open. What I would dois generate the random number in the macro. Sub worksheet_change(ByVal target As Range) Set isect = Application.Intersect(target, Range("A1")) If Not isect Is Nothing Then If target.Value = "Y" Then Range("C1").Value = Range("B1").Value End If End If End Sub "Azrael" wrote: I have hit a brick wall with my spreadhseet, due mainly to my lack of experience with Macros (I didn't even know they existed until last Friday). My requirements are as follows: I have a spreadsheet that generates a random number once certain requirements are met in other cells. This I have managed to create with no real problems, but every time the spreadsheet refreshes, it changes the value of every random number in the form. I require a Macro, that triggered by the entry of a "Y" into cell A1, runs a one time Macro, that doesn't repeat, to copy the entry in Cell B1, and paste the value only into cell C1. I have tried many approaches to this issue, and none have worked in the way I want it to. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro for a one time triggered copy of cell value
I made a tweak and it works perfectly!! Now I have one question to follow,
which may seem mediocre, nut as I said, I'm new to this. This is working perfectly for one row. Now I need to create a similar macro for each row in the form.(up to 50) To ensure that they won't interfere with each other. is there some way of saving the macro's to the form under a custom name for each row, because I find that when I attempt to create a macro for the next row, it states that I have an ambigous name in worksheet_change, and if I change it, the macro won't trigger properly. Is this only because it is used in the same window? "Joel" wrote: Remove the number of C1 then run this macro. I'm not using the random number in B1. Sub worksheet_change(ByVal target As Range) Set isect = Application.Intersect(target, Range("A1")) If Not isect Is Nothing Then If target.Value = "Y" Then Range("C1").Value = Rnd() End If End If End Sub "Joel" wrote: The following code must be placed in the VBA sheet where you hav ethe data. What do you mean by doesn't repeat? You can test if cell C1 is empty and only move the data when C1 is empty. You can clear cell C1 with a worksheet open. What I would dois generate the random number in the macro. Sub worksheet_change(ByVal target As Range) Set isect = Application.Intersect(target, Range("A1")) If Not isect Is Nothing Then If target.Value = "Y" Then Range("C1").Value = Range("B1").Value End If End If End Sub "Azrael" wrote: I have hit a brick wall with my spreadhseet, due mainly to my lack of experience with Macros (I didn't even know they existed until last Friday). My requirements are as follows: I have a spreadsheet that generates a random number once certain requirements are met in other cells. This I have managed to create with no real problems, but every time the spreadsheet refreshes, it changes the value of every random number in the form. I require a Macro, that triggered by the entry of a "Y" into cell A1, runs a one time Macro, that doesn't repeat, to copy the entry in Cell B1, and paste the value only into cell C1. I have tried many approaches to this issue, and none have worked in the way I want it to. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro for a one time triggered copy of cell value
See if this works. I change the Intersect Range and I'm using Offset from
the target location to place the random number Sub worksheet_change(ByVal target As Range) Set isect = Application.Intersect(target, Range("A1:A50")) If Not isect Is Nothing Then If target.Value = "Y" Then Target.Offset(0,2).Value = Rnd() End If End If End Sub "Azrael" wrote: I made a tweak and it works perfectly!! Now I have one question to follow, which may seem mediocre, nut as I said, I'm new to this. This is working perfectly for one row. Now I need to create a similar macro for each row in the form.(up to 50) To ensure that they won't interfere with each other. is there some way of saving the macro's to the form under a custom name for each row, because I find that when I attempt to create a macro for the next row, it states that I have an ambigous name in worksheet_change, and if I change it, the macro won't trigger properly. Is this only because it is used in the same window? "Joel" wrote: Remove the number of C1 then run this macro. I'm not using the random number in B1. Sub worksheet_change(ByVal target As Range) Set isect = Application.Intersect(target, Range("A1")) If Not isect Is Nothing Then If target.Value = "Y" Then Range("C1").Value = Rnd() End If End If End Sub "Joel" wrote: The following code must be placed in the VBA sheet where you hav ethe data. What do you mean by doesn't repeat? You can test if cell C1 is empty and only move the data when C1 is empty. You can clear cell C1 with a worksheet open. What I would dois generate the random number in the macro. Sub worksheet_change(ByVal target As Range) Set isect = Application.Intersect(target, Range("A1")) If Not isect Is Nothing Then If target.Value = "Y" Then Range("C1").Value = Range("B1").Value End If End If End Sub "Azrael" wrote: I have hit a brick wall with my spreadhseet, due mainly to my lack of experience with Macros (I didn't even know they existed until last Friday). My requirements are as follows: I have a spreadsheet that generates a random number once certain requirements are met in other cells. This I have managed to create with no real problems, but every time the spreadsheet refreshes, it changes the value of every random number in the form. I require a Macro, that triggered by the entry of a "Y" into cell A1, runs a one time Macro, that doesn't repeat, to copy the entry in Cell B1, and paste the value only into cell C1. I have tried many approaches to this issue, and none have worked in the way I want it to. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro for a one time triggered copy of cell value
Thank you both for your help in this matter. Using a combination of your
suggestions, and other things I had found lying about in the forum, I managed to construct the following macro script which does exactly what I wanted . Sub worksheet_change(byval target as range) set isect = application.intersect(target, range("A1:A51")) If not isect is nothing then Target.offset(0, 1).copy Target.offset(0, 2).pastespecial xlpastevalues application.cutcopymode = false end if end if end sub "Joel" wrote: See if this works. I change the Intersect Range and I'm using Offset from the target location to place the random number Sub worksheet_change(ByVal target As Range) Set isect = Application.Intersect(target, Range("A1:A50")) If Not isect Is Nothing Then If target.Value = "Y" Then Target.Offset(0,2).Value = Rnd() End If End If End Sub "Azrael" wrote: I made a tweak and it works perfectly!! Now I have one question to follow, which may seem mediocre, nut as I said, I'm new to this. This is working perfectly for one row. Now I need to create a similar macro for each row in the form.(up to 50) To ensure that they won't interfere with each other. is there some way of saving the macro's to the form under a custom name for each row, because I find that when I attempt to create a macro for the next row, it states that I have an ambigous name in worksheet_change, and if I change it, the macro won't trigger properly. Is this only because it is used in the same window? "Joel" wrote: Remove the number of C1 then run this macro. I'm not using the random number in B1. Sub worksheet_change(ByVal target As Range) Set isect = Application.Intersect(target, Range("A1")) If Not isect Is Nothing Then If target.Value = "Y" Then Range("C1").Value = Rnd() End If End If End Sub "Joel" wrote: The following code must be placed in the VBA sheet where you hav ethe data. What do you mean by doesn't repeat? You can test if cell C1 is empty and only move the data when C1 is empty. You can clear cell C1 with a worksheet open. What I would dois generate the random number in the macro. Sub worksheet_change(ByVal target As Range) Set isect = Application.Intersect(target, Range("A1")) If Not isect Is Nothing Then If target.Value = "Y" Then Range("C1").Value = Range("B1").Value End If End If End Sub "Azrael" wrote: I have hit a brick wall with my spreadhseet, due mainly to my lack of experience with Macros (I didn't even know they existed until last Friday). My requirements are as follows: I have a spreadsheet that generates a random number once certain requirements are met in other cells. This I have managed to create with no real problems, but every time the spreadsheet refreshes, it changes the value of every random number in the form. I require a Macro, that triggered by the entry of a "Y" into cell A1, runs a one time Macro, that doesn't repeat, to copy the entry in Cell B1, and paste the value only into cell C1. I have tried many approaches to this issue, and none have worked in the way I want it to. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro for a one time triggered copy of cell value
Not sure why you want to use the worksheet randomize function. But that is
ok. He is a simple change from: Target.offset(0, 1).copy Target.offset(0, 2).pastespecial xlpastevalues to: Target.offset(0, 2).value = Target.offset(0, 1).value "Azrael" wrote: Thank you both for your help in this matter. Using a combination of your suggestions, and other things I had found lying about in the forum, I managed to construct the following macro script which does exactly what I wanted . Sub worksheet_change(byval target as range) set isect = application.intersect(target, range("A1:A51")) If not isect is nothing then Target.offset(0, 1).copy Target.offset(0, 2).pastespecial xlpastevalues application.cutcopymode = false end if end if end sub "Joel" wrote: See if this works. I change the Intersect Range and I'm using Offset from the target location to place the random number Sub worksheet_change(ByVal target As Range) Set isect = Application.Intersect(target, Range("A1:A50")) If Not isect Is Nothing Then If target.Value = "Y" Then Target.Offset(0,2).Value = Rnd() End If End If End Sub "Azrael" wrote: I made a tweak and it works perfectly!! Now I have one question to follow, which may seem mediocre, nut as I said, I'm new to this. This is working perfectly for one row. Now I need to create a similar macro for each row in the form.(up to 50) To ensure that they won't interfere with each other. is there some way of saving the macro's to the form under a custom name for each row, because I find that when I attempt to create a macro for the next row, it states that I have an ambigous name in worksheet_change, and if I change it, the macro won't trigger properly. Is this only because it is used in the same window? "Joel" wrote: Remove the number of C1 then run this macro. I'm not using the random number in B1. Sub worksheet_change(ByVal target As Range) Set isect = Application.Intersect(target, Range("A1")) If Not isect Is Nothing Then If target.Value = "Y" Then Range("C1").Value = Rnd() End If End If End Sub "Joel" wrote: The following code must be placed in the VBA sheet where you hav ethe data. What do you mean by doesn't repeat? You can test if cell C1 is empty and only move the data when C1 is empty. You can clear cell C1 with a worksheet open. What I would dois generate the random number in the macro. Sub worksheet_change(ByVal target As Range) Set isect = Application.Intersect(target, Range("A1")) If Not isect Is Nothing Then If target.Value = "Y" Then Range("C1").Value = Range("B1").Value End If End If End Sub "Azrael" wrote: I have hit a brick wall with my spreadhseet, due mainly to my lack of experience with Macros (I didn't even know they existed until last Friday). My requirements are as follows: I have a spreadsheet that generates a random number once certain requirements are met in other cells. This I have managed to create with no real problems, but every time the spreadsheet refreshes, it changes the value of every random number in the form. I require a Macro, that triggered by the entry of a "Y" into cell A1, runs a one time Macro, that doesn't repeat, to copy the entry in Cell B1, and paste the value only into cell C1. I have tried many approaches to this issue, and none have worked in the way I want it to. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy cell with macro and increment down each time | Excel Worksheet Functions | |||
Automatic copy triggered by change in date | Excel Worksheet Functions | |||
Conditional formatting a full row triggered by a single cell | Excel Worksheet Functions | |||
Macro triggered by an event | Excel Discussion (Misc queries) | |||
formula coping 5 consecutive cell triggered by a value from anoth. | Excel Discussion (Misc queries) |