Error 1004 on Range.Text with AutoFilter
 

I have a spreadsheet like this

A B C
----------------------------------------------------
2 | 1 123.4586 =my_func(B2)
3 | 2 2123.53132 =my_func(B3)
4 | 1 78231.74853 =my_func(B4)



Public Function my_func(tar As Range) As String
On Error GoTo handler

my_func = tar.Text

Exit Function
handler:
Debug.Print Err.Number
End Function

Sub test()
With Range("$A$2:$A$4")
.AutoFilter
.AutoFilter Field:=1, Criteria1:="1"
End With
End Sub


Every time I run test() error code 1004 - Unable to get the Text
property of the Range class occurs. This error does not occur if I do
the autofilter manually.
Is there any workaround? Thanks.

Error 1004 on Range.Text with AutoFilter
 

Your code should include a 'top' cell for the filter arrow and should be all one line...

Range("$A$1:$A$4").AutoFilter Field:=1, Criteria1:="1"
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Permutations Add-in: with option to highlight valid words)



"Edmond Lai"
wrote in message
...
I have a spreadsheet like this

A B C
----------------------------------------------------
2 | 1 123.4586 =my_func(B2)
3 | 2 2123.53132 =my_func(B3)
4 | 1 78231.74853 =my_func(B4)



Public Function my_func(tar As Range) As String
On Error GoTo handler

my_func = tar.Text

Exit Function
handler:
Debug.Print Err.Number
End Function

Sub test()
With Range("$A$2:$A$4")
.AutoFilter
.AutoFilter Field:=1, Criteria1:="1"
End With
End Sub


Every time I run test() error code 1004 - Unable to get the Text
property of the Range class occurs. This error does not occur if I do
the autofilter manually.
Is there any workaround? Thanks.

Error 1004 on Range.Text with AutoFilter
 

Hi Jim,

I tried your code but got the same result.


On Jul 17, 7:25*pm, "Jim Cone" wrote:
Your code should include a 'top' cell for the filter arrow and should be all one line...

* Range("$A$1:$A$4").AutoFilter Field:=1, Criteria1:="1"
--
Jim Cone
Portland, Oregon USAhttp://www.mediafire.com/PrimitiveSoftware
(Permutations Add-in: *with option to highlight valid words)

"Edmond Lai"
wrote in ...







I have a spreadsheet like this

* * A B C
----------------------------------------------------
2 | *1 123.4586 =my_func(B2)
3 | *2 2123.53132 =my_func(B3)
4 | *1 78231.74853 =my_func(B4)

Public Function my_func(tar As Range) As String
* *On Error GoTo handler

* *my_func = tar.Text

* *Exit Function
handler:
* *Debug.Print Err.Number
End Function

Sub test()
* *With Range("$A$2:$A$4")
* * * *.AutoFilter
* * * *.AutoFilter Field:=1, Criteria1:="1"
* *End With
End Sub

Every time I run test() error code 1004 - Unable to get the Text
property of the Range class occurs. This error does not occur if I do
the autofilter manually.
Is there any workaround? *Thanks.

Error 1004 on Range.Text with AutoFilter
 

Jim's code works for me as follows...

Sub FilterRange()
Range("$A$1:$A$4").AutoFilter Field:=1, Criteria1:="1"
Application.Calculate '**rebuild formulas**
End Sub

**Required to mitigate AutoFilter causing '#VALUE!' error (#1004)**

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc