Comparing sql record to cell value

**mozart** · 06-07-2005, 07:36 AM

I am assigning a value to a variable from an oracle query using 0040 and a dynaset.

Eg For i = 1 To OraDynaSet.RecordCount
dbValue = OraDynaSet.Fields(0).Value
............................ bla bla

I am then trying to compare this variable with some value in a range and am getting in all sorts of a mess with Exit For, End If

What I am trying to do is getting first record and compare to every value in my range eg Range("A1:A3"). If it find a value then good and we move onto next record. If it does not find a matching value then up pops a msgbox and procedure stops. If all records from query are matched in the range then naturally it also stops or does something else without any problems

Thanks

**mangesh_yadav** · 06-07-2005, 07:57 AM

if not (WorksheetFunction.Match(dbValue,range("A1:A3"),0) > 0) then
msgbox "your message here"
exit sub
end if

Mangesh

**mangesh_yadav** · 06-07-2005, 08:04 AM

Sorry, when not found, the match gives an error. You may use something like this instead:

Sub Test()

On Error GoTo Err_handler
a = WorksheetFunction.Match(2, Range("A1:A3"), 0)

' your code here

Err_handler:
Exit Sub

End Sub

Mangesh

**mangesh_yadav** · 06-07-2005, 08:19 AM

A better approach:

Sub test()

Set a = Range("a1:a3").Find(dbValue, LookIn:=xlValues)

If a Is Nothing Then
MsgBox "not found"
Exit Sub
End If

End Sub

Mangesh

Comparing sql record to cell value

LinkBack

Thread Tools

Rate This Thread

Display

Comparing sql record to cell value

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions