Error 1004 on Range.Text with AutoFilter

[Edmond Lai]

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.

 

[Jim Cone]

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" <[email protected]>
wrote in message
news:6464ef03-b32c-4a56-a693-5bf146de11ed@f39g2000prb.googlegroups.com...

 

[Edmond Lai]

Hi Jim,

I tried your code but got the same result.

 

[GS]

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)**