Excelent,

Could you explain a little more on how to use this. Its a little over my
experience level, but I'd love to know how to do this. So far I have
created a new module and pasted you code. I then changed each Sheet1! to
Losses! (the name of the worksheet with the data). This is the only change
I made on your code. I get an error saying Compile Error: Expected: line
number or label or statement or end of statement. Was there something else
needing changes?

Thanks
Dan



try this function. but it depend on that ur custumernames are in column A
and the cell just below is empty, then a cell with #
just like it seems to be in ur example
otherwise the function have to be modifyed a bit.

put in a regular module then use like this:
=Vlokup("Cust. #1",Sheet1!A1:A100,1,2) - return 2005 (column B)
=Vlokup("Cust. #1",Sheet1!A1:A100,3,2) - return 2003 (column B)
=Vlokup("Cust. #1",Sheet1!A1:A100,0,2) - return 1999 (0=first year)
=Vlokup("Cust. #2",Sheet1!A1:A100,5,4) - return 15 (column D)

Function Vlokup(Custumer As String, MyRange As Range, YearNumber, Col)
Dim Mysheet
Dim c As Range, Last
Set Mysheet = MyRange.Parent
Set c = MyRange
Application.Volatile
If YearNumber < 0 Then
Vlokup = Mysheet.Cells(c.Find(Custumer, LookIn:=xlValues).Row +
YearNumber + 2, Col)
End If
If YearNumber = 0 Then
Last = c.Find(Custumer, LookIn:=xlValues).Offset(2, 0).End(xlDown).Row
Vlokup = Mysheet.Cells(Last, Col)
End If
End Function

What is the code you're using?
--
Brevity is the soul of wit.


"Dan B" wrote:

Excelent,

Could you explain a little more on how to use this. Its a little over my
experience level, but I'd love to know how to do this. So far I have
created a new module and pasted you code. I then changed each Sheet1! to
Losses! (the name of the worksheet with the data). This is the only change
I made on your code. I get an error saying Compile Error: Expected: line
number or label or statement or end of statement. Was there something else
needing changes?

Thanks
Dan



try this function. but it depend on that ur custumernames are in column A
and the cell just below is empty, then a cell with #
just like it seems to be in ur example
otherwise the function have to be modifyed a bit.

put in a regular module then use like this:
=Vlokup("Cust. #1",Sheet1!A1:A100,1,2) - return 2005 (column B)
=Vlokup("Cust. #1",Sheet1!A1:A100,3,2) - return 2003 (column B)
=Vlokup("Cust. #1",Sheet1!A1:A100,0,2) - return 1999 (0=first year)
=Vlokup("Cust. #2",Sheet1!A1:A100,5,4) - return 15 (column D)

Function Vlokup(Custumer As String, MyRange As Range, YearNumber, Col)
Dim Mysheet
Dim c As Range, Last
Set Mysheet = MyRange.Parent
Set c = MyRange
Application.Volatile
If YearNumber < 0 Then
Vlokup = Mysheet.Cells(c.Find(Custumer, LookIn:=xlValues).Row +
YearNumber + 2, Col)
End If
If YearNumber = 0 Then
Last = c.Find(Custumer, LookIn:=xlValues).Offset(2, 0).End(xlDown).Row
Vlokup = Mysheet.Cells(Last, Col)
End If
End Function

this is it:

=Vlokup("Cust. #1",Sheet1!A1:A100,1,2) - return 2005 (column B)
=Vlokup("Cust. #1",Sheet1!A1:A100,3,2) - return 2003 (column B)
=Vlokup("Cust. #1",Sheet1!A1:A100,0,2) - return 1999 (0=first year)
=Vlokup("Cust. #2",Sheet1!A1:A100,5,4) - return 15 (column D)

Function Vlokup(Custumer As String, MyRange As Range, YearNumber, Col)
Dim Mysheet
Dim c As Range, Last
Set Mysheet = MyRange.Parent
Set c = MyRange
Application.Volatile
If YearNumber < 0 Then
Vlokup = Mysheet.Cells(c.Find(Custumer, LookIn:=xlValues).Row +
YearNumber + 2, Col)
End If
If YearNumber = 0 Then
Last = c.Find(Custumer, LookIn:=xlValues).Offset(2, 0).End(xlDown).Row
Vlokup = Mysheet.Cells(Last, Col)
End If
End Function




