B
BTU_needs_assistance_43
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."
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."