Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with lookups via VBA
I'm trying to use the Find method to look up values in tables according to
the date. The first column holds the date, and columns b - j hold the numerical data. All columns and rows are in the named range "Data". Here's the code I've written - Sub Lookup() Dim vResult As Variant datein = InputBox("Enter Target Date") If datein 0 Then datein = DateValue(datein) End If MsgBox "Date = " & datein If WorksheetFunction.CountIf(Sheet1.Range("Data"), datein) 0 Then With Sheet1.Range("Data") vResult = .Find(What:=(datein), After:=.Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlNext).Offset(0, 2) End With MsgBox vResult End If End Sub The error tells is 91: Object or with variable not set - I have read that this may not have to do with the variables, but on mouseover, the variable vResult is empty. Any help would be appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with lookups via VBA
try this -- i believe the problem was putting the variable called Data in ""
If WorksheetFunction.CountIf(Sheets("Sheet1").Range(D ata), datein) 0 Then "Roy Kirkland" wrote: I'm trying to use the Find method to look up values in tables according to the date. The first column holds the date, and columns b - j hold the numerical data. All columns and rows are in the named range "Data". Here's the code I've written - Sub Lookup() Dim vResult As Variant datein = InputBox("Enter Target Date") If datein 0 Then datein = DateValue(datein) End If MsgBox "Date = " & datein If WorksheetFunction.CountIf(Sheet1.Range("Data"), datein) 0 Then With Sheet1.Range("Data") vResult = .Find(What:=(datein), After:=.Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlNext).Offset(0, 2) End With MsgBox vResult End If End Sub The error tells is 91: Object or with variable not set - I have read that this may not have to do with the variables, but on mouseover, the variable vResult is empty. Any help would be appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with lookups via VBA
Thanks, but I pasted in the code and got a new error - RunTime 1004 -
Application-defined or Object-defined error - I'm still trying variations on it though. Roy *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with lookups via VBA
vResult is actually a range (the cell where the date is found).
You could drop the .countif() and just use .find. Option Explicit Sub myLookup() Dim vResult As Range Dim DateIn As Variant DateIn = InputBox("Enter Target Date") If IsDate(DateIn) Then DateIn = DateValue(DateIn) Else Exit Sub End If MsgBox "Date = " & DateIn With Sheet1.Range("Data") Set vResult = .Find(What:=CLng(DateIn), After:=.Cells(1, 1), _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext).Offset(0, 2) End With If vResult Is Nothing Then MsgBox "not Found" Else MsgBox vResult.Value End If End Sub And sometimes dates are easier to find if you search for the numeric representation (clng(datein)). And I changed the name of your sub. =Lookup() is a worksheet function and I don't like to use words that excel likes to use. Roy Kirkland wrote: I'm trying to use the Find method to look up values in tables according to the date. The first column holds the date, and columns b - j hold the numerical data. All columns and rows are in the named range "Data". Here's the code I've written - Sub Lookup() Dim vResult As Variant datein = InputBox("Enter Target Date") If datein 0 Then datein = DateValue(datein) End If MsgBox "Date = " & datein If WorksheetFunction.CountIf(Sheet1.Range("Data"), datein) 0 Then With Sheet1.Range("Data") vResult = .Find(What:=(datein), After:=.Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlNext).Offset(0, 2) End With MsgBox vResult End If End Sub The error tells is 91: Object or with variable not set - I have read that this may not have to do with the variables, but on mouseover, the variable vResult is empty. Any help would be appreciated. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with lookups via VBA
I tried it Dave, but still got the same error. The line with the Set
statement gives the error 91 (variable not set) when the program flow reaches it. I've tried several variations of this code and am at a dead end. Any links to various lookup routines? Thanks Roy "Dave Peterson" wrote in message ... vResult is actually a range (the cell where the date is found). You could drop the .countif() and just use .find. Option Explicit Sub myLookup() Dim vResult As Range Dim DateIn As Variant DateIn = InputBox("Enter Target Date") If IsDate(DateIn) Then DateIn = DateValue(DateIn) Else Exit Sub End If MsgBox "Date = " & DateIn With Sheet1.Range("Data") Set vResult = .Find(What:=CLng(DateIn), After:=.Cells(1, 1), _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext).Offset(0, 2) End With If vResult Is Nothing Then MsgBox "not Found" Else MsgBox vResult.Value End If End Sub And sometimes dates are easier to find if you search for the numeric representation (clng(datein)). And I changed the name of your sub. =Lookup() is a worksheet function and I don't like to use words that excel likes to use. Roy Kirkland wrote: I'm trying to use the Find method to look up values in tables according to the date. The first column holds the date, and columns b - j hold the numerical data. All columns and rows are in the named range "Data". Here's the code I've written - Sub Lookup() Dim vResult As Variant datein = InputBox("Enter Target Date") If datein 0 Then datein = DateValue(datein) End If MsgBox "Date = " & datein If WorksheetFunction.CountIf(Sheet1.Range("Data"), datein) 0 Then With Sheet1.Range("Data") vResult = .Find(What:=(datein), After:=.Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlNext).Offset(0, 2) End With MsgBox vResult End If End Sub The error tells is 91: Object or with variable not set - I have read that this may not have to do with the variables, but on mouseover, the variable vResult is empty. Any help would be appreciated. -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with lookups via VBA
Hi Roy,
Try changing: SearchDirection:=xlNext).Offset(0, 2) to SearchDirection:=xlNext) --- Regards, Norman "Roy Kirkland" wrote in message ... I tried it Dave, but still got the same error. The line with the Set statement gives the error 91 (variable not set) when the program flow reaches it. I've tried several variations of this code and am at a dead end. Any links to various lookup routines? Thanks Roy "Dave Peterson" wrote in message ... vResult is actually a range (the cell where the date is found). You could drop the .countif() and just use .find. Option Explicit Sub myLookup() Dim vResult As Range Dim DateIn As Variant DateIn = InputBox("Enter Target Date") If IsDate(DateIn) Then DateIn = DateValue(DateIn) Else Exit Sub End If MsgBox "Date = " & DateIn With Sheet1.Range("Data") Set vResult = .Find(What:=CLng(DateIn), After:=.Cells(1, 1), _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext).Offset(0, 2) End With If vResult Is Nothing Then MsgBox "not Found" Else MsgBox vResult.Value End If End Sub And sometimes dates are easier to find if you search for the numeric representation (clng(datein)). And I changed the name of your sub. =Lookup() is a worksheet function and I don't like to use words that excel likes to use. Roy Kirkland wrote: I'm trying to use the Find method to look up values in tables according to the date. The first column holds the date, and columns b - j hold the numerical data. All columns and rows are in the named range "Data". Here's the code I've written - Sub Lookup() Dim vResult As Variant datein = InputBox("Enter Target Date") If datein 0 Then datein = DateValue(datein) End If MsgBox "Date = " & datein If WorksheetFunction.CountIf(Sheet1.Range("Data"), datein) 0 Then With Sheet1.Range("Data") vResult = .Find(What:=(datein), After:=.Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlNext).Offset(0, 2) End With MsgBox vResult End If End Sub The error tells is 91: Object or with variable not set - I have read that this may not have to do with the variables, but on mouseover, the variable vResult is empty. Any help would be appreciated. -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with lookups via VBA
OK, but if I do this, how do I get the value from the 2nd cell to the right
of the date? I tried adding a statement: If Not IsEmpty(vResult) Then ThisNum = vResult.Offset(0,1) But still end up with error 91 on the 'ThisNum' line., and the program stops there. Roy "Norman Jones" wrote in message ... Hi Roy, Try changing: SearchDirection:=xlNext).Offset(0, 2) to SearchDirection:=xlNext) --- Regards, Norman "Roy Kirkland" wrote in message ... I tried it Dave, but still got the same error. The line with the Set statement gives the error 91 (variable not set) when the program flow reaches it. I've tried several variations of this code and am at a dead end. Any links to various lookup routines? Thanks Roy "Dave Peterson" wrote in message ... vResult is actually a range (the cell where the date is found). You could drop the .countif() and just use .find. Option Explicit Sub myLookup() Dim vResult As Range Dim DateIn As Variant DateIn = InputBox("Enter Target Date") If IsDate(DateIn) Then DateIn = DateValue(DateIn) Else Exit Sub End If MsgBox "Date = " & DateIn With Sheet1.Range("Data") Set vResult = .Find(What:=CLng(DateIn), After:=.Cells(1, 1), _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext).Offset(0, 2) End With If vResult Is Nothing Then MsgBox "not Found" Else MsgBox vResult.Value End If End Sub And sometimes dates are easier to find if you search for the numeric representation (clng(datein)). And I changed the name of your sub. =Lookup() is a worksheet function and I don't like to use words that excel likes to use. Roy Kirkland wrote: I'm trying to use the Find method to look up values in tables according to the date. The first column holds the date, and columns b - j hold the numerical data. All columns and rows are in the named range "Data". Here's the code I've written - Sub Lookup() Dim vResult As Variant datein = InputBox("Enter Target Date") If datein 0 Then datein = DateValue(datein) End If MsgBox "Date = " & datein If WorksheetFunction.CountIf(Sheet1.Range("Data"), datein) 0 Then With Sheet1.Range("Data") vResult = .Find(What:=(datein), After:=.Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlNext).Offset(0, 2) End With MsgBox vResult End If End Sub The error tells is 91: Object or with variable not set - I have read that this may not have to do with the variables, but on mouseover, the variable vResult is empty. Any help would be appreciated. -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with lookups via VBA
Hi Roy,
Not IsEmpty(vResult) Then ThisNum If = vResult.Offset(0,1) Try instead: If not vResult is Nothing Then ThisNum = vResult.Offset(0,2).Value End If Incidentally, if the date cells in the named data range are formatted as dates, you should replace: Set vResult = .Find(What:=CLng(DateIn) with Set vResult = .Find(What:=(DateIn) Incorporating these modifications, your code worked for me and reads something like: Sub myLookup() Dim vResult As Range Dim DateIn As Variant Dim ThisNum As Double DateIn = InputBox("Enter Target Date") If IsDate(DateIn) Then DateIn = DateValue(DateIn) Else Exit Sub End If MsgBox "Date = " & DateIn With Selection ' Sheet1.Range("Data") Set vResult = .Find(What:=DateIn, After:=.Cells(1, 1), _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext) End With If vResult Is Nothing Then MsgBox "not Found" Else ThisNum = vResult.Offset(0, 2).Value MsgBox ThisNum End If End Sub --- Regards, Norman "Roy Kirkland" wrote in message ... OK, but if I do this, how do I get the value from the 2nd cell to the right of the date? I tried adding a statement: Not IsEmpty(vResult) Then ThisNum If = vResult.Offset(0,1) But still end up with error 91 on the 'ThisNum' line., and the program stops there. Roy "Norman Jones" wrote in message ... Hi Roy, Try changing: SearchDirection:=xlNext).Offset(0, 2) to SearchDirection:=xlNext) --- Regards, Norman "Roy Kirkland" wrote in message ... I tried it Dave, but still got the same error. The line with the Set statement gives the error 91 (variable not set) when the program flow reaches it. I've tried several variations of this code and am at a dead end. Any links to various lookup routines? Thanks Roy "Dave Peterson" wrote in message ... vResult is actually a range (the cell where the date is found). You could drop the .countif() and just use .find. Option Explicit Sub myLookup() Dim vResult As Range Dim DateIn As Variant DateIn = InputBox("Enter Target Date") If IsDate(DateIn) Then DateIn = DateValue(DateIn) Else Exit Sub End If MsgBox "Date = " & DateIn With Sheet1.Range("Data") Set vResult = .Find(What:=CLng(DateIn), After:=.Cells(1, 1), _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext).Offset(0, 2) End With If vResult Is Nothing Then MsgBox "not Found" Else MsgBox vResult.Value End If End Sub And sometimes dates are easier to find if you search for the numeric representation (clng(datein)). And I changed the name of your sub. =Lookup() is a worksheet function and I don't like to use words that excel likes to use. Roy Kirkland wrote: I'm trying to use the Find method to look up values in tables according to the date. The first column holds the date, and columns b - j hold the numerical data. All columns and rows are in the named range "Data". Here's the code I've written - Sub Lookup() Dim vResult As Variant datein = InputBox("Enter Target Date") If datein 0 Then datein = DateValue(datein) End If MsgBox "Date = " & datein If WorksheetFunction.CountIf(Sheet1.Range("Data"), datein) 0 Then With Sheet1.Range("Data") vResult = .Find(What:=(datein), After:=.Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlNext).Offset(0, 2) End With MsgBox vResult End If End Sub The error tells is 91: Object or with variable not set - I have read that this may not have to do with the variables, but on mouseover, the variable vResult is empty. Any help would be appreciated. -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with lookups via VBA
Hi Norm -
I recorded the find macro, and with a little tweaking, it is working perfectly. The code you have is almost identical to what I've come up with - the differences being only that I know what I'm going to do with the results. Thanks for all your help. Roy "Norman Jones" wrote in message ... Hi Roy, Not IsEmpty(vResult) Then ThisNum If = vResult.Offset(0,1) Try instead: If not vResult is Nothing Then ThisNum = vResult.Offset(0,2).Value End If Incidentally, if the date cells in the named data range are formatted as dates, you should replace: Set vResult = .Find(What:=CLng(DateIn) with Set vResult = .Find(What:=(DateIn) Incorporating these modifications, your code worked for me and reads something like: Sub myLookup() Dim vResult As Range Dim DateIn As Variant Dim ThisNum As Double DateIn = InputBox("Enter Target Date") If IsDate(DateIn) Then DateIn = DateValue(DateIn) Else Exit Sub End If MsgBox "Date = " & DateIn With Selection ' Sheet1.Range("Data") Set vResult = .Find(What:=DateIn, After:=.Cells(1, 1), _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext) End With If vResult Is Nothing Then MsgBox "not Found" Else ThisNum = vResult.Offset(0, 2).Value MsgBox ThisNum End If End Sub --- Regards, Norman "Roy Kirkland" wrote in message ... OK, but if I do this, how do I get the value from the 2nd cell to the right of the date? I tried adding a statement: Not IsEmpty(vResult) Then ThisNum If = vResult.Offset(0,1) But still end up with error 91 on the 'ThisNum' line., and the program stops there. Roy "Norman Jones" wrote in message ... Hi Roy, Try changing: SearchDirection:=xlNext).Offset(0, 2) to SearchDirection:=xlNext) --- Regards, Norman "Roy Kirkland" wrote in message ... I tried it Dave, but still got the same error. The line with the Set statement gives the error 91 (variable not set) when the program flow reaches it. I've tried several variations of this code and am at a dead end. Any links to various lookup routines? Thanks Roy "Dave Peterson" wrote in message ... vResult is actually a range (the cell where the date is found). You could drop the .countif() and just use .find. Option Explicit Sub myLookup() Dim vResult As Range Dim DateIn As Variant DateIn = InputBox("Enter Target Date") If IsDate(DateIn) Then DateIn = DateValue(DateIn) Else Exit Sub End If MsgBox "Date = " & DateIn With Sheet1.Range("Data") Set vResult = .Find(What:=CLng(DateIn), After:=.Cells(1, 1), _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext).Offset(0, 2) End With If vResult Is Nothing Then MsgBox "not Found" Else MsgBox vResult.Value End If End Sub And sometimes dates are easier to find if you search for the numeric representation (clng(datein)). And I changed the name of your sub. =Lookup() is a worksheet function and I don't like to use words that excel likes to use. Roy Kirkland wrote: I'm trying to use the Find method to look up values in tables according to the date. The first column holds the date, and columns b - j hold the numerical data. All columns and rows are in the named range "Data". Here's the code I've written - Sub Lookup() Dim vResult As Variant datein = InputBox("Enter Target Date") If datein 0 Then datein = DateValue(datein) End If MsgBox "Date = " & datein If WorksheetFunction.CountIf(Sheet1.Range("Data"), datein) 0 Then With Sheet1.Range("Data") vResult = .Find(What:=(datein), After:=.Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlNext).Offset(0, 2) End With MsgBox vResult End If End Sub The error tells is 91: Object or with variable not set - I have read that this may not have to do with the variables, but on mouseover, the variable vResult is empty. Any help would be appreciated. -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with lookups via VBA
Thanks for the correction. (I missed the .offset stuff.)
Norman Jones wrote: Hi Roy, Not IsEmpty(vResult) Then ThisNum If = vResult.Offset(0,1) Try instead: If not vResult is Nothing Then ThisNum = vResult.Offset(0,2).Value End If Incidentally, if the date cells in the named data range are formatted as dates, you should replace: Set vResult = .Find(What:=CLng(DateIn) with Set vResult = .Find(What:=(DateIn) Incorporating these modifications, your code worked for me and reads something like: Sub myLookup() Dim vResult As Range Dim DateIn As Variant Dim ThisNum As Double DateIn = InputBox("Enter Target Date") If IsDate(DateIn) Then DateIn = DateValue(DateIn) Else Exit Sub End If MsgBox "Date = " & DateIn With Selection ' Sheet1.Range("Data") Set vResult = .Find(What:=DateIn, After:=.Cells(1, 1), _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext) End With If vResult Is Nothing Then MsgBox "not Found" Else ThisNum = vResult.Offset(0, 2).Value MsgBox ThisNum End If End Sub --- Regards, Norman "Roy Kirkland" wrote in message ... OK, but if I do this, how do I get the value from the 2nd cell to the right of the date? I tried adding a statement: Not IsEmpty(vResult) Then ThisNum If = vResult.Offset(0,1) But still end up with error 91 on the 'ThisNum' line., and the program stops there. Roy "Norman Jones" wrote in message ... Hi Roy, Try changing: SearchDirection:=xlNext).Offset(0, 2) to SearchDirection:=xlNext) --- Regards, Norman "Roy Kirkland" wrote in message ... I tried it Dave, but still got the same error. The line with the Set statement gives the error 91 (variable not set) when the program flow reaches it. I've tried several variations of this code and am at a dead end. Any links to various lookup routines? Thanks Roy "Dave Peterson" wrote in message ... vResult is actually a range (the cell where the date is found). You could drop the .countif() and just use .find. Option Explicit Sub myLookup() Dim vResult As Range Dim DateIn As Variant DateIn = InputBox("Enter Target Date") If IsDate(DateIn) Then DateIn = DateValue(DateIn) Else Exit Sub End If MsgBox "Date = " & DateIn With Sheet1.Range("Data") Set vResult = .Find(What:=CLng(DateIn), After:=.Cells(1, 1), _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext).Offset(0, 2) End With If vResult Is Nothing Then MsgBox "not Found" Else MsgBox vResult.Value End If End Sub And sometimes dates are easier to find if you search for the numeric representation (clng(datein)). And I changed the name of your sub. =Lookup() is a worksheet function and I don't like to use words that excel likes to use. Roy Kirkland wrote: I'm trying to use the Find method to look up values in tables according to the date. The first column holds the date, and columns b - j hold the numerical data. All columns and rows are in the named range "Data". Here's the code I've written - Sub Lookup() Dim vResult As Variant datein = InputBox("Enter Target Date") If datein 0 Then datein = DateValue(datein) End If MsgBox "Date = " & datein If WorksheetFunction.CountIf(Sheet1.Range("Data"), datein) 0 Then With Sheet1.Range("Data") vResult = .Find(What:=(datein), After:=.Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlNext).Offset(0, 2) End With MsgBox vResult End If End Sub The error tells is 91: Object or with variable not set - I have read that this may not have to do with the variables, but on mouseover, the variable vResult is empty. Any help would be appreciated. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookups | Excel Discussion (Misc queries) | |||
Problem understanding dependant lookups | Excel Worksheet Functions | |||
Lookups | Excel Worksheet Functions | |||
LOOKUPS - Creating LOOKUPs where two different values must BOTH be satisfied. | Excel Worksheet Functions | |||
Lookups.. | Excel Programming |