CODEWITHSUNDEEP
vbaexcelfind
Wizhi
Wizhi

Reputation: 6549

VBA Find value in row using wildcard

I have tried to make a loop (I'm very new to the VBA) that check for the word "Apple" in column A. Column A contains fruit names. When "Apple" is found, then the code is copying range "H2:S2" to Column G and the corresponding row value in Column A.

But the code will not run when the search criteria part is changed to wildcard (My goal is to include rows where the word Applecake exist).

lookupVal = "Apple" 'Works
lookupVal = "*Apple*" 'Nothing happens

How should I adjust the code below so it runs with wildcard or maybe there are a better solution than this code states to achieve the result?

Full code:

Sub CopypasteValues()
    Dim i, j, lastrowA As Long
    Dim lookupVal As String

    'finds the last row in Column A
    lastrowA = Sheets("Dataset").Cells(Rows.Count, "A").End(xlUp).Row

    'loop over values in Sheet "Dataset"
    For i = 1 To lastrowA 
        lookupVal = "Apple" 'Define search critera

        For j = 1 To lastrowA
            currVal = Sheets("Dataset").Cells(j, "A")
            If lookupVal = currVal Then
                ValueCopy = Range("G2:S2").Copy 'Range to copy
                Sheets("Dataset").Cells(j, "G") = Range("G" & j).PasteSpecial 
            End If
        Next j
    Next i
End Sub

Upvotes: 1

Views: 1745

Answers (1)

YowE3K
YowE3K

Reputation: 23994

You can use the Like operator:

If currVal Like lookupVal Then

So, if lookupVal was "*Apple*" and currVal was "Aren't Apples nice to eat", the test would be True.

You might also need to use

If LCase$(currVal) Like LCase$(lookupVal) Then

if you want to avoid case-sensitivity problems.

You could also consider using Find (with a LookAt:=xlPart or LookAt:=xlWhole parameter as required), rather than doing a cell-by-cell comparison. But it would depend on exactly what your requirements are as to whether that was a feasible solution.

Upvotes: 4

Related Questions