Hi,
Sorry but I have to try to ask this question again. I have had usefull
information from some members but no resolution and this is the last
stumbling block,
I do not know how to say it simply that I feel conveys what I need but here
goes .

I am trying to write code which records information that is changed on
sheet1 and record what was entered, when and in what cell on sheet2. This is
working but I only get the cell address that is changed - i need this to give
me a company name and I have tried with a lookup by creating yet another
sheet. I have entered a manual Vlookup and that works fine but I want to
create a query from this data and thus the vlookup is a hinderance.

Is any one willing to spend the time to help me with this one. I know it
sounds like I am asking to be spoon feed but it is the last stage on the
project and I have spent the last 2 days trying to resolve this.

Thank you

Hello again, This might be over my head but could i see the code you have so
far?


"Leanne" wrote:

Hi,
Sorry but I have to try to ask this question again. I have had usefull
information from some members but no resolution and this is the last
stumbling block,
I do not know how to say it simply that I feel conveys what I need but here
goes .

I am trying to write code which records information that is changed on
sheet1 and record what was entered, when and in what cell on sheet2. This is
working but I only get the cell address that is changed - i need this to give
me a company name and I have tried with a lookup by creating yet another
sheet. I have entered a manual Vlookup and that works fine but I want to
create a query from this data and thus the vlookup is a hinderance.

Is any one willing to spend the time to help me with this one. I know it
sounds like I am asking to be spoon feed but it is the last stage on the
project and I have spent the last 2 days trying to resolve this.

Thank you

As ALWAYS, post your coding efforts for comments

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Leanne" wrote in message
...
Hi,
Sorry but I have to try to ask this question again. I have had usefull
information from some members but no resolution and this is the last
stumbling block,
I do not know how to say it simply that I feel conveys what I need but
here
goes .

I am trying to write code which records information that is changed on
sheet1 and record what was entered, when and in what cell on sheet2. This
is
working but I only get the cell address that is changed - i need this to
give
me a company name and I have tried with a lookup by creating yet another
sheet. I have entered a manual Vlookup and that works fine but I want to
create a query from this data and thus the vlookup is a hinderance.

Is any one willing to spend the time to help me with this one. I know it
sounds like I am asking to be spoon feed but it is the last stage on the
project and I have spent the last 2 days trying to resolve this.

Thank you

Sorry - New to the forum and did not think about the obvious.

This is the code for recording the change and own its own works well. It is
worksheet code. OFFICE_NOVICE - this sheet is the one I am trying to create
the form for also.

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set ra = Range("C2:C300")
Set s2 = Sheets("Visit History")
If Intersect(ra, t) Is Nothing Then Exit Sub
v = t.Value
n = s2.Cells(Rows.Count, 1).End(xlUp).Row + 1
Application.EnableEvents = False
s2.Cells(n, 1).Value = v
s2.Cells(n, 2).Value = Date
s2.Cells(n, 3).Value = t.Address
Application.EnableEvents = True
End Sub

The result I get is the following
Data changed When changed Cell address that was changed
08/01/2008 17/04/2008 $C$2

The sheet it takes the data from has the customer name in column A and dates
entered into column B & C.

Ideally what I want to see is the customer name either instead of the cell
address or beside it would be fine.

The following code is one that I have been given by someone else on this
site but I can not get it to work. I have created another sheet with the
cell reference and then the customer name as instructed but still can not get
it to work - it does not even bring back the data that was changed. I
thought I would add it however as it may help.

Private Sub Worksheet_Change(ByVal Target As Range)

'Select Range of target cells
Set ra = Range("C2:C300")
'select sheet where table is located
Set s2 = Sheets("Visit History")
'select sheet and Range of customer Table
Set Cust_Names = _
Sheets("Customer Names").Range("A1:A200") '*****Change *********

Application.EnableEvents = False

For Each cell In Target

If Intersect(ra, cell) Is Nothing Then Exit Sub

'get next empty row in table
NewRow = s2.Cells(Rows.Count, 1).End(xlUp).Row + 1

'Put target data into table
s2.Cells(NewRow, "A").Value = cell.Value
'Put date into Table
s2.Cells(NewRow, 2).Value = Date