"Dave F" wrote in message
...
What is the code you're using?
--
Brevity is the soul of wit.


"Dan B" wrote:

Excelent,

Could you explain a little more on how to use this. Its a little over my
experience level, but I'd love to know how to do this. So far I have
created a new module and pasted you code. I then changed each Sheet1! to
Losses! (the name of the worksheet with the data). This is the only
change
I made on your code. I get an error saying Compile Error: Expected: line
number or label or statement or end of statement. Was there something
else
needing changes?

Thanks
Dan



try this function. but it depend on that ur custumernames are in column A
and the cell just below is empty, then a cell with #
just like it seems to be in ur example
otherwise the function have to be modifyed a bit.

put in a regular module then use like this:
=Vlokup("Cust. #1",Sheet1!A1:A100,1,2) - return 2005 (column B)
=Vlokup("Cust. #1",Sheet1!A1:A100,3,2) - return 2003 (column B)
=Vlokup("Cust. #1",Sheet1!A1:A100,0,2) - return 1999 (0=first year)
=Vlokup("Cust. #2",Sheet1!A1:A100,5,4) - return 15 (column D)

Function Vlokup(Custumer As String, MyRange As Range, YearNumber, Col)
Dim Mysheet
Dim c As Range, Last
Set Mysheet = MyRange.Parent
Set c = MyRange
Application.Volatile
If YearNumber < 0 Then
Vlokup = Mysheet.Cells(c.Find(Custumer, LookIn:=xlValues).Row +
YearNumber + 2, Col)
End If
If YearNumber = 0 Then
Last = c.Find(Custumer, LookIn:=xlValues).Offset(2,
0).End(xlDown).Row
Vlokup = Mysheet.Cells(Last, Col)
End If
End Function

Hi, you say you changed the code to show the sheet name Losses! but I don't
see that in your code?

Dave
--
Brevity is the soul of wit.


"Dan B" wrote:

this is it:

=Vlokup("Cust. #1",Sheet1!A1:A100,1,2) - return 2005 (column B)
=Vlokup("Cust. #1",Sheet1!A1:A100,3,2) - return 2003 (column B)
=Vlokup("Cust. #1",Sheet1!A1:A100,0,2) - return 1999 (0=first year)
=Vlokup("Cust. #2",Sheet1!A1:A100,5,4) - return 15 (column D)

Function Vlokup(Custumer As String, MyRange As Range, YearNumber, Col)
Dim Mysheet
Dim c As Range, Last
Set Mysheet = MyRange.Parent
Set c = MyRange
Application.Volatile
If YearNumber < 0 Then
Vlokup = Mysheet.Cells(c.Find(Custumer, LookIn:=xlValues).Row +
YearNumber + 2, Col)
End If
If YearNumber = 0 Then
Last = c.Find(Custumer, LookIn:=xlValues).Offset(2, 0).End(xlDown).Row
Vlokup = Mysheet.Cells(Last, Col)
End If
End Function




"Dave F" wrote in message
...
What is the code you're using?
--
Brevity is the soul of wit.


"Dan B" wrote:

Excelent,

Could you explain a little more on how to use this. Its a little over my
experience level, but I'd love to know how to do this. So far I have
created a new module and pasted you code. I then changed each Sheet1! to
Losses! (the name of the worksheet with the data). This is the only
change
I made on your code. I get an error saying Compile Error: Expected: line
number or label or statement or end of statement. Was there something
else
needing changes?

Thanks
Dan



try this function. but it depend on that ur custumernames are in column A
and the cell just below is empty, then a cell with #
just like it seems to be in ur example
otherwise the function have to be modifyed a bit.

put in a regular module then use like this:
=Vlokup("Cust. #1",Sheet1!A1:A100,1,2) - return 2005 (column B)
=Vlokup("Cust. #1",Sheet1!A1:A100,3,2) - return 2003 (column B)
=Vlokup("Cust. #1",Sheet1!A1:A100,0,2) - return 1999 (0=first year)
=Vlokup("Cust. #2",Sheet1!A1:A100,5,4) - return 15 (column D)

