ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Problem with lookups via VBA (https://www.excelbanter.com/excel-programming/317491-problem-lookups-via-vba.html)

Roy Kirkland
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.



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




Roy[_7_]
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!

Dave Peterson[_5_]
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

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



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

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

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

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

Dave Peterson[_5_]
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


All times are GMT +1. The time now is 12:02 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com