'Get Customer Address
Set c = Cust_Names.Find(what:=cell, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Cust_Addr = c.Offset(0, 1) 'change offset if
necessary
s2.Cells(NewRow, 3).Value = Cust_Addr
End If
Next cell

Application.EnableEvents = True
End Sub

Thanks again to anyone who can help.

"Leanne" wrote:

Sorry - New to the forum and did not think about the obvious.

This is the code for recording the change and own its own works well. It is
worksheet code. OFFICE_NOVICE - this sheet is the one I am trying to create
the form for also.

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set ra = Range("C2:C300")
Set s2 = Sheets("Visit History")
If Intersect(ra, t) Is Nothing Then Exit Sub
v = t.Value
n = s2.Cells(Rows.Count, 1).End(xlUp).Row + 1
Application.EnableEvents = False
s2.Cells(n, 1).Value = v
s2.Cells(n, 2).Value = Date
s2.Cells(n, 3).Value = t.Address
Application.EnableEvents = True
End Sub

The result I get is the following
Data changed When changed Cell address that was changed
08/01/2008 17/04/2008 $C$2

The sheet it takes the data from has the customer name in column A and dates
entered into column B & C.

Ideally what I want to see is the customer name either instead of the cell
address or beside it would be fine.

The following code is one that I have been given by someone else on this
site but I can not get it to work. I have created another sheet with the
cell reference and then the customer name as instructed but still can not get
it to work - it does not even bring back the data that was changed. I
thought I would add it however as it may help.

Private Sub Worksheet_Change(ByVal Target As Range)

'Select Range of target cells
Set ra = Range("C2:C300")
'select sheet where table is located
Set s2 = Sheets("Visit History")
'select sheet and Range of customer Table
Set Cust_Names = _
Sheets("Customer Names").Range("A1:A200") '*****Change *********

Application.EnableEvents = False

For Each cell In Target

If Intersect(ra, cell) Is Nothing Then Exit Sub

'get next empty row in table
NewRow = s2.Cells(Rows.Count, 1).End(xlUp).Row + 1

'Put target data into table
s2.Cells(NewRow, "A").Value = cell.Value
'Put date into Table
s2.Cells(NewRow, 2).Value = Date

'Get Customer Address
Set c = Cust_Names.Find(what:=cell, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Cust_Addr = c.Offset(0, 1) 'change offset if
necessary
s2.Cells(NewRow, 3).Value = Cust_Addr
End If
Next cell

Application.EnableEvents = True
End Sub

Thanks again to anyone who can help.

Try...
s2.Cells(n, 3).Value = t.Offset(0,-3).Value in lieu of
s2.Cells(n, 3).Value = t.Address

"Leanne" wrote:



"Leanne" wrote:

Sorry - New to the forum and did not think about the obvious.

This is the code for recording the change and own its own works well. It is
worksheet code. OFFICE_NOVICE - this sheet is the one I am trying to create
the form for also.

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set ra = Range("C2:C300")
Set s2 = Sheets("Visit History")
If Intersect(ra, t) Is Nothing Then Exit Sub
v = t.Value
n = s2.Cells(Rows.Count, 1).End(xlUp).Row + 1
Application.EnableEvents = False
s2.Cells(n, 1).Value = v
s2.Cells(n, 2).Value = Date
s2.Cells(n, 3).Value = t.Address
Application.EnableEvents = True
End Sub

The result I get is the following
Data changed When changed Cell address that was changed
08/01/2008 17/04/2008 $C$2

The sheet it takes the data from has the customer name in column A and dates
entered into column B & C.

Ideally what I want to see is the customer name either instead of the cell
address or beside it would be fine.

The following code is one that I have been given by someone else on this
site but I can not get it to work. I have created another sheet with the
cell reference and then the customer name as instructed but still can not get
it to work - it does not even bring back the data that was changed. I
thought I would add it however as it may help.

Private Sub Worksheet_Change(ByVal Target As Range)

'Select Range of target cells
Set ra = Range("C2:C300")
'select sheet where table is located
Set s2 = Sheets("Visit History")
'select sheet and Range of customer Table
Set Cust_Names = _
Sheets("Customer Names").Range("A1:A200") '*****Change *********

Application.EnableEvents = False

For Each cell In Target

If Intersect(ra, cell) Is Nothing Then Exit Sub

'get next empty row in table
NewRow = s2.Cells(Rows.Count, 1).End(xlUp).Row + 1

'Put target data into table
s2.Cells(NewRow, "A").Value = cell.Value
'Put date into Table
s2.Cells(NewRow, 2).Value = Date

'Get Customer Address
Set c = Cust_Names.Find(what:=cell, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Cust_Addr = c.Offset(0, 1) 'change offset if
necessary
s2.Cells(NewRow, 3).Value = Cust_Addr
End If
Next cell

Application.EnableEvents = True
End Sub

Thanks again to anyone who can help.

Or rather, this worked for me

s2.Cells(n, 3).Value = t.Offset(0, -2).Value

"Leanne" wrote:



"Leanne" wrote:

Sorry - New to the forum and did not think about the obvious.

This is the code for recording the change and own its own works well. It is
worksheet code. OFFICE_NOVICE - this sheet is the one I am trying to create
the form for also.

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set ra = Range("C2:C300")
Set s2 = Sheets("Visit History")
If Intersect(ra, t) Is Nothing Then Exit Sub
v = t.Value
n = s2.Cells(Rows.Count, 1).End(xlUp).Row + 1
Application.EnableEvents = False
s2.Cells(n, 1).Value = v
s2.Cells(n, 2).Value = Date
s2.Cells(n, 3).Value = t.Address
Application.EnableEvents = True
End Sub

The result I get is the following
Data changed When changed Cell address that was changed
08/01/2008 17/04/2008 $C$2

The sheet it takes the data from has the customer name in column A and dates
entered into column B & C.

Ideally what I want to see is the customer name either instead of the cell
address or beside it would be fine.

The following code is one that I have been given by someone else on this
site but I can not get it to work. I have created another sheet with the
cell reference and then the customer name as instructed but still can not get
it to work - it does not even bring back the data that was changed. I
thought I would add it however as it may help.

Private Sub Worksheet_Change(ByVal Target As Range)

'Select Range of target cells
Set ra = Range("C2:C300")
'select sheet where table is located
Set s2 = Sheets("Visit History")
'select sheet and Range of customer Table
Set Cust_Names = _
Sheets("Customer Names").Range("A1:A200") '*****Change *********

Application.EnableEvents = False

For Each cell In Target

If Intersect(ra, cell) Is Nothing Then Exit Sub

'get next empty row in table
NewRow = s2.Cells(Rows.Count, 1).End(xlUp).Row + 1

'Put target data into table
s2.Cells(NewRow, "A").Value = cell.Value
'Put date into Table
s2.Cells(NewRow, 2).Value = Date

'Get Customer Address
Set c = Cust_Names.Find(what:=cell, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Cust_Addr = c.Offset(0, 1) 'change offset if
necessary
s2.Cells(NewRow, 3).Value = Cust_Addr
End If
Next cell

Application.EnableEvents = True
End Sub

Thanks again to anyone who can help.