Function Vlokup(Custumer As String, MyRange As Range, YearNumber, Col)
Dim Mysheet
Dim c As Range, Last
Set Mysheet = MyRange.Parent
Set c = MyRange
Application.Volatile
If YearNumber < 0 Then
Vlokup = Mysheet.Cells(c.Find(Custumer, LookIn:=xlValues).Row +
YearNumber + 2, Col)
End If
If YearNumber = 0 Then
Last = c.Find(Custumer, LookIn:=xlValues).Offset(2,
0).End(xlDown).Row
Vlokup = Mysheet.Cells(Last, Col)
End If
End Function

sorry...I copied the wrong one. Here it is:

=Vlokup("Cust. #1",Losses!A16:A2600,1,2) - return 2005 (column B)
=Vlokup("Cust. #1",Losses!A16:A2600,3,2) - return 2003 (column B)
=Vlokup("Cust. #1",Losses!A16:A2600,0,2) - return 1999 (0=first year)
=Vlokup("Cust. #2",Losses!A16:A2600,5,4) - return 15 (column D)

Function Vlokup(Custumer As String, MyRange As Range, YearNumber, Col)
Dim Mysheet
Dim c As Range, Last
Set Mysheet = MyRange.Parent
Set c = MyRange
Application.Volatile
If YearNumber < 0 Then
Vlokup = Mysheet.Cells(c.Find(Custumer, LookIn:=xlValues).Row +
YearNumber + 2, Col)
End If
If YearNumber = 0 Then
Last = c.Find(Custumer, LookIn:=xlValues).Offset(2, 0).End(xlDown).Row
Vlokup = Mysheet.Cells(Last, Col)
End If
End Function



"Dave F" wrote in message
...
Hi, you say you changed the code to show the sheet name Losses! but I
don't
see that in your code?

Dave
--
Brevity is the soul of wit.


"Dan B" wrote:

this is it:

=Vlokup("Cust. #1",Sheet1!A1:A100,1,2) - return 2005 (column B)
=Vlokup("Cust. #1",Sheet1!A1:A100,3,2) - return 2003 (column B)
=Vlokup("Cust. #1",Sheet1!A1:A100,0,2) - return 1999 (0=first year)
=Vlokup("Cust. #2",Sheet1!A1:A100,5,4) - return 15 (column D)

Function Vlokup(Custumer As String, MyRange As Range, YearNumber, Col)
Dim Mysheet
Dim c As Range, Last
Set Mysheet = MyRange.Parent
Set c = MyRange
Application.Volatile
If YearNumber < 0 Then
Vlokup = Mysheet.Cells(c.Find(Custumer, LookIn:=xlValues).Row +
YearNumber + 2, Col)
End If
If YearNumber = 0 Then
Last = c.Find(Custumer, LookIn:=xlValues).Offset(2,
0).End(xlDown).Row
Vlokup = Mysheet.Cells(Last, Col)
End If
End Function




"Dave F" wrote in message
...
What is the code you're using?
--
Brevity is the soul of wit.


"Dan B" wrote:

Excelent,

Could you explain a little more on how to use this. Its a little over
my
experience level, but I'd love to know how to do this. So far I have
created a new module and pasted you code. I then changed each Sheet1!
to
Losses! (the name of the worksheet with the data). This is the only
change
I made on your code. I get an error saying Compile Error: Expected:
line
number or label or statement or end of statement. Was there something
else
needing changes?

Thanks
Dan



try this function. but it depend on that ur custumernames are in
column A
and the cell just below is empty, then a cell with #
just like it seems to be in ur example
otherwise the function have to be modifyed a bit.

put in a regular module then use like this:
=Vlokup("Cust. #1",Sheet1!A1:A100,1,2) - return 2005 (column B)
=Vlokup("Cust. #1",Sheet1!A1:A100,3,2) - return 2003 (column B)
=Vlokup("Cust. #1",Sheet1!A1:A100,0,2) - return 1999 (0=first year)
=Vlokup("Cust. #2",Sheet1!A1:A100,5,4) - return 15 (column D)

