using if and evaluate in excel vba to convert during runtime cell data into text

Question

Using IF and EVALUATE in excel vba how to convert during runtime cell data of a column into text to filter data based on user's input? I have posted similar query here

Here is the posted issue:

The AutoFilter given below filters records with PinCodes "600 083" if varPin = "083". But this AutoFilter does not filter records with PinCodes "600083" if varPin = "083". Code:

Dim varPin As String
Dim PinWithWildCard As String
Dim rng As Range
Set rng = ActiveSheet.Range("D1:Q100") 'D has Names. Q has Email IDs.
varPin = "083"
PinWithWildCard = "*" & varPin & "*"
      rng.AutoFilter Field:=10, Criteria1:=PinWithWildCard  'field number is from the one set in rng and has PinCodes

I checked the format of the cells having these PinCodes and they are set in GENERAL and are aligned in Left as Charaters. I also tried PinWithWildCard = "=" & varPin & "" but it does not work. Kindly suggest as to how to make this filter numbers set as general with space and without space. Thanks.

L42 · Accepted Answer · 2014-01-21 02:24:51Z

0

if you're working with not so large data set, you can try this:

Sub Sample()

Dim myfilter() As String, varpin As String, PinWithWildCard as String
Dim rng As Range, cel As Range


Set rng = ActiveSheet.Range("$D$1:$Q$100")
ReDim myfilter(0 To 0)
varpin = "083"
PinWithWildCard = "*" & varpin & "*"

ActiveSheet.AutoFilterMode = False

For Each cel In rng
    If cel.Value Like PinWithWildCard Then
        myfilter(UBound(myfilter)) = cel.Value
        ReDim Preserve myfilter(0 To UBound(myfilter) + 1)
    End If
Next cel

rng.AutoFilter field:=10, Criteria1:=myfilter, Operator:=xlFilterValues

End Sub

Above filters all values with O83 in it.
Hope this works for you. :)

answered Jan 21, 2014 at 2:24

L42

19.8k11 gold badges47 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

using if and evaluate in excel vba to convert during runtime cell data into text

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related