VB Code not grabbing cell values

  • Thread starter Thread starter Morgan
  • Start date Start date
M

Morgan

hi, i was given the VB code below which takes all the value of cells V17 and
X25 and create a list down columns A and K on the 'graphs' sheet, it works
when i manually enter values into cells V17 and X25 and was testing it, but
it doesn't work when those cells are auto populated with data as they
calculate other cells, so the VB code isn't taking the values that appear in
those cells automatically like it should.

any ideas?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strColumn As String, lngRow As Long, ws As Worksheet

If Target.Count <> 1 Or Target.Text = "" Then Exit Sub

If Target.Address = "$V$17" Then
strColumn = "A"
ElseIf Target.Address = "$X$25" Then
strColumn = "K"
End If

If strColumn <> vbNullString Then
Set ws = Worksheets("graphs")
Application.EnableEvents = False
lngRow = ws.Cells(Rows.Count, strColumn).End(xlUp).Row
ws.Range(strColumn & lngRow + 1) = Target.Text
Application.EnableEvents = True
End If


End Sub
 
Testing your code puts the appropriate value in the LAST cell of the
appropriate range
 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim strColumn As String, lngRow As Long, ws As Worksheet

If Target.Count <> 1 Or Target.Text = "" Then Exit Sub

If Target.Address = "$V$17" Then
strColumn = "A"
ElseIf Target.Address = "$X$25" Then
strColumn = "K"
End If
'MsgBox strColumn
If strColumn <> vbNullString Then
With Worksheets("graphs")
lngRow = .Cells(Rows.Count, strColumn).End(xlUp).Row
..Range(.Cells(1, strColumn), _
..Cells(lngRow + 1, strColumn)) = Target.Text

End With
End If
 
hi, thanks for your efforts but i put your code in below and it wouldn't
work, i just need to capture the values that appear in those cells, forming a
list in columns A & K,

thank you
 
It was tested. Did you put the code in a sheet module or in a regular
module.
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.
 
A change event is designed to fire when that cell is changed by an entry
instead of a calculation.
 
Back
Top