Extending Range in Code

Hi,

I have some code that stores the values the are typed into a cell. This is so I can track dates and how much they changed from start to finish.

The code that I have does this function, but it only works for A1 and nothing else.

I want this to work on a line by line basis as each row in excel is a different piece of work.

 Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lCol As Long
      If Not Intersect(Target, Range("A1")) Is Nothing Then
          Application.EnableEvents = False
          'Get the last column for Row A. This will determine where the value needs to be copied
          lCol = Cells(1, Columns.Count).End(xlToLeft).Column
          'Copy to the first row of the next column found above
          Cells(1, lCol + 1).Value = Range("A1").Value
          Application.EnableEvents = True
      End If

End Sub

Any help would be great!

If Not Intersect(Target, Range("A1")) Is Nothing Then

this line is what triggers the code, it's looking for a cell value change in the specified range (A1). To extend the range change A1 to..... A1:A10000.....or A1:Z1.....or A1:Z10000, whatever range is needed.

However, the code to copy bits around is done with:

lCol = Cells(1, Columns.Count).End(xlToLeft).Column
Cells(1, lCol + 1).Value = Range("A1").Value

this only looks for the next available cell in row 1. In order to make this copy to the next available cell of whichever row was just changed, change it to:

lCol = Cells(target.row, Columns.Count).End(xlToLeft).Column
Cells(target.row, lCol + 1).Value = Range("A" & target.row).Value

Not too sure what you mean but I'll have a guess.

lCol = Cells(target.row, Columns.Count).End(xlToLeft).Column
Cells(target.row, lCol + 1).Value = Range("A" & target.row).Value

This bit selects the last column with data in it for the row that's changed and writes to it. To force this to write to column Z replace with:

range("Z" & target.row).value = Range("A" & target.row).Value

Please Help!

I'm still not fully understanding you.....I think what you mean is you want the date logging to be in column Z rather than B.....and this will do that:

range("Z" & target.row).value = Range("A" & target.row).Value

Sorry about this.

I still need the code to keep a track of all the values that have been entered in column A but I need to output to start in column Z then AA then AB etc.

I have a picture that might explain better.

This code is what I need except I need it to not start recording in the column beside A (where data is input).

  Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lCol As Long
      If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
          Application.EnableEvents = False
          'Get the last column for Row A. This will determine where the value needs to be copied
          lCol = Cells(Target.Row, Columns.Count).End(xlToLeft).Column
          'Copy to the first row of the next column found above
          Cells(Target.Row, lCol + 1).Value = Range("A" & Target.Row).Value
          Application.EnableEvents = True
      End If

End Sub

Capture.PNG

ahhhhhh, now I get you.

Ok, so what you want is for the code that copies the date over to choose the next empty column to write to, after column Z, not overwrite the value in column Z.

Do this:

lCol = Cells(Target.Row, Columns.Count).End(xlToLeft).Column
if lcol < 26 then lcol = 26

What you're telling Excel is find the next available column number but if it's to the left of column Z make it column Z.

This worked like a charm!

Thank you so much