Function Vlokup(Custumer As String, MyRange As Range, YearNumber, Col)
Dim Mysheet
Dim c As Range, Last
Set Mysheet = MyRange.Parent
Set c = MyRange
Application.Volatile
If YearNumber < 0 Then
Vlokup = Mysheet.Cells(c.Find(Custumer, LookIn:=xlValues).Row +
YearNumber + 2, Col)
End If
If YearNumber = 0 Then
Last = c.Find(Custumer, LookIn:=xlValues).Offset(2,
0).End(xlDown).Row
Vlokup = Mysheet.Cells(Last, Col)
End If
End Function

The first thing I would try is this code:

Function Vlokup(Custumer As String, MyRange As Range, YearNumber, Col)
Dim Mysheet
Dim c As Range, Last
Set Mysheet = MyRange.Parent
Set c = MyRange
Application.Volatile
If YearNumber < 0 Then Vlokup = Mysheet.Cells(c.Find(Custumer,
LookIn:=xlValues).Row + YearNumber + 2, Col)
End If
If YearNumber = 0 Then
Last = c.Find(Custumer, LookIn:=xlValues).Offset(2, 0).End(xlDown).Row
Vlokup = Mysheet.Cells(Last, Col)
End If
End Function

You have a carriage return in your code at the point where you're adding
..Row + YearNumber + 2 and Excel doesn't know how to handle that because it's
expecting you to finish the math in the line.

So, make sure that a line is not interrupted with a carriage return.

Also note that the lines at the top of your code which begin with vlokup are
not part of the code but rather the syntax of the function when you use it in
a workbook.

Dave
--
Brevity is the soul of wit.


"Dan B" wrote:

this is it:

=Vlokup("Cust. #1",Sheet1!A1:A100,1,2) - return 2005 (column B)
=Vlokup("Cust. #1",Sheet1!A1:A100,3,2) - return 2003 (column B)
=Vlokup("Cust. #1",Sheet1!A1:A100,0,2) - return 1999 (0=first year)
=Vlokup("Cust. #2",Sheet1!A1:A100,5,4) - return 15 (column D)

Function Vlokup(Custumer As String, MyRange As Range, YearNumber, Col)
Dim Mysheet
Dim c As Range, Last
Set Mysheet = MyRange.Parent
Set c = MyRange
Application.Volatile
If YearNumber < 0 Then
Vlokup = Mysheet.Cells(c.Find(Custumer, LookIn:=xlValues).Row +
YearNumber + 2, Col)
End If
If YearNumber = 0 Then
Last = c.Find(Custumer, LookIn:=xlValues).Offset(2, 0).End(xlDown).Row
Vlokup = Mysheet.Cells(Last, Col)
End If
End Function




"Dave F" wrote in message
...
What is the code you're using?
--
Brevity is the soul of wit.


"Dan B" wrote:

Excelent,

Could you explain a little more on how to use this. Its a little over my
experience level, but I'd love to know how to do this. So far I have
created a new module and pasted you code. I then changed each Sheet1! to
Losses! (the name of the worksheet with the data). This is the only
change
I made on your code. I get an error saying Compile Error: Expected: line
number or label or statement or end of statement. Was there something
else
needing changes?

Thanks
Dan



try this function. but it depend on that ur custumernames are in column A
and the cell just below is empty, then a cell with #
just like it seems to be in ur example
otherwise the function have to be modifyed a bit.

put in a regular module then use like this:
=Vlokup("Cust. #1",Sheet1!A1:A100,1,2) - return 2005 (column B)
=Vlokup("Cust. #1",Sheet1!A1:A100,3,2) - return 2003 (column B)
=Vlokup("Cust. #1",Sheet1!A1:A100,0,2) - return 1999 (0=first year)
=Vlokup("Cust. #2",Sheet1!A1:A100,5,4) - return 15 (column D)

Function Vlokup(Custumer As String, MyRange As Range, YearNumber, Col)
Dim Mysheet
Dim c As Range, Last
Set Mysheet = MyRange.Parent
Set c = MyRange
Application.Volatile
If YearNumber < 0 Then
Vlokup = Mysheet.Cells(c.Find(Custumer, LookIn:=xlValues).Row +
YearNumber + 2, Col)
End If
If YearNumber = 0 Then
Last = c.Find(Custumer, LookIn:=xlValues).Offset(2,
0).End(xlDown).Row
Vlokup = Mysheet.Cells(Last, Col)
End If
End Function

