PC Review


Reply
Thread Tools Rate Thread

Code that works in Excel but not Access

 
 
BTU_needs_assistance_43
Guest
Posts: n/a
 
      23rd Jul 2009
I can use this code to find certain values in Excel but it won't carry over
to Access. Can I tweak this code to make it work in Access or am I at a dead
end with this code?

I posted this in a module:

Option Compare Database

Function Find_Range(Find_Item As Variant, _
Search_Range As Range, _
Optional LookIn As Variant, _
Optional LookAt As Variant, _
Optional MatchCase As Boolean) As Range

Dim c As Range
If IsMissing(LookIn) Then LookIn = xlValues 'xlFormulas
If IsMissing(LookAt) Then LookAt = xlPart 'xlWhole
If IsMissing(MatchCase) Then MatchCase = False

With Search_Range
Set c = .Find( _
What:=Find_Item, _
LookIn:=LookIn, _
LookAt:=LookAt, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=MatchCase, _
SearchFormat:=False)
If Not c Is Nothing Then
Set Find_Range = c
firstAddress = c.Address
Do
Set Find_Range = Union(Find_Range, c)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

End Function
-------------------------------------------------------------
Then this as part of a command for a button

Find_Range("Shot Date", Cells, xlFormulas, xlWhole).Cells.Select
Set ShotName = ActiveCell.Offset(2, -1)

Set xlc = xls.Range("ShotName")
..
..
..
and then wrote the values to a table.

When I try to run this code i get an error message that says "Compile error:
User-defined type not defined" and it then highlights the first 5 lines of my
module code.
If I put the code in its own section at the bottom of the vba sheet as its
own section I get another error message that says "The expression On Click
you entered as the event property setting produced the following error:
User-defined type not defined."
 
Reply With Quote
 
 
 
 
ryguy7272
Guest
Posts: n/a
 
      23rd Jul 2009
In Excel this code is dealing with ranges, unions, and cell addresses.
AFAIK, none of these are available in Access. Excel and Access are two
completely different applications. If the code works fine in Excel, what do
you need to transport it to Access for; run it in Excel.

You will have to learn VBA if you want to do anything meaningful with code.

Take a look at this
http://www.amazon.com/Excel-2003-Pow...8375530&sr=8-6

Or this, if you use 2007
http://www.amazon.com/Excel-Power-Pr...8375530&sr=8-2


And For Access
http://www.amazon.com/Access-2003-Po...8375658&sr=8-7

And for Access 2007
http://www.amazon.com/Access-2007-Pr...8375658&sr=8-2

Good luck,
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"BTU_needs_assistance_43" wrote:

> I can use this code to find certain values in Excel but it won't carry over
> to Access. Can I tweak this code to make it work in Access or am I at a dead
> end with this code?
>
> I posted this in a module:
>
> Option Compare Database
>
> Function Find_Range(Find_Item As Variant, _
> Search_Range As Range, _
> Optional LookIn As Variant, _
> Optional LookAt As Variant, _
> Optional MatchCase As Boolean) As Range
>
> Dim c As Range
> If IsMissing(LookIn) Then LookIn = xlValues 'xlFormulas
> If IsMissing(LookAt) Then LookAt = xlPart 'xlWhole
> If IsMissing(MatchCase) Then MatchCase = False
>
> With Search_Range
> Set c = .Find( _
> What:=Find_Item, _
> LookIn:=LookIn, _
> LookAt:=LookAt, _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlNext, _
> MatchCase:=MatchCase, _
> SearchFormat:=False)
> If Not c Is Nothing Then
> Set Find_Range = c
> firstAddress = c.Address
> Do
> Set Find_Range = Union(Find_Range, c)
> Set c = .FindNext(c)
> Loop While Not c Is Nothing And c.Address <> firstAddress
> End If
> End With
>
> End Function
> -------------------------------------------------------------
> Then this as part of a command for a button
>
> Find_Range("Shot Date", Cells, xlFormulas, xlWhole).Cells.Select
> Set ShotName = ActiveCell.Offset(2, -1)
>
> Set xlc = xls.Range("ShotName")
> .
> .
> .
> and then wrote the values to a table.
>
> When I try to run this code i get an error message that says "Compile error:
> User-defined type not defined" and it then highlights the first 5 lines of my
> module code.
> If I put the code in its own section at the bottom of the vba sheet as its
> own section I get another error message that says "The expression On Click
> you entered as the event property setting produced the following error:
> User-defined type not defined."

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL code works in Access SQL window but not in VBA SQL code EagleOne@discussions.microsoft.com Microsoft Access 61 5th Sep 2008 07:53 AM
Re: Code works with Excel 2000 but not with Excel 2002 Tom Ogilvy Microsoft Excel Programming 0 17th Sep 2004 09:21 PM
Code works with Excel 2000 but not with Excel 2002 =?Utf-8?B?TGEgRHVyYW5kZQ==?= Microsoft Excel Programming 0 17th Sep 2004 08:49 PM
Code works in Excel but not in Access Mike Collard Microsoft Access VBA Modules 4 3rd Sep 2004 01:46 PM
code works in excel 2002, but not in excel 2003 pyc Microsoft Excel Programming 2 2nd Sep 2004 01:16 PM


Features
 

Advertising
 

Newsgroups
 


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