Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with VBA Code - once again
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. This is the code for recording the change and own its own works well. It is worksheet code. 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 cannot 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with VBA Code - once again
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 s2.Cells(n, 4).Value = t.Offset(0, -2).Value Application.EnableEvents = True End Sub This verision gets the customer name from column A and puts in the log sheet along with the other material. -- Gary''s Student - gsnu200780 "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. This is the code for recording the change and own its own works well. It is worksheet code. 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 cannot 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with VBA Code - once again
Thank you. I get Device I/O Error - but it works??!!
If I want to change the range to look at column B also - would it work if I just changed the range or would I have to repeat it due to the offset? "Gary''s Student" wrote: 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 s2.Cells(n, 4).Value = t.Offset(0, -2).Value Application.EnableEvents = True End Sub This verision gets the customer name from column A and puts in the log sheet along with the other material. -- Gary''s Student - gsnu200780 "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. This is the code for recording the change and own its own works well. It is worksheet code. 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 cannot 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with VBA Code - once again
This will trap changes to either cols B or C. You are correct about not
being able to use Offfset. We get to column A explicitly instead: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set ra = Range("B2: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 s2.Cells(n, 4).Value = Cells(t.Row, "A").Value Application.EnableEvents = True End Sub -- Gary''s Student - gsnu200780 "Leanne" wrote: Thank you. I get Device I/O Error - but it works??!! If I want to change the range to look at column B also - would it work if I just changed the range or would I have to repeat it due to the offset? "Gary''s Student" wrote: 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 s2.Cells(n, 4).Value = t.Offset(0, -2).Value Application.EnableEvents = True End Sub This verision gets the customer name from column A and puts in the log sheet along with the other material. -- Gary''s Student - gsnu200780 "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. This is the code for recording the change and own its own works well. It is worksheet code. 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 cannot 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with VBA Code - once again
Thank you so much for your help, I wish I knew more about VBA code and then I
could sort some of the stuff out myself. What do you know about forms? I have a post with a problem with one of them too if you feel like being really helpful!! Thanks again "Gary''s Student" wrote: This will trap changes to either cols B or C. You are correct about not being able to use Offfset. We get to column A explicitly instead: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set ra = Range("B2: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 s2.Cells(n, 4).Value = Cells(t.Row, "A").Value Application.EnableEvents = True End Sub -- Gary''s Student - gsnu200780 "Leanne" wrote: Thank you. I get Device I/O Error - but it works??!! If I want to change the range to look at column B also - would it work if I just changed the range or would I have to repeat it due to the offset? "Gary''s Student" wrote: 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 s2.Cells(n, 4).Value = t.Offset(0, -2).Value Application.EnableEvents = True End Sub This verision gets the customer name from column A and puts in the log sheet along with the other material. -- Gary''s Student - gsnu200780 "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. This is the code for recording the change and own its own works well. It is worksheet code. 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 cannot 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with VBA Code - once again
I wish I could be of help..I know very little about either forms or charting
-- Gary''s Student - gsnu200780 "Leanne" wrote: Thank you so much for your help, I wish I knew more about VBA code and then I could sort some of the stuff out myself. What do you know about forms? I have a post with a problem with one of them too if you feel like being really helpful!! Thanks again "Gary''s Student" wrote: This will trap changes to either cols B or C. You are correct about not being able to use Offfset. We get to column A explicitly instead: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set ra = Range("B2: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 s2.Cells(n, 4).Value = Cells(t.Row, "A").Value Application.EnableEvents = True End Sub -- Gary''s Student - gsnu200780 "Leanne" wrote: Thank you. I get Device I/O Error - but it works??!! If I want to change the range to look at column B also - would it work if I just changed the range or would I have to repeat it due to the offset? "Gary''s Student" wrote: 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 s2.Cells(n, 4).Value = t.Offset(0, -2).Value Application.EnableEvents = True End Sub This verision gets the customer name from column A and puts in the log sheet along with the other material. -- Gary''s Student - gsnu200780 "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. This is the code for recording the change and own its own works well. It is worksheet code. 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 cannot 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with VBA Code - once again
No problem, the help you have provided already has been invaluable. Thanks
"Gary''s Student" wrote: I wish I could be of help..I know very little about either forms or charting -- Gary''s Student - gsnu200780 "Leanne" wrote: Thank you so much for your help, I wish I knew more about VBA code and then I could sort some of the stuff out myself. What do you know about forms? I have a post with a problem with one of them too if you feel like being really helpful!! Thanks again "Gary''s Student" wrote: This will trap changes to either cols B or C. You are correct about not being able to use Offfset. We get to column A explicitly instead: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set ra = Range("B2: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 s2.Cells(n, 4).Value = Cells(t.Row, "A").Value Application.EnableEvents = True End Sub -- Gary''s Student - gsnu200780 "Leanne" wrote: Thank you. I get Device I/O Error - but it works??!! If I want to change the range to look at column B also - would it work if I just changed the range or would I have to repeat it due to the offset? "Gary''s Student" wrote: 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 s2.Cells(n, 4).Value = t.Offset(0, -2).Value Application.EnableEvents = True End Sub This verision gets the customer name from column A and puts in the log sheet along with the other material. -- Gary''s Student - gsnu200780 "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. This is the code for recording the change and own its own works well. It is worksheet code. 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 cannot 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
Shorten code to apply to all sheets except a few, instead of individually naming them, and later adding to code. | Excel Programming | |||
Protect Sheet with code, but then code will not Paste error. How do i get around this. Please read for explainations.... | Excel Programming | |||
Excel code convert to Access code - Concat & eliminate duplicates | Excel Programming |