| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VB Code not grabbing cell values
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 -- thanks |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VB Code not grabbing cell values
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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Testing your code puts the appropriate value in the LAST cell of the appropriate range -- Don Guillett Microsoft MVP Excel SalesAid Software "Morgan" wrote in message ... 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 -- thanks |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VB Code not grabbing cell values
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 "Don Guillett" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Testing your code puts the appropriate value in the LAST cell of the appropriate range -- Don Guillett Microsoft MVP Excel SalesAid Software "Morgan" wrote in message ... 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 -- thanks . |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VB Code not grabbing cell values
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. -- Don Guillett Microsoft MVP Excel SalesAid Software "Morgan" wrote in message ... 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 "Don Guillett" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Testing your code puts the appropriate value in the LAST cell of the appropriate range -- Don Guillett Microsoft MVP Excel SalesAid Software "Morgan" wrote in message ... 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 -- thanks . |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VB Code not grabbing cell values
A change event is designed to fire when that cell is changed by an entry
instead of a calculation. -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... 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. -- Don Guillett Microsoft MVP Excel SalesAid Software "Morgan" wrote in message ... 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 "Don Guillett" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Testing your code puts the appropriate value in the LAST cell of the appropriate range -- Don Guillett Microsoft MVP Excel SalesAid Software "Morgan" wrote in message ... 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 -- thanks . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Grabbing a number from another cell on another worksheet | Excel Discussion (Misc queries) | |||
| Keeps grabbing more than one cell/column | Excel Worksheet Functions | |||
| Grabbing Formula Information from Another Cell | Excel Discussion (Misc queries) | |||
| Grabbing data from a specific cell | Excel Worksheet Functions | |||
| Grabbing recods based on date and shift values | Excel Worksheet Functions | |||
Linear Mode
