Autofiltering macro - filters text but not the numbers

[skaban]

Hello everyone,

Can any of the pros please provide an advice on the macro below? :)

I've combined a macro that takes data from the clipboard and applies it to the active column. It works great on any column containing text however it I copy a number and run this macro on the column with numbers it doesn't filter the strings containing the number I have in my clipboard (e. g. - clipboard has 1 and I apply macro to a column with values 101, 120, 150 and none of the values are displayed after filter).
I'm guessing the issue is with a variable being treated as text but is there a way to make it filter no matter what data type is in a column?
My macro text is below:


Sub FilterBasedOnClipboardValue()
'
' Macro
'
Dim MyData As DataObject
Dim strClip As String

Set MyData = New DataObject
MyData.GetFromClipboard
strClip = MyData.GetText

CurColumn = ActiveCell.Column

'
If Not ActiveSheet.AutoFilterMode Then ' You can remove If Not ActiveSheet.AutoFilterMode Then è End If lines to have filter reapply each time. Otherwise each time you execute this macro filter will be applied to current filtered data.
Cells.Select
Selection.AutoFilter
End If

ActiveSheet.Cells.AutoFilter Field:=CurColumn, Criteria1:="=*" & strClip & "*"

End Sub

[mancubus]

hi.

wellcome to VBAX.

this is a good tutorial for the use of clipboard in VBA.

http://www.cpearson.com/excel/Clipboard.aspx

[skaban]

Thanks for the link, very good tutorial indeed!

However it doesn't help me with my issue.
The value in the clipboard is always treated as text by my macro. Which is fine until I copy a numeric value to the clipboard and apply it to the column with numbers - in this case values contain that number are simply no filtered and filtering works as if there were no matches.
Is there a way to make VBA to assign the value from clipboard the same format as the data I'm filtering has?

[skaban]

Ok, found out that this can be done via IsNumeric check.