automated copy & paste

[DarkNight]

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

[Otto Moehrbach]

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" <[email protected]> wrote in message
news:[email protected]...
> 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

[Sandy Mann]

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

[email protected]
[email protected] with @tiscali.co.uk


"DarkNight" <[email protected]> wrote in message
news:[email protected]...
> 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

[DarkNight]

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
>
> [email protected]
> [email protected] with @tiscali.co.uk
>
>
> "DarkNight" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[Sandy Mann]

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

[email protected]
[email protected] with @tiscali.co.uk


"DarkNight" <[email protected]> wrote in message
news:[email protected]...
> 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
>>
>> [email protected]
>> [email protected] with @tiscali.co.uk
>>
>>
>> "DarkNight" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>>
>>
>>

[DarkNight]

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
>
> [email protected]
> [email protected] with @tiscali.co.uk
>
>
> "DarkNight" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >>
> >> [email protected]
> >> [email protected] with @tiscali.co.uk
> >>
> >>
> >> "DarkNight" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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
> >>
> >>
> >>
>
>
>

[Sandy Mann]

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

[email protected]
[email protected] with @tiscali.co.uk


"DarkNight" <[email protected]> wrote in message
news:[email protected]...
> 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
>>
>> [email protected]
>> [email protected] with @tiscali.co.uk
>>
>>
>> "DarkNight" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>> >>
>> >> [email protected]
>> >> [email protected] with @tiscali.co.uk
>> >>
>> >>
>> >> "DarkNight" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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
>> >>
>> >>
>> >>
>>
>>
>>

[DarkNight]

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
>
> [email protected]
> [email protected] with @tiscali.co.uk
>
>
> "DarkNight" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >>
> >> [email protected]
> >> [email protected] with @tiscali.co.uk
> >>
> >>
> >> "DarkNight" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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
> >> >>
> >> >> [email protected]
> >> >> [email protected] with @tiscali.co.uk
> >> >>
> >> >>
> >> >> "DarkNight" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > 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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

[Sandy Mann]

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk


"DarkNight" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>>
>> [email protected]
>> [email protected] with @tiscali.co.uk
>>
>>
>> "DarkNight" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>> >>
>> >> [email protected]
>> >> [email protected] with @tiscali.co.uk
>> >>
>> >>
>> >> "DarkNight" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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
>> >> >>
>> >> >> [email protected]
>> >> >> [email protected] with @tiscali.co.uk
>> >> >>
>> >> >>
>> >> >> "DarkNight" <[email protected]> wrote in message
>> >> >> news:[email protected]...
>> >> >> > 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
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

[DarkNight]

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
>
> [email protected]
> Replace @mailinator.com with @tiscali.co.uk
>
>
> "DarkNight" <[email protected]> wrote in message
> news:[email protected]...
> >
> > 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
> >>
> >> [email protected]
> >> [email protected] with @tiscali.co.uk
> >>
> >>
> >> "DarkNight" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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
> >> >>
> >> >> [email protected]
> >> >> [email protected] with @tiscali.co.uk
> >> >>
> >> >>
> >> >> "DarkNight" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > 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
> >> >> >>
> >> >> >> [email protected]
> >> >> >> [email protected] with @tiscali.co.uk
> >> >> >>
> >> >> >>
> >> >> >> "DarkNight" <[email protected]> wrote in message
> >> >> >> news:[email protected]...
> >> >> >> > 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
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

[Sandy Mann]

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk


"DarkNight" <[email protected]> wrote in message
news:[email protected]...
> 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
>>
>> [email protected]
>> Replace @mailinator.com with @tiscali.co.uk
>>
>>
>> "DarkNight" <[email protected]> wrote in message
>> news:[email protected]...
>> >
>> > 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
>> >>
>> >> [email protected]
>> >> [email protected] with @tiscali.co.uk
>> >>
>> >>
>> >> "DarkNight" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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
>> >> >>
>> >> >> [email protected]
>> >> >> [email protected] with @tiscali.co.uk
>> >> >>
>> >> >>
>> >> >> "DarkNight" <[email protected]> wrote in message
>> >> >> news:[email protected]...
>> >> >> > 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
>> >> >> >>
>> >> >> >> [email protected]
>> >> >> >> [email protected] with @tiscali.co.uk
>> >> >> >>
>> >> >> >>
>> >> >> >> "DarkNight" <[email protected]> wrote in
>> >> >> >> message
>> >> >> >> news:[email protected]...
>> >> >> >> > 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
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

[DarkNight]

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
>
> [email protected]
> Replace @mailinator.com with @tiscali.co.uk
>
>
> "DarkNight" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >>
> >> [email protected]
> >> Replace @mailinator.com with @tiscali.co.uk
> >>
> >>
> >> "DarkNight" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >
> >> > 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
> >> >>
> >> >> [email protected]
> >> >> [email protected] with @tiscali.co.uk
> >> >>
> >> >>
> >> >> "DarkNight" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > 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
> >> >> >>
> >> >> >> [email protected]
> >> >> >> [email protected] with @tiscali.co.uk
> >> >> >>
> >> >> >>
> >> >> >> "DarkNight" <[email protected]> wrote in message
> >> >> >> news:[email protected]...
> >> >> >> > 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
> >> >> >> >>
> >> >> >> >> [email protected]
> >> >> >> >> [email protected] with @tiscali.co.uk
> >> >> >> >>
> >> >> >> >>
> >> >> >> >> "DarkNight" <[email protected]> wrote in
> >> >> >> >> message
> >> >> >> >> news:[email protected]...
> >> >> >> >> > 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
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

[Sandy Mann]

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk


"DarkNight" <[email protected]> wrote in message
news:[email protected]...
> 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
>>
>> [email protected]
>> Replace @mailinator.com with @tiscali.co.uk
>>
>>
>> "DarkNight" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>> >>
>> >> [email protected]
>> >> Replace @mailinator.com with @tiscali.co.uk
>> >>
>> >>
>> >> "DarkNight" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> >
>> >> > 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
>> >> >>
>> >> >> [email protected]
>> >> >> [email protected] with @tiscali.co.uk
>> >> >>
>> >> >>
>> >> >> "DarkNight" <[email protected]> wrote in message
>> >> >> news:[email protected]...
>> >> >> > 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
>> >> >> >>
>> >> >> >> [email protected]
>> >> >> >> [email protected] with @tiscali.co.uk
>> >> >> >>
>> >> >> >>
>> >> >> >> "DarkNight" <[email protected]> wrote in
>> >> >> >> message
>> >> >> >> news:[email protected]...
>> >> >> >> > 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
>> >> >> >> >>
>> >> >> >> >> [email protected]
>> >> >> >> >> [email protected] with @tiscali.co.uk
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >> "DarkNight" <[email protected]> wrote in
>> >> >> >> >> message
>> >> >> >> >> news:[email protected]...
>> >> >> >> >> > 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
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>