skaban
10-29-2011, 10:18 AM
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
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