Ok, I fixed the carriage return. That was a problem. I'll take the
functions out and see what that does. I will also give your suggestion a
try.

Thanks.


"Dave F" wrote in message
...
The first thing I would try is this code:

Function Vlokup(Custumer As String, MyRange As Range, YearNumber, Col)
Dim Mysheet
Dim c As Range, Last
Set Mysheet = MyRange.Parent
Set c = MyRange
Application.Volatile
If YearNumber < 0 Then Vlokup = Mysheet.Cells(c.Find(Custumer,
LookIn:=xlValues).Row + YearNumber + 2, Col)
End If
If YearNumber = 0 Then
Last = c.Find(Custumer, LookIn:=xlValues).Offset(2,
0).End(xlDown).Row
Vlokup = Mysheet.Cells(Last, Col)
End If
End Function

You have a carriage return in your code at the point where you're adding
.Row + YearNumber + 2 and Excel doesn't know how to handle that because
it's
expecting you to finish the math in the line.

So, make sure that a line is not interrupted with a carriage return.

Also note that the lines at the top of your code which begin with vlokup
are
not part of the code but rather the syntax of the function when you use it
in
a workbook.

Dave
--
Brevity is the soul of wit.


"Dan B" wrote:

this is it:

=Vlokup("Cust. #1",Sheet1!A1:A100,1,2) - return 2005 (column B)
=Vlokup("Cust. #1",Sheet1!A1:A100,3,2) - return 2003 (column B)
=Vlokup("Cust. #1",Sheet1!A1:A100,0,2) - return 1999 (0=first year)
=Vlokup("Cust. #2",Sheet1!A1:A100,5,4) - return 15 (column D)

Function Vlokup(Custumer As String, MyRange As Range, YearNumber, Col)
Dim Mysheet
Dim c As Range, Last
Set Mysheet = MyRange.Parent
Set c = MyRange
Application.Volatile
If YearNumber < 0 Then
Vlokup = Mysheet.Cells(c.Find(Custumer, LookIn:=xlValues).Row +
YearNumber + 2, Col)
End If
If YearNumber = 0 Then
Last = c.Find(Custumer, LookIn:=xlValues).Offset(2,
0).End(xlDown).Row
Vlokup = Mysheet.Cells(Last, Col)
End If
End Function




"Dave F" wrote in message
...
What is the code you're using?
--
Brevity is the soul of wit.


"Dan B" wrote:

Excelent,

Could you explain a little more on how to use this. Its a little over
my
experience level, but I'd love to know how to do this. So far I have
created a new module and pasted you code. I then changed each Sheet1!
to
Losses! (the name of the worksheet with the data). This is the only
change
I made on your code. I get an error saying Compile Error: Expected:
line
number or label or statement or end of statement. Was there something
else
needing changes?

Thanks
Dan



try this function. but it depend on that ur custumernames are in
column A
and the cell just below is empty, then a cell with #
just like it seems to be in ur example
otherwise the function have to be modifyed a bit.

put in a regular module then use like this:
=Vlokup("Cust. #1",Sheet1!A1:A100,1,2) - return 2005 (column B)
=Vlokup("Cust. #1",Sheet1!A1:A100,3,2) - return 2003 (column B)
=Vlokup("Cust. #1",Sheet1!A1:A100,0,2) - return 1999 (0=first year)
=Vlokup("Cust. #2",Sheet1!A1:A100,5,4) - return 15 (column D)

Function Vlokup(Custumer As String, MyRange As Range, YearNumber, Col)
Dim Mysheet
Dim c As Range, Last
Set Mysheet = MyRange.Parent
Set c = MyRange
Application.Volatile
If YearNumber < 0 Then
Vlokup = Mysheet.Cells(c.Find(Custumer, LookIn:=xlValues).Row +
YearNumber + 2, Col)
End If
If YearNumber = 0 Then
Last = c.Find(Custumer, LookIn:=xlValues).Offset(2,
0).End(xlDown).Row
Vlokup = Mysheet.Cells(Last, Col)
End If
End Function

Dave,

