Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need big help with look up....is this possible
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need big help with look up....is this possible
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need big help with look up....is this possible
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need big help with look up....is this possible
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need big help with look up....is this possible
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need big help with look up....is this possible
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need big help with look up....is this possible
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need big help with look up....is this possible
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need big help with look up....is this possible
I believe you can leave the code as is. Are you getting errors when you try
to use the function? -- Brevity is the soul of wit. "Dan B" wrote: 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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need big help with look up....is this possible
Yeah...a #VALUE! error. I think I'm in over my head on this one.
"Dave F" wrote in message ... I believe you can leave the code as is. Are you getting errors when you try to use the function? -- Brevity is the soul of wit. "Dan B" wrote: 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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need big help with look up....is this possible
What is this function supposed to do for you that the standard VLOOKUP
function will not do? -- Brevity is the soul of wit. "Dan B" wrote: Yeah...a #VALUE! error. I think I'm in over my head on this one. "Dave F" wrote in message ... I believe you can leave the code as is. Are you getting errors when you try to use the function? -- Brevity is the soul of wit. "Dan B" wrote: 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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need big help with look up....is this possible
By the way, is the table in which you are doing this "vlokup" headed with
"Custumer" (what's in your original code) or is it headed with "Customer" (proper spelling)? If it's the latter, then that could cause the #VALUE! error. It's also worth noting that you can determine from receiving this error that (1) Excel is not having a compiling error from your code, and (2) Excel recognizes the UDF. Dave -- Brevity is the soul of wit. "Dan B" wrote: Yeah...a #VALUE! error. I think I'm in over my head on this one. "Dave F" wrote in message ... I believe you can leave the code as is. Are you getting errors when you try to use the function? -- Brevity is the soul of wit. "Dan B" wrote: 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 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need big help with look up....is this possible
Truthfully, I'm not sure. I got the original function as a response to my
posting. Here is my original post in case you haven't seen it. Again...thank you for you help! Hi, I'm building a sheet that pulls data from another worksheet based on a customer number, so when I enter a customer number into cell it will pull specific information. The part I'm stuck on is this....There are varying numbers of rows below each customers' data depending on the number of years they have been a customer....one row per year, starting with the current year. I want to pull data for the pervious year, three years ago, five years ago and the first year they were a customer. The data looks like this: "Cust. #1" "Cust. Name" # Year Premium Losses Claims.....etc =lots more columns 1 2005 1000 50 1 2 2004 1500 10 2 3 2003 1500 0 0 4 2002 1500 15 1 5 2001 1500 15 1 6 2000 1500 10 1 7 1999 1500 0 0 "Cust. #2" "Cust. Name" # Year Premium Losses Claims.....etc =lots more colums 1 2005 500 50 1 2 2004 500 10 2 3 2003 500 0 0 4 2002 500 15 1 The column with the # and numbers is actually part of the data, not the Excel row number. So when I enter a customer number to look up, I need it to look at only the data for that customer and pull rows 1, 3, 5 and the last row, whatever it might be. Is this possible, or am I dreaming?? I hope that makes sense. Thanks, Dan "Dave F" wrote in message ... What is this function supposed to do for you that the standard VLOOKUP function will not do? -- Brevity is the soul of wit. "Dan B" wrote: Yeah...a #VALUE! error. I think I'm in over my head on this one. "Dave F" wrote in message ... I believe you can leave the code as is. Are you getting errors when you try to use the function? -- Brevity is the soul of wit. "Dan B" wrote: 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 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need big help with look up....is this possible
it was misspelled, but I still got the error. I also tried the function you
posted and I got a VB error in the module saying Compile Error End If without Block If. "Dave F" wrote in message ... By the way, is the table in which you are doing this "vlokup" headed with "Custumer" (what's in your original code) or is it headed with "Customer" (proper spelling)? If it's the latter, then that could cause the #VALUE! error. It's also worth noting that you can determine from receiving this error that (1) Excel is not having a compiling error from your code, and (2) Excel recognizes the UDF. Dave -- Brevity is the soul of wit. "Dan B" wrote: Yeah...a #VALUE! error. I think I'm in over my head on this one. "Dave F" wrote in message ... I believe you can leave the code as is. Are you getting errors when you try to use the function? -- Brevity is the soul of wit. "Dan B" wrote: 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 |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need big help with look up....is this possible
Personally, I would just format this data as a data table and make a pivot
table, rather than wrestle with VBA code. To create a data table, all you have to do is create a column, Customer ID and put Cust 1 for your first customer, Cust 2 for your second customer, etc. Then eliminate all empty rows. Keep all column headings from your first customer and delete all other column headings. Then go to Data--Pivot Table and follow the directions. Play around with slicing and dicing the data as you want. For info on pivot tables, see he http://www.cpearson.com/excel/pivots.htm Dave -- Brevity is the soul of wit. "Dan B" wrote: Truthfully, I'm not sure. I got the original function as a response to my posting. Here is my original post in case you haven't seen it. Again...thank you for you help! Hi, I'm building a sheet that pulls data from another worksheet based on a customer number, so when I enter a customer number into cell it will pull specific information. The part I'm stuck on is this....There are varying numbers of rows below each customers' data depending on the number of years they have been a customer....one row per year, starting with the current year. I want to pull data for the pervious year, three years ago, five years ago and the first year they were a customer. The data looks like this: "Cust. #1" "Cust. Name" # Year Premium Losses Claims.....etc =lots more columns 1 2005 1000 50 1 2 2004 1500 10 2 3 2003 1500 0 0 4 2002 1500 15 1 5 2001 1500 15 1 6 2000 1500 10 1 7 1999 1500 0 0 "Cust. #2" "Cust. Name" # Year Premium Losses Claims.....etc =lots more colums 1 2005 500 50 1 2 2004 500 10 2 3 2003 500 0 0 4 2002 500 15 1 The column with the # and numbers is actually part of the data, not the Excel row number. So when I enter a customer number to look up, I need it to look at only the data for that customer and pull rows 1, 3, 5 and the last row, whatever it might be. Is this possible, or am I dreaming?? I hope that makes sense. Thanks, Dan "Dave F" wrote in message ... What is this function supposed to do for you that the standard VLOOKUP function will not do? -- Brevity is the soul of wit. "Dan B" wrote: Yeah...a #VALUE! error. I think I'm in over my head on this one. "Dave F" wrote in message ... I believe you can leave the code as is. Are you getting errors when you try to use the function? -- Brevity is the soul of wit. "Dan B" wrote: 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 |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need big help with look up....is this possible
I'll give that a try. Good suggestion.
Thanks Again! Dan "Dave F" wrote in message ... Personally, I would just format this data as a data table and make a pivot table, rather than wrestle with VBA code. To create a data table, all you have to do is create a column, Customer ID and put Cust 1 for your first customer, Cust 2 for your second customer, etc. Then eliminate all empty rows. Keep all column headings from your first customer and delete all other column headings. Then go to Data--Pivot Table and follow the directions. Play around with slicing and dicing the data as you want. For info on pivot tables, see he http://www.cpearson.com/excel/pivots.htm Dave -- Brevity is the soul of wit. "Dan B" wrote: Truthfully, I'm not sure. I got the original function as a response to my posting. Here is my original post in case you haven't seen it. Again...thank you for you help! Hi, I'm building a sheet that pulls data from another worksheet based on a customer number, so when I enter a customer number into cell it will pull specific information. The part I'm stuck on is this....There are varying numbers of rows below each customers' data depending on the number of years they have been a customer....one row per year, starting with the current year. I want to pull data for the pervious year, three years ago, five years ago and the first year they were a customer. The data looks like this: "Cust. #1" "Cust. Name" # Year Premium Losses Claims.....etc =lots more columns 1 2005 1000 50 1 2 2004 1500 10 2 3 2003 1500 0 0 4 2002 1500 15 1 5 2001 1500 15 1 6 2000 1500 10 1 7 1999 1500 0 0 "Cust. #2" "Cust. Name" # Year Premium Losses Claims.....etc =lots more colums 1 2005 500 50 1 2 2004 500 10 2 3 2003 500 0 0 4 2002 500 15 1 The column with the # and numbers is actually part of the data, not the Excel row number. So when I enter a customer number to look up, I need it to look at only the data for that customer and pull rows 1, 3, 5 and the last row, whatever it might be. Is this possible, or am I dreaming?? I hope that makes sense. Thanks, Dan "Dave F" wrote in message ... What is this function supposed to do for you that the standard VLOOKUP function will not do? -- Brevity is the soul of wit. "Dan B" wrote: Yeah...a #VALUE! error. I think I'm in over my head on this one. "Dave F" wrote in message ... I believe you can leave the code as is. Are you getting errors when you try to use the function? -- Brevity is the soul of wit. "Dan B" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|