RE: Large If function

I made a few changes
1) added comments and made variables names easier to understand
2) I added a for loop so if somebody does a copy and paste all the data will
be put in the table.
3) Added the look up code for the address. You have to modify the Range of
where the Customer Name/ Address table is located. If the address is not one
column over from the Customer name then you have to change the offset in the
code below.

Note I added comments to indicate which two lines of code yo uhave to change.


Private Sub Worksheet_Change(ByVal Target As Range)

'Select Range of target cells
Set ra = Range("C2:C300")
'select *** where table is located
Set s2 = Sheets("Visit History")
'select *** 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


"Leanne" wrote:

Hi Joel,

I am a bit new at code so am not sure how to integrate this into my existing
code - which was kindly provided by someone on this site. I was able to
amend it but it didnt need much.

The code records changes from one *** and places them in another. Where I
see the address of the cell changed I want to know the name of the customer
(all of which have not been decided yet)

I have made a list of all possible address and the Customer Name that would
be associated with it but am not sure about the 'Table' comment. Do you just
mean lable the list?

This is the code I have already.

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


"Joel" wrote:

Build a table on the work*** then use find in your macro

Cust_Name = "John Smith"
set c = sheets("Sheet2").columns("A:A").find(what:=Cust_Name, _
lookin:=xlvalues,lookat:=xlwhole)
if not c is nothing then
Lookup_Data = c.offset(0,1)
end if

"Leanne" wrote:

Hi - I am not sure if this should be posted in programing or not

I know that an if function can only hold 7 veriables so how can I get around
the following situation.

I have a macro which shows me which cell is being changed so contains
$C$2.....etc

What I need is to translate all these into customer names and the only way I
can think of is with an If function but I have 300 possible answers so know
this will not work.

Thanks
.

Quantcast