Can I just copy the code as is, or do I need to edit things like
MyRange.Parent or MySheet.Cells. I'm trying to get a grasp on the
functionality of all this.
I appreciate your help.

Dan


"Dan B" wrote in message
...
Ok, I fixed the carriage return. That was a problem. I'll take the
functions out and see what that does. I will also give your suggestion a
try.

Thanks.


"Dave F" wrote in message
...
The first thing I would try is this code:

Function Vlokup(Custumer As String, MyRange As Range, YearNumber, Col)
Dim Mysheet
Dim c As Range, Last
Set Mysheet = MyRange.Parent
Set c = MyRange
Application.Volatile
If YearNumber < 0 Then Vlokup = Mysheet.Cells(c.Find(Custumer,
LookIn:=xlValues).Row + YearNumber + 2, Col)
End If
If YearNumber = 0 Then
Last = c.Find(Custumer, LookIn:=xlValues).Offset(2,
0).End(xlDown).Row
Vlokup = Mysheet.Cells(Last, Col)
End If
End Function

You have a carriage return in your code at the point where you're adding
.Row + YearNumber + 2 and Excel doesn't know how to handle that because
it's
expecting you to finish the math in the line.

So, make sure that a line is not interrupted with a carriage return.

Also note that the lines at the top of your code which begin with vlokup
are
not part of the code but rather the syntax of the function when you use
it in
a workbook.

Dave
--
Brevity is the soul of wit.


"Dan B" wrote:

this is it:

=Vlokup("Cust. #1",Sheet1!A1:A100,1,2) - return 2005 (column B)
=Vlokup("Cust. #1",Sheet1!A1:A100,3,2) - return 2003 (column B)
=Vlokup("Cust. #1",Sheet1!A1:A100,0,2) - return 1999 (0=first year)
=Vlokup("Cust. #2",Sheet1!A1:A100,5,4) - return 15 (column D)

Function Vlokup(Custumer As String, MyRange As Range, YearNumber, Col)
Dim Mysheet
Dim c As Range, Last
Set Mysheet = MyRange.Parent
Set c = MyRange
Application.Volatile
If YearNumber < 0 Then
Vlokup = Mysheet.Cells(c.Find(Custumer, LookIn:=xlValues).Row +
YearNumber + 2, Col)
End If
If YearNumber = 0 Then
Last = c.Find(Custumer, LookIn:=xlValues).Offset(2,
0).End(xlDown).Row
Vlokup = Mysheet.Cells(Last, Col)
End If
End Function




"Dave F" wrote in message
...
What is the code you're using?
--
Brevity is the soul of wit.


"Dan B" wrote:

Excelent,

Could you explain a little more on how to use this. Its a little
over my
experience level, but I'd love to know how to do this. So far I have
created a new module and pasted you code. I then changed each
Sheet1! to
Losses! (the name of the worksheet with the data). This is the only
change
I made on your code. I get an error saying Compile Error: Expected:
line
number or label or statement or end of statement. Was there
something
else
needing changes?

Thanks
Dan



try this function. but it depend on that ur custumernames are in
column A
and the cell just below is empty, then a cell with #
just like it seems to be in ur example
otherwise the function have to be modifyed a bit.

put in a regular module then use like this:
=Vlokup("Cust. #1",Sheet1!A1:A100,1,2) - return 2005 (column B)
=Vlokup("Cust. #1",Sheet1!A1:A100,3,2) - return 2003 (column B)
=Vlokup("Cust. #1",Sheet1!A1:A100,0,2) - return 1999 (0=first year)
=Vlokup("Cust. #2",Sheet1!A1:A100,5,4) - return 15 (column D)

Function Vlokup(Custumer As String, MyRange As Range, YearNumber,
Col)
Dim Mysheet
Dim c As Range, Last
Set Mysheet = MyRange.Parent
Set c = MyRange
Application.Volatile
If YearNumber < 0 Then
Vlokup = Mysheet.Cells(c.Find(Custumer, LookIn:=xlValues).Row +
YearNumber + 2, Col)
End If
If YearNumber = 0 Then
Last = c.Find(Custumer, LookIn:=xlValues).Offset(2,
0).End(xlDown).Row
Vlokup = Mysheet.Cells(Last, Col)
End If
End Function