Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
automated copy & paste
hello again, i'm trying to get this to work:-
A1 = date ( changeable ) A2 = £ ( changeable ) B1 to B100 ( if B1 has £ in it move to next cell down without over righting previous cell) B100 could be greater , just depends on how much data i collect. up to now i can only get data by copy and pasting information from A2 and placing it in the next empty available cell in B. i've tryed using the date but all cells then change to the current contents of A2 and really want previous contants to remain untouched. Thanks inadvance |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
automated copy & paste
What you wrote is very difficult to follow. You must understand that of all
of us who read and write in these newsgroups, you are the only one who understands what you have and what you are wanting to do. Explain it to us as you would explain it to someone who just walked in off the street and knows nothing. HTH Otto "DarkNight" wrote in message ... hello again, i'm trying to get this to work:- A1 = date ( changeable ) A2 = £ ( changeable ) B1 to B100 ( if B1 has £ in it move to next cell down without over righting previous cell) B100 could be greater , just depends on how much data i collect. up to now i can only get data by copy and pasting information from A2 and placing it in the next empty available cell in B. i've tryed using the date but all cells then change to the current contents of A2 and really want previous contants to remain untouched. Thanks inadvance |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
automated copy & paste
If I follow you correctly and you want to add the data in A2 to the bottom
of a list of data in Column B then right click on the sheet tab and select View Code and paste this Worksheet code into the module: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub If Target.Value = "" Then Exit Sub Application.EnableEvents = False LastRow = Cells(Rows.Count, 2).End(xlUp).Row + 1 Cells(LastRow, 2).Value = Cells(2, 1).Value Application.EnableEvents = True End Sub -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "DarkNight" wrote in message ... hello again, i'm trying to get this to work:- A1 = date ( changeable ) A2 = £ ( changeable ) B1 to B100 ( if B1 has £ in it move to next cell down without over righting previous cell) B100 could be greater , just depends on how much data i collect. up to now i can only get data by copy and pasting information from A2 and placing it in the next empty available cell in B. i've tryed using the date but all cells then change to the current contents of A2 and really want previous contants to remain untouched. Thanks inadvance |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
automated copy & paste
thanks for your help Sandy Mann,
i forgot to add another cell Colum C = date so is there any way you can only get it to put information in the next available cell if the 2 date match. other wise it works just like i want it to. Is there an easy way for newbies to enter a formula rather than entering a code wich means nothing to me. sorry to be a pain "Sandy Mann" wrote: If I follow you correctly and you want to add the data in A2 to the bottom of a list of data in Column B then right click on the sheet tab and select View Code and paste this Worksheet code into the module: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub If Target.Value = "" Then Exit Sub Application.EnableEvents = False LastRow = Cells(Rows.Count, 2).End(xlUp).Row + 1 Cells(LastRow, 2).Value = Cells(2, 1).Value Application.EnableEvents = True End Sub -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "DarkNight" wrote in message ... hello again, i'm trying to get this to work:- A1 = date ( changeable ) A2 = £ ( changeable ) B1 to B100 ( if B1 has £ in it move to next cell down without over righting previous cell) B100 could be greater , just depends on how much data i collect. up to now i can only get data by copy and pasting information from A2 and placing it in the next empty available cell in B. i've tryed using the date but all cells then change to the current contents of A2 and really want previous contants to remain untouched. Thanks inadvance |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
automated copy & paste
mmm ok i'll try to explain abit more, but thanks anyway for your responces so
far. the problem i have is i'm trying to keep track of how much extra £'s i make in a given week, fortnight, month therefore A1 = current date A2 = extra £'s i've made so far, Column C = date of next pay starting with 1st empty cell in C then adding 1 cell down at a time. so if A1 = 1 day less than the date in Column C or then put in 1st empty cell in Column B and so on so lets say C1 = the 1st date and A1 = 1 day less than C1 then put contents of A2 in B1 then repeat all over again hope this helps. thopught this was gonna be hard to do as every attempt i've don it changes every cell value in colum C to the same value "Sandy Mann" wrote: The problem with trying to do what you want with only formulas is that formulas are volatile inasmuch as that they will recalculate when the dependent cells change. For example in B2: =IF(C2=$A$1,$A$2,"") will return the contents of A2 when the date in A1 is the same as the date in C2. However, as soon as you change either A1 or A2 then B2 will change either to an empty string if you change A1 or the new value if you change A2. The way to stop this is to copy the value in that cell and paste it back using Paste Special but that is more trouble then simple entering the value manually. You could get an Event Macro to do it automatically when you make a change to A1 or A2 but that is a poor solution which brings other problems. I think that the best thing would be, like Otto said, if you were to try to explain to us as fully as you can, what it is that you are trying to do. -- Regards Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "DarkNight" wrote in message ... thanks for your help Sandy Mann, i forgot to add another cell Colum C = date so is there any way you can only get it to put information in the next available cell if the 2 date match. other wise it works just like i want it to. Is there an easy way for newbies to enter a formula rather than entering a code wich means nothing to me. sorry to be a pain "Sandy Mann" wrote: If I follow you correctly and you want to add the data in A2 to the bottom of a list of data in Column B then right click on the sheet tab and select View Code and paste this Worksheet code into the module: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub If Target.Value = "" Then Exit Sub Application.EnableEvents = False LastRow = Cells(Rows.Count, 2).End(xlUp).Row + 1 Cells(LastRow, 2).Value = Cells(2, 1).Value Application.EnableEvents = True End Sub -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "DarkNight" wrote in message ... hello again, i'm trying to get this to work:- A1 = date ( changeable ) A2 = £ ( changeable ) B1 to B100 ( if B1 has £ in it move to next cell down without over righting previous cell) B100 could be greater , just depends on how much data i collect. up to now i can only get data by copy and pasting information from A2 and placing it in the next empty available cell in B. i'vetryed tryed using the date but all cells then change to the current contents of A2 and really want previous contants to remain untouched. Thanks inadvance |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
automated copy & paste
Hi Dark Night,
A further couple of questions - If the date in C1, (or can it be further down Column C?), is the same as the date in A1 and there are three entries down Column B, where do you want to have the data in A2 to go to? If it helps you to explain what you want then by all means send me a sample sheet. Just replace the part form the @ as it says in my signatute -- Rrgards Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "DarkNight" wrote in message ... mmm ok i'll try to explain abit more, but thanks anyway for your responces so far. the problem i have is i'm trying to keep track of how much extra £'s i make in a given week, fortnight, month therefore A1 = current date A2 = extra £'s i've made so far, Column C = date of next pay starting with 1st empty cell in C then adding 1 cell down at a time. so if A1 = 1 day less than the date in Column C or then put in 1st empty cell in Column B and so on so lets say C1 = the 1st date and A1 = 1 day less than C1 then put contents of A2 in B1 then repeat all over again hope this helps. thopught this was gonna be hard to do as every attempt i've don it changes every cell value in colum C to the same value "Sandy Mann" wrote: The problem with trying to do what you want with only formulas is that formulas are volatile inasmuch as that they will recalculate when the dependent cells change. For example in B2: =IF(C2=$A$1,$A$2,"") will return the contents of A2 when the date in A1 is the same as the date in C2. However, as soon as you change either A1 or A2 then B2 will change either to an empty string if you change A1 or the new value if you change A2. The way to stop this is to copy the value in that cell and paste it back using Paste Special but that is more trouble then simple entering the value manually. You could get an Event Macro to do it automatically when you make a change to A1 or A2 but that is a poor solution which brings other problems. I think that the best thing would be, like Otto said, if you were to try to explain to us as fully as you can, what it is that you are trying to do. -- Regards Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "DarkNight" wrote in message ... thanks for your help Sandy Mann, i forgot to add another cell Colum C = date so is there any way you can only get it to put information in the next available cell if the 2 date match. other wise it works just like i want it to. Is there an easy way for newbies to enter a formula rather than entering a code wich means nothing to me. sorry to be a pain "Sandy Mann" wrote: If I follow you correctly and you want to add the data in A2 to the bottom of a list of data in Column B then right click on the sheet tab and select View Code and paste this Worksheet code into the module: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub If Target.Value = "" Then Exit Sub Application.EnableEvents = False LastRow = Cells(Rows.Count, 2).End(xlUp).Row + 1 Cells(LastRow, 2).Value = Cells(2, 1).Value Application.EnableEvents = True End Sub -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "DarkNight" wrote in message ... hello again, i'm trying to get this to work:- A1 = date ( changeable ) A2 = £ ( changeable ) B1 to B100 ( if B1 has £ in it move to next cell down without over righting previous cell) B100 could be greater , just depends on how much data i collect. up to now i can only get data by copy and pasting information from A2 and placing it in the next empty available cell in B. i'vetryed tryed using the date but all cells then change to the current contents of A2 and really want previous contants to remain untouched. Thanks inadvance |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
automated copy & paste
cheers Sandy Mann sending you a copy now. "Sandy Mann" wrote: Hi Dark Night, A further couple of questions - If the date in C1, (or can it be further down Column C?), is the same as the date in A1 and there are three entries down Column B, where do you want to have the data in A2 to go to? If it helps you to explain what you want then by all means send me a sample sheet. Just replace the part form the @ as it says in my signatute -- Rrgards Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "DarkNight" wrote in message ... mmm ok i'll try to explain abit more, but thanks anyway for your responces so far. the problem i have is i'm trying to keep track of how much extra £'s i make in a given week, fortnight, month therefore A1 = current date A2 = extra £'s i've made so far, Column C = date of next pay starting with 1st empty cell in C then adding 1 cell down at a time. so if A1 = 1 day less than the date in Column C or then put in 1st empty cell in Column B and so on so lets say C1 = the 1st date and A1 = 1 day less than C1 then put contents of A2 in B1 then repeat all over again hope this helps. thopught this was gonna be hard to do as every attempt i've don it changes every cell value in colum C to the same value "Sandy Mann" wrote: The problem with trying to do what you want with only formulas is that formulas are volatile inasmuch as that they will recalculate when the dependent cells change. For example in B2: =IF(C2=$A$1,$A$2,"") will return the contents of A2 when the date in A1 is the same as the date in C2. However, as soon as you change either A1 or A2 then B2 will change either to an empty string if you change A1 or the new value if you change A2. The way to stop this is to copy the value in that cell and paste it back using Paste Special but that is more trouble then simple entering the value manually. You could get an Event Macro to do it automatically when you make a change to A1 or A2 but that is a poor solution which brings other problems. I think that the best thing would be, like Otto said, if you were to try to explain to us as fully as you can, what it is that you are trying to do. -- Regards Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "DarkNight" wrote in message ... thanks for your help Sandy Mann, i forgot to add another cell Colum C = date so is there any way you can only get it to put information in the next available cell if the 2 date match. other wise it works just like i want it to. Is there an easy way for newbies to enter a formula rather than entering a code wich means nothing to me. sorry to be a pain "Sandy Mann" wrote: If I follow you correctly and you want to add the data in A2 to the bottom of a list of data in Column B then right click on the sheet tab and select View Code and paste this Worksheet code into the module: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub If Target.Value = "" Then Exit Sub Application.EnableEvents = False LastRow = Cells(Rows.Count, 2).End(xlUp).Row + 1 Cells(LastRow, 2).Value = Cells(2, 1).Value Application.EnableEvents = True End Sub -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "DarkNight" wrote in message ... hello again, i'm trying to get this to work:- A1 = date ( changeable ) A2 = £ ( changeable ) B1 to B100 ( if B1 has £ in it move to next cell down without over righting previous cell) B100 could be greater , just depends on how much data i collect. up to now i can only get data by copy and pasting information from A2 and placing it in the next empty available cell in B. i'vetryed tryed using the date but all cells then change to the current contents of A2 and really want previous contants to remain untouched. Thanks inadvance |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
automated copy & paste
Nothing has arrived yet Dark Night - would you like to try again and I will
have a look tomorrow -- Regards Sandy In Perth, the ancient capital of Scotland Replace @mailinator.com with @tiscali.co.uk "DarkNight" wrote in message ... cheers Sandy Mann sending you a copy now. "Sandy Mann" wrote: Hi Dark Night, A further couple of questions - If the date in C1, (or can it be further down Column C?), is the same as the date in A1 and there are three entries down Column B, where do you want to have the data in A2 to go to? If it helps you to explain what you want then by all means send me a sample sheet. Just replace the part form the @ as it says in my signatute -- Rrgards Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "DarkNight" wrote in message ... mmm ok i'll try to explain abit more, but thanks anyway for your responces so far. the problem i have is i'm trying to keep track of how much extra £'s i make in a given week, fortnight, month therefore A1 = current date A2 = extra £'s i've made so far, Column C = date of next pay starting with 1st empty cell in C then adding 1 cell down at a time. so if A1 = 1 day less than the date in Column C or then put in 1st empty cell in Column B and so on so lets say C1 = the 1st date and A1 = 1 day less than C1 then put contents of A2 in B1 then repeat all over again hope this helps. thopught this was gonna be hard to do as every attempt i've don it changes every cell value in colum C to the same value "Sandy Mann" wrote: The problem with trying to do what you want with only formulas is that formulas are volatile inasmuch as that they will recalculate when the dependent cells change. For example in B2: =IF(C2=$A$1,$A$2,"") will return the contents of A2 when the date in A1 is the same as the date in C2. However, as soon as you change either A1 or A2 then B2 will change either to an empty string if you change A1 or the new value if you change A2. The way to stop this is to copy the value in that cell and paste it back using Paste Special but that is more trouble then simple entering the value manually. You could get an Event Macro to do it automatically when you make a change to A1 or A2 but that is a poor solution which brings other problems. I think that the best thing would be, like Otto said, if you were to try to explain to us as fully as you can, what it is that you are trying to do. -- Regards Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "DarkNight" wrote in message ... thanks for your help Sandy Mann, i forgot to add another cell Colum C = date so is there any way you can only get it to put information in the next available cell if the 2 date match. other wise it works just like i want it to. Is there an easy way for newbies to enter a formula rather than entering a code wich means nothing to me. sorry to be a pain "Sandy Mann" wrote: If I follow you correctly and you want to add the data in A2 to the bottom of a list of data in Column B then right click on the sheet tab and select View Code and paste this Worksheet code into the module: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub If Target.Value = "" Then Exit Sub Application.EnableEvents = False LastRow = Cells(Rows.Count, 2).End(xlUp).Row + 1 Cells(LastRow, 2).Value = Cells(2, 1).Value Application.EnableEvents = True End Sub -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "DarkNight" wrote in message ... hello again, i'm trying to get this to work:- A1 = date ( changeable ) A2 = £ ( changeable ) B1 to B100 ( if B1 has £ in it move to next cell down without over righting previous cell) B100 could be greater , just depends on how much data i collect. up to now i can only get data by copy and pasting information from A2 and placing it in the next empty available cell in B. i'vetryed tryed using the date but all cells then change to the current contents of A2 and really want previous contants to remain untouched. Thanks inadvance |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
automated copy & paste
Thanks for the code Sandy Mann everything works fine,
just 1 little question, can this code be added to take more than 1 cell (26,9) be copyed to column P. if so other cells to include would be:- cell(26,10) up to cell (26,12) and tranasfer information to cell (x,16) up to (x,19) ? think this is the bit i'm refering to... for x = 4 to endrow if cells (2,1).value< cells(x+1,15).value2 and_ cells(2(1).value2 = cells(x,15).value2 then cells(x,16).value = cells(26,9).value goto getout "Sandy Mann" wrote: Nothing has arrived yet Dark Night - would you like to try again and I will have a look tomorrow -- Regards Sandy In Perth, the ancient capital of Scotland Replace @mailinator.com with @tiscali.co.uk "DarkNight" wrote in message ... cheers Sandy Mann sending you a copy now. "Sandy Mann" wrote: Hi Dark Night, A further couple of questions - If the date in C1, (or can it be further down Column C?), is the same as the date in A1 and there are three entries down Column B, where do you want to have the data in A2 to go to? If it helps you to explain what you want then by all means send me a sample sheet. Just replace the part form the @ as it says in my signatute -- Rrgards Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "DarkNight" wrote in message ... mmm ok i'll try to explain abit more, but thanks anyway for your responces so far. the problem i have is i'm trying to keep track of how much extra £'s i make in a given week, fortnight, month therefore A1 = current date A2 = extra £'s i've made so far, Column C = date of next pay starting with 1st empty cell in C then adding 1 cell down at a time. so if A1 = 1 day less than the date in Column C or then put in 1st empty cell in Column B and so on so lets say C1 = the 1st date and A1 = 1 day less than C1 then put contents of A2 in B1 then repeat all over again hope this helps. thopught this was gonna be hard to do as every attempt i've don it changes every cell value in colum C to the same value "Sandy Mann" wrote: The problem with trying to do what you want with only formulas is that formulas are volatile inasmuch as that they will recalculate when the dependent cells change. For example in B2: =IF(C2=$A$1,$A$2,"") will return the contents of A2 when the date in A1 is the same as the date in C2. However, as soon as you change either A1 or A2 then B2 will change either to an empty string if you change A1 or the new value if you change A2. The way to stop this is to copy the value in that cell and paste it back using Paste Special but that is more trouble then simple entering the value manually. You could get an Event Macro to do it automatically when you make a change to A1 or A2 but that is a poor solution which brings other problems. I think that the best thing would be, like Otto said, if you were to try to explain to us as fully as you can, what it is that you are trying to do. -- Regards Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "DarkNight" wrote in message ... thanks for your help Sandy Mann, i forgot to add another cell Colum C = date so is there any way you can only get it to put information in the next available cell if the 2 date match. other wise it works just like i want it to. Is there an easy way for newbies to enter a formula rather than entering a code wich means nothing to me. sorry to be a pain "Sandy Mann" wrote: If I follow you correctly and you want to add the data in A2 to the bottom of a list of data in Column B then right click on the sheet tab and select View Code and paste this Worksheet code into the module: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub If Target.Value = "" Then Exit Sub Application.EnableEvents = False LastRow = Cells(Rows.Count, 2).End(xlUp).Row + 1 Cells(LastRow, 2).Value = Cells(2, 1).Value Application.EnableEvents = True End Sub -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "DarkNight" wrote in message ... hello again, i'm trying to get this to work:- A1 = date ( changeable ) A2 = £ ( changeable ) B1 to B100 ( if B1 has £ in it move to next cell down without over righting previous cell) B100 could be greater , just depends on how much data i collect. up to now i can only get data by copy and pasting information from A2 and placing it in the next empty available cell in B. i'vetryed tryed using the date but all cells then change to the current contents of A2 and really want previous contants to remain untouched. Thanks inadvance |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
automated copy & paste
Hi DarkNight,
Assuming that you are becoming confused by my use of x and you mean to copy your 'Difference' cell and the three cells to the right of it to the three cells to the 'Extra £ Made' and the three cells to the right replace the line: cells(x,16).value = cells(26,9).value with: Range(Cells(x, 16), Cells(x, 19)).Value = _ Range(Cells(26, 9), Cells(26, 12)).Value Note that this is all one line with a line break after the Value= created by a space follows by an underscore. Probably the easiest thing to do is to copy that line from this post, highlight the line to be replace in the code and paste the new line in. If I've got you requirements wrong then paste back. -- HTH Sandy In Perth, the ancient capital of Scotland Replace @mailinator.com with @tiscali.co.uk "DarkNight" wrote in message ... Thanks for the code Sandy Mann everything works fine, just 1 little question, can this code be added to take more than 1 cell (26,9) be copyed to column P. if so other cells to include would be:- cell(26,10) up to cell (26,12) and tranasfer information to cell (x,16) up to (x,19) ? think this is the bit i'm refering to... for x = 4 to endrow if cells (2,1).value< cells(x+1,15).value2 and_ cells(2(1).value2 = cells(x,15).value2 then cells(x,16).value = cells(26,9).value goto getout "Sandy Mann" wrote: Nothing has arrived yet Dark Night - would you like to try again and I will have a look tomorrow -- Regards Sandy In Perth, the ancient capital of Scotland Replace @mailinator.com with @tiscali.co.uk "DarkNight" wrote in message ... cheers Sandy Mann sending you a copy now. "Sandy Mann" wrote: Hi Dark Night, A further couple of questions - If the date in C1, (or can it be further down Column C?), is the same as the date in A1 and there are three entries down Column B, where do you want to have the data in A2 to go to? If it helps you to explain what you want then by all means send me a sample sheet. Just replace the part form the @ as it says in my signatute -- Rrgards Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "DarkNight" wrote in message ... mmm ok i'll try to explain abit more, but thanks anyway for your responces so far. the problem i have is i'm trying to keep track of how much extra £'s i make in a given week, fortnight, month therefore A1 = current date A2 = extra £'s i've made so far, Column C = date of next pay starting with 1st empty cell in C then adding 1 cell down at a time. so if A1 = 1 day less than the date in Column C or then put in 1st empty cell in Column B and so on so lets say C1 = the 1st date and A1 = 1 day less than C1 then put contents of A2 in B1 then repeat all over again hope this helps. thopught this was gonna be hard to do as every attempt i've don it changes every cell value in colum C to the same value "Sandy Mann" wrote: The problem with trying to do what you want with only formulas is that formulas are volatile inasmuch as that they will recalculate when the dependent cells change. For example in B2: =IF(C2=$A$1,$A$2,"") will return the contents of A2 when the date in A1 is the same as the date in C2. However, as soon as you change either A1 or A2 then B2 will change either to an empty string if you change A1 or the new value if you change A2. The way to stop this is to copy the value in that cell and paste it back using Paste Special but that is more trouble then simple entering the value manually. You could get an Event Macro to do it automatically when you make a change to A1 or A2 but that is a poor solution which brings other problems. I think that the best thing would be, like Otto said, if you were to try to explain to us as fully as you can, what it is that you are trying to do. -- Regards Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "DarkNight" wrote in message ... thanks for your help Sandy Mann, i forgot to add another cell Colum C = date so is there any way you can only get it to put information in the next available cell if the 2 date match. other wise it works just like i want it to. Is there an easy way for newbies to enter a formula rather than entering a code wich means nothing to me. sorry to be a pain "Sandy Mann" wrote: If I follow you correctly and you want to add the data in A2 to the bottom of a list of data in Column B then right click on the sheet tab and select View Code and paste this Worksheet code into the module: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub If Target.Value = "" Then Exit Sub Application.EnableEvents = False LastRow = Cells(Rows.Count, 2).End(xlUp).Row + 1 Cells(LastRow, 2).Value = Cells(2, 1).Value Application.EnableEvents = True End Sub -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "DarkNight" wrote in message ... hello again, i'm trying to get this to work:- A1 = date ( changeable ) A2 = £ ( changeable ) B1 to B100 ( if B1 has £ in it move to next cell down without over righting previous cell) B100 could be greater , just depends on how much data i collect. up to now i can only get data by copy and pasting information from A2 and placing it in the next empty available cell in B. i'vetryed tryed using the date but all cells then change to the current contents of A2 and really want previous contants to remain untouched. Thanks inadvance |
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
automated copy & paste
Cheers Sandy Mann , i've coped that but dont seem to work might be becaouse i
changed the original sheet about. would it be ok to send you it via email again? "Sandy Mann" wrote: Hi DarkNight, Assuming that you are becoming confused by my use of x and you mean to copy your 'Difference' cell and the three cells to the right of it to the three cells to the 'Extra £ Made' and the three cells to the right replace the line: cells(x,16).value = cells(26,9).value with: Range(Cells(x, 16), Cells(x, 19)).Value = _ Range(Cells(26, 9), Cells(26, 12)).Value Note that this is all one line with a line break after the Value= created by a space follows by an underscore. Probably the easiest thing to do is to copy that line from this post, highlight the line to be replace in the code and paste the new line in. If I've got you requirements wrong then paste back. -- HTH Sandy In Perth, the ancient capital of Scotland Replace @mailinator.com with @tiscali.co.uk "DarkNight" wrote in message ... Thanks for the code Sandy Mann everything works fine, just 1 little question, can this code be added to take more than 1 cell (26,9) be copyed to column P. if so other cells to include would be:- cell(26,10) up to cell (26,12) and tranasfer information to cell (x,16) up to (x,19) ? think this is the bit i'm refering to... for x = 4 to endrow if cells (2,1).value< cells(x+1,15).value2 and_ cells(2(1).value2 = cells(x,15).value2 then cells(x,16).value = cells(26,9).value goto getout "Sandy Mann" wrote: Nothing has arrived yet Dark Night - would you like to try again and I will have a look tomorrow -- Regards Sandy In Perth, the ancient capital of Scotland Replace @mailinator.com with @tiscali.co.uk "DarkNight" wrote in message ... cheers Sandy Mann sending you a copy now. "Sandy Mann" wrote: Hi Dark Night, A further couple of questions - If the date in C1, (or can it be further down Column C?), is the same as the date in A1 and there are three entries down Column B, where do you want to have the data in A2 to go to? If it helps you to explain what you want then by all means send me a sample sheet. Just replace the part form the @ as it says in my signatute -- Rrgards Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "DarkNight" wrote in message ... mmm ok i'll try to explain abit more, but thanks anyway for your responces so far. the problem i have is i'm trying to keep track of how much extra £'s i make in a given week, fortnight, month therefore A1 = current date A2 = extra £'s i've made so far, Column C = date of next pay starting with 1st empty cell in C then adding 1 cell down at a time. so if A1 = 1 day less than the date in Column C or then put in 1st empty cell in Column B and so on so lets say C1 = the 1st date and A1 = 1 day less than C1 then put contents of A2 in B1 then repeat all over again hope this helps. thopught this was gonna be hard to do as every attempt i've don it changes every cell value in colum C to the same value "Sandy Mann" wrote: The problem with trying to do what you want with only formulas is that formulas are volatile inasmuch as that they will recalculate when the dependent cells change. For example in B2: =IF(C2=$A$1,$A$2,"") will return the contents of A2 when the date in A1 is the same as the date in C2. However, as soon as you change either A1 or A2 then B2 will change either to an empty string if you change A1 or the new value if you change A2. The way to stop this is to copy the value in that cell and paste it back using Paste Special but that is more trouble then simple entering the value manually. You could get an Event Macro to do it automatically when you make a change to A1 or A2 but that is a poor solution which brings other problems. I think that the best thing would be, like Otto said, if you were to try to explain to us as fully as you can, what it is that you are trying to do. -- Regards Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "DarkNight" wrote in message ... thanks for your help Sandy Mann, i forgot to add another cell Colum C = date so is there any way you can only get it to put information in the next available cell if the 2 date match. other wise it works just like i want it to. Is there an easy way for newbies to enter a formula rather than entering a code wich means nothing to me. sorry to be a pain "Sandy Mann" wrote: If I follow you correctly and you want to add the data in A2 to the bottom of a list of data in Column B then right click on the sheet tab and select View Code and paste this Worksheet code into the module: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub If Target.Value = "" Then Exit Sub Application.EnableEvents = False LastRow = Cells(Rows.Count, 2).End(xlUp).Row + 1 Cells(LastRow, 2).Value = Cells(2, 1).Value Application.EnableEvents = True End Sub -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "DarkNight" wrote in message ... hello again, i'm trying to get this to work:- A1 = date ( changeable ) A2 = £ ( changeable ) B1 to B100 ( if B1 has £ in it move to next cell down without over righting previous cell) B100 could be greater , just depends on how much data i collect. up to now i can only get data by copy and pasting information from A2 and placing it in the next empty available cell in B. i'vetryed tryed using the date but all cells then change to the current contents of A2 and really want previous contants to remain untouched. Thanks inadvance |
#13
Posted to microsoft.public.excel.newusers
|
|||
|
|||
automated copy & paste
Hi,
Yes by all means send you sheet and I will take a look at it. -- Regards Sandy In Perth, the ancient capital of Scotland Replace @mailinator.com with @tiscali.co.uk "DarkNight" wrote in message ... Cheers Sandy Mann , i've coped that but dont seem to work might be becaouse i changed the original sheet about. would it be ok to send you it via email again? "Sandy Mann" wrote: Hi DarkNight, Assuming that you are becoming confused by my use of x and you mean to copy your 'Difference' cell and the three cells to the right of it to the three cells to the 'Extra £ Made' and the three cells to the right replace the line: cells(x,16).value = cells(26,9).value with: Range(Cells(x, 16), Cells(x, 19)).Value = _ Range(Cells(26, 9), Cells(26, 12)).Value Note that this is all one line with a line break after the Value= created by a space follows by an underscore. Probably the easiest thing to do is to copy that line from this post, highlight the line to be replace in the code and paste the new line in. If I've got you requirements wrong then paste back. -- HTH Sandy In Perth, the ancient capital of Scotland Replace @mailinator.com with @tiscali.co.uk "DarkNight" wrote in message ... Thanks for the code Sandy Mann everything works fine, just 1 little question, can this code be added to take more than 1 cell (26,9) be copyed to column P. if so other cells to include would be:- cell(26,10) up to cell (26,12) and tranasfer information to cell (x,16) up to (x,19) ? think this is the bit i'm refering to... for x = 4 to endrow if cells (2,1).value< cells(x+1,15).value2 and_ cells(2(1).value2 = cells(x,15).value2 then cells(x,16).value = cells(26,9).value goto getout "Sandy Mann" wrote: Nothing has arrived yet Dark Night - would you like to try again and I will have a look tomorrow -- Regards Sandy In Perth, the ancient capital of Scotland Replace @mailinator.com with @tiscali.co.uk "DarkNight" wrote in message ... cheers Sandy Mann sending you a copy now. "Sandy Mann" wrote: Hi Dark Night, A further couple of questions - If the date in C1, (or can it be further down Column C?), is the same as the date in A1 and there are three entries down Column B, where do you want to have the data in A2 to go to? If it helps you to explain what you want then by all means send me a sample sheet. Just replace the part form the @ as it says in my signatute -- Rrgards Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "DarkNight" wrote in message ... mmm ok i'll try to explain abit more, but thanks anyway for your responces so far. the problem i have is i'm trying to keep track of how much extra £'s i make in a given week, fortnight, month therefore A1 = current date A2 = extra £'s i've made so far, Column C = date of next pay starting with 1st empty cell in C then adding 1 cell down at a time. so if A1 = 1 day less than the date in Column C or then put in 1st empty cell in Column B and so on so lets say C1 = the 1st date and A1 = 1 day less than C1 then put contents of A2 in B1 then repeat all over again hope this helps. thopught this was gonna be hard to do as every attempt i've don it changes every cell value in colum C to the same value "Sandy Mann" wrote: The problem with trying to do what you want with only formulas is that formulas are volatile inasmuch as that they will recalculate when the dependent cells change. For example in B2: =IF(C2=$A$1,$A$2,"") will return the contents of A2 when the date in A1 is the same as the date in C2. However, as soon as you change either A1 or A2 then B2 will change either to an empty string if you change A1 or the new value if you change A2. The way to stop this is to copy the value in that cell and paste it back using Paste Special but that is more trouble then simple entering the value manually. You could get an Event Macro to do it automatically when you make a change to A1 or A2 but that is a poor solution which brings other problems. I think that the best thing would be, like Otto said, if you were to try to explain to us as fully as you can, what it is that you are trying to do. -- Regards Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "DarkNight" wrote in message ... thanks for your help Sandy Mann, i forgot to add another cell Colum C = date so is there any way you can only get it to put information in the next available cell if the 2 date match. other wise it works just like i want it to. Is there an easy way for newbies to enter a formula rather than entering a code wich means nothing to me. sorry to be a pain "Sandy Mann" wrote: If I follow you correctly and you want to add the data in A2 to the bottom of a list of data in Column B then right click on the sheet tab and select View Code and paste this Worksheet code into the module: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub If Target.Value = "" Then Exit Sub Application.EnableEvents = False LastRow = Cells(Rows.Count, 2).End(xlUp).Row + 1 Cells(LastRow, 2).Value = Cells(2, 1).Value Application.EnableEvents = True End Sub -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "DarkNight" wrote in message ... hello again, i'm trying to get this to work:- A1 = date ( changeable ) A2 = £ ( changeable ) B1 to B100 ( if B1 has £ in it move to next cell down without over righting previous cell) B100 could be greater , just depends on how much data i collect. up to now i can only get data by copy and pasting information from A2 and placing it in the next empty available cell in B. i'vetryed tryed using the date but all cells then change to the current contents of A2 and really want previous contants to remain untouched. Thanks inadvance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to copy & paste excel charts to Words as Windows Metafile | Charts and Charting in Excel | |||
copy & paste spreadsheet cells from excel to outlook to excel | Excel Discussion (Misc queries) | |||
Copy and paste from one spreadsheet to another | Excel Worksheet Functions | |||
Issue with copy & paste? | Excel Discussion (Misc queries) | |||
Copy & Paste | Excel Discussion (Misc queries) |