VB Code not grabbing cell values

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
 
D

Don Guillett

Testing your code puts the appropriate value in the LAST cell of the
appropriate range
 
D

Don Guillett

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
 
M

Morgan

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
 
D

Don Guillett

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.
 
D

Don Guillett

A change event is designed to fire when that cell is changed by an entry
instead of a calculation.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top