# Thread: Default / Paste Problem (2002)

1. ## Default / Paste Problem (2002)

I have the user enter time in cells A1:A10 one at a time. In B1:B10 I have the change event add an hour to the cell in column A and place the result in column B as a default. The user can change the default if he wishes.

My problem occurs when the user, let

2. ## Re: Default / Paste Problem (2002)

Why use a change event rather than just having a formula in column B that adds the hour?

3. ## Re: Default / Paste Problem (2002)

Slight modification to Jan's suggestion:
Why not just enter times in A,
Hour increases in B
and in C use the formula(eg in C2)
<pre>=A2+B2/24</pre>

Formulas can be copied down as desired.

If you want no display until A has a time, you could use something like this in C2
<pre>=IF(A2,A2+B2/24,"")</pre>

and copy it down the column

Steve

4. ## Re: Default / Paste Problem (2002)

One reason I am using the change event instead of a formula is to preserve the calculation for future use. For instance, A3 has 13:00. B3 would have 14:00 based on a formula in cell B3 like =A3+Time(1,0,0). The user then decides he doesn

5. ## Re: Default / Paste Problem (2002)

Hi Steve,

Thanks for the reply. My reply to Jan's post may shed some more light on what it is I am trying to do.

6. ## Re: Default / Paste Problem (2002)

You could use code something like this:

<pre>Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Application.EnableEvents = False
For Each oCell In Intersect(Target, Range("A:A"))
If Intersect(Target, Range("B" & oCell.Row)) Is Nothing Then
oCell.Offset(0, 1).Value = oCell.Value + (1 / 24)
End If
Next oCell
Application.EnableEvents = True
End If
End Sub
</pre>

7. ## Re: Default / Paste Problem (2002)

Thanks for the reply. I have code very similar to yours. The main problem I'm having is when a value gets copied from A3:B3 and then pasted into row A4:B4, the value of B4 is generated by code because A4 was changed. The pasted value of B3 is not pasted.

The only work around I can see to the problem is to provide a toolbar button to run the code. This way pasting into A:B doesn't trigger the change event to default the value for B. Can't think of any other way to do it.

8. ## Re: Default / Paste Problem (2002)

Did you run Legare's code?

If you copy A3:B3, it copies B3 it does not regenerate it from the code using the value in A3.

Steve

9. ## Re: Default / Paste Problem (2002)

Steve, I honestly didn't because it look so much like what I had. I'm never going to do that again! Your encouragement made me look a lot closer and I realized there was second intersect in there checking the B cell. I used it and it worked as I needed it to. Thank you for speaking up and for waking me up! <img src=/S/doze.gif border=0 alt=doze width=15 height=15>

10. ## Re: Default / Paste Problem (2002)

Lagare, my humble apologizes. <img src=/S/blush.gif border=0 alt=blush width=15 height=15> Steve encouraged me to revisit your code. It looked very similar to mine but with an extra intersect in it checking for the B cell. I used it and it worked very nicely. Your code was great <img src=/S/artist.gif border=0 alt=artist width=34 height=29> and I learned a lot from it. Thank you so much for your help!

11. ## Re: Default / Paste Problem (2002)

I'm glad it worked for you.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•