Large If function
 

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

Large If function
 

Build a table on the worksheet then use find in your macro

Cust_Name = "John Smith"
set c = sheets("Sheet2").columns("A:A").find(what:=Cust_Na me, _
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

Large If function
 

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 sheet 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 worksheet then use find in your macro

Cust_Name = "John Smith"
set c = sheets("Sheet2").columns("A:A").find(what:=Cust_Na me, _
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

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 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


"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 sheet 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 worksheet then use find in your macro

Cust_Name = "John Smith"
set c = sheets("Sheet2").columns("A:A").find(what:=Cust_Na me, _
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

Large If function
 

Hi - I think I am having a bad day and should really leave this but it is
bugging me.
I have sheet 'Dates' where this code is in worksheet code. This takes
changes to column c and puts it in sheet 'Visit History' as below.
01/05/2008 16/04/2008 $C$2
This tells me what data has been entered, when it was changed, and the
address of the cell that was changed.
I can tell that this means the information for customer Marchwood ERF was
changed as this customers information is recorded in row 2.
Eventually I will want to run a report on this data but can not show changes
to customer $C$2 as it will mean nothing to other people. Hence I wanted to
do
=IF(C2=$C$2,"Marchwood ERF",IF(C2=$C$3,"Portsmouth ERF")) and so on and so
forth but realsise this can not be done for 300 entries.

When I refered to address I meant to say Cell Address

Sorry for being difficult.
"Joel" wrote:

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 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


"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 sheet 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 worksheet then use find in your macro

Cust_Name = "John Smith"
set c = sheets("Sheet2").columns("A:A").find(what:=Cust_Na me, _
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

Large If function
 

You need to build a table or List what every you want to call it.

Row Col A Col B
1 $C$2 Marchwood ERF
2 $C$3 Portsmouth ERF
3
4
5

if $C$2 is really a cell address then
Data = Range("C3")

Then you can use a look up function.
"Leanne" wrote:

Hi - I think I am having a bad day and should really leave this but it is
bugging me.
I have sheet 'Dates' where this code is in worksheet code. This takes
changes to column c and puts it in sheet 'Visit History' as below.
01/05/2008 16/04/2008 $C$2
This tells me what data has been entered, when it was changed, and the
address of the cell that was changed.
I can tell that this means the information for customer Marchwood ERF was
changed as this customers information is recorded in row 2.
Eventually I will want to run a report on this data but can not show changes
to customer $C$2 as it will mean nothing to other people. Hence I wanted to
do
=IF(C2=$C$2,"Marchwood ERF",IF(C2=$C$3,"Portsmouth ERF")) and so on and so
forth but realsise this can not be done for 300 entries.

When I refered to address I meant to say Cell Address

Sorry for being difficult.
"Joel" wrote:

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 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


"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 sheet 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 worksheet then use find in your macro

Cust_Name = "John Smith"
set c = sheets("Sheet2").columns("A:A").find(what:=Cust_Na me, _
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