Can someone please write me a macro for excel [Solved/Closed]

Report
-
Posts
2674
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 27, 2020
-
I want to be able to input a number into a cell and have that number recorded in a list elsewhere within the same sheet.
Then be able to replace that number with another and have it added to the list.

Can someone please help me by writing this macro for me?


3 replies

Posts
368
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
Could you put some data of your file and redefine the problem accordance to your data.
When user makes A1 equal "1", so B1=1.
After...
User now makes A1 equal "19", so B1=1 & B2=19.
After...
User now makes A1 equal ""484566", so B1=1 & B2=19 & B3=484566
etc.


Will something like this help for helping recognise the changing of cell?

Private Sub Worksheet_Change(ByVal Target As Range)

'Do nothing if more than one cell is changed or content deleted

If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub



If Target.Address = "$A$1" Then

'Ensure target is a number before multiplying by 2

If IsNumeric(Target) Then

'Stop any possible runtime errors and halting code

On Error Resume Next

'Turn off ALL events so the Target * 2 does not _

put the code into a loop.

Application.EnableEvents = False

Target = Target * 2

'Turn events back on

Application.EnableEvents = True

'Allow run time errors again

On Error GoTo 0

End If

End If



End Sub
Posts
2674
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 27, 2020
446
Hi Paul,

Try this code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
Range("A1").Copy
Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
End Sub

The code will only run when a change is made to cell A1.
Assuming B1 contains header, the first destination cell will be B2.

Best regards,
Trowa