Help with VBA Code - once again

[Leanne]

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.

 

[Gary''s Student]

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.

 

[Leanne]

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]

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

 

[Leanne]

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]

I wish I could be of help..I know very little about either forms or charting

 

[Leanne]

No problem, the help you have provided already has been invaluable. Thanks