Output to consequtive cells

[Paddington1881]

Hi. I'm trying to figure out a way to take numeric inputs in a single cell and copy them to another column of cells. Then, when the single input cell is overwritten, the new input is copied to the cell below in the new column.

As an example, if I input 23 into A1, I want D1 to show 23. Then when I input 46 into A1 (overwriting the previous 23 figure), I want D2 to show 46 etc etc.

Is there a formula method to do this or would a macro be easier? I'm new to macros so go easy with me.

Thanks for any help and sorry of this is tediously easy to everyone, but its driving me mad.

Darren

 

[Yogi Anand]

Hi Darren:

I would suggest using the following Worksheet_Chane event macro ...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Intersect(Target, [a1]) Is Nothing Or Target.Count > 1 Then Exit Sub
        If [D1] = "" Then
            [D1] = Target.Value
            Else: Range("D" & [D65536].End(xlUp).Row + 1) = Target.Value
        End If
End Sub

To install the macro, Right_Click on the Sheet Tab, and then click on View_Code, that will take you to VBE code window. Paste the code in the large white space.

Use ALT+F11 to return to the Worksheet ... now when you make an entry in cell A1, it will be appended in column D.

I hope this helps!

 

[Smitty]

Welcome to the Board!

How's this:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range
        <SPAN style="color:#00007F">Set</SPAN> rng = Range("A1")
        <SPAN style="color:#00007F">Set</SPAN> LastRow = Range("D65536").End(xlUp)
             <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN>
            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
            <SPAN style="color:#007F00">'   Only look at that range</SPAN>
            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
            <SPAN style="color:#007F00">'   Copy A1's entry to Column D</SPAN>
            LastRow.Offset(1, 0) = Range("A1").Value
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

The code goes in the worksheet specific module.

Hope that helps,

Smitty

EDIT: Yogi beat me to it.

 

[Paddington1881]

Firstly - thanks to both responders for their time - I'm using the macro from Yogi and this has worked so well that my ides for my spreadsheet mushroomed.

I've spent a day trying to integrate similar code to do the same thing in different cells but without any success.

In my original post I was trying to copy cell A1 inputs into D1 then D2 etc etc. Yogi's macro did this perfectly. If I also wanted to take inputs in A2 and copy them into column E1, E2 etc, what modifications to the macro would be required?

Can these two tasks run together?

I've been playing around with the VB editor (trial & error) but can't get anywhere. Can someone give me a hint?

Thanks as always

Darren

 

[Yogi Anand]

Hi Darren:

Based on your PM of Jan-15-2005, I believe you have sorted everything out to your satisfaction. If you do need to discuss something further please post back and let us take it from there.

 

[Paddington1881]

Hi Yogi

My second post in this thread came after my PM to you - the success of your macro got me really thinking how my spreadsheet could be developed.

So... I'm still trying to figure out how to write more lines into your code to accomplish the same affect, but a different input cell and a different output column. Hence my question above: can the same functionality be duplicated in the same macro? Or if not, how do I 'link' 2 macos?

I'm sorry to be a pain, and I'm all for learning from trial & error, so if you can spare the time to point me in the right direction I'd be very grateful.

Best wishes
Darren

 

[Smitty]

So... I'm still trying to figure out how to write more lines into your code to accomplish the same affect, but a different input cell and a different output column. Hence my question above: can the same functionality be duplicated in the same macro?

Yes, if you indicate what the cells are and what you want done. :wink:

Smitty

 

[Paddington1881]

OK, thanks Penny.

Yogi's original macro took cell inputs in A1 and copied them to D1, then D2, D3 etc over and over as A1 was overwritten.

I would like to now have cell A2 inputs copied to E1, E2, E3 etc as and when A2 is overwritten, but still retain the functionality that A1 inputs would still be apended to the bottom of the list in column D.

Thanks very much for your efforts.

Best wishes

Darren

 

[HalfAce]

Hi Darren (Yogi, Smitty...)
If you're only interested in those two cells (A1 & A2) you could amend Yogi's solution (only because that's the one you're using at present...) to this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Intersect(Target, [a1]) Is Nothing Or Target.Count > 1 Then GoTo TryNext
        If [D1] = "" Then
            [D1] = Target.Value
            Else: Range("D" & [D65536].End(xlUp).Row + 1) = Target.Value
        Exit Sub
        End If
        
TryNext:
     If Intersect(Target, [a2]) Is Nothing Or Target.Count > 1 Then Exit Sub
        If [E1] = "" Then
            [E1] = Target.Value
            Else: Range("E" & [E65536].End(xlUp).Row + 1) = Target.Value
        End If
    
End Sub

If you're going to be doing this with more than just two cells, I'd start thinking of a slightly different approach.

Hope it helps,
Dan

 

[HalfAce]

As a follow up, (and due to your comment "my ideas for my spreadsheet mushroomed"), it's a fair bet you'll (at some point) be looking to do this with more than just two cells. So with that in mind, here's (one) "slightly different approach" as mentioned above...just to give you some ideas.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng
rng = Target.Address

Select Case rng
    Case "$A$1"
        If [D1] = "" Then
            [D1] = Target.Value
            Else: Range("D" & [D65536].End(xlUp).Row + 1) = Target.Value
        End If
    Case "$A$2"
        If [E1] = "" Then
            [E1] = Target.Value
            Else: Range("E" & [E65536].End(xlUp).Row + 1) = Target.Value
        End If
    Case "$A$3"
        If [F1] = "" Then
            [F1] = Target.Value
            Else: Range("F" & [F65536].End(xlUp).Row + 1) = Target.Value
        End If
End Select
End Sub

Food for thought.
Hope it helps,
Dan