Excel VBA find values in another column if it matches

Question

Generally, my problem is I want to find values in Column B if it Exists in Column D and do concatenate the first character in Column D to Column B.

Here's my initial code:

Dim ix, ixLastrow as Long
ixLastrow = ShtData.Range("B" & Rows.Count).End(xlUp).Row

For ix = 2 to ixLastrow
If ShtData.Cells(ix, 2).value = ShtData.Cells(ix, 4) then
ShtData.Cells(ix, 3).Value = ShtData.Cells(ix,2) & Left(ShtData.Cells(ix, 4), 2)
End if
Next ix

The code works if the value is Align to the ROW and Exact same value, but what I want to do is the Find String value is in column B then search for Matches in Column D.

For Better understanding what I want to do here. See sample Data below.

Column B     |     Column D
AAA          |     IH (for AAF only)
AAB          |     ID (for AAD only)
AAC          |
AAD          |     IA (for AAA and AAB only)
AAE          |
AAF          |

As you could see, my goal would be something like this

Column C
AAA IA
AAB IA
AAC
AAD ID
AAE
AAF IH

As far as I know, my code can't give me this output. Im still new to Excel VBA, I would be really glad If someone would help me with this. Thanks :)

Variatus · Accepted Answer · 2017-12-23 02:57:48Z

1

The code below will do what you want.

Option Explicit

Sub AddMatch()
    ' 23 Dec 2017

    Dim Ws As Worksheet
    Dim Rng As Range                        ' the range to search in
    Dim Fnd As Range
    Dim Rl As Long                          ' last used row
    Dim R As Long

    Set Ws = ActiveSheet
    Application.ScreenUpdating = False
    With Ws
        ' determine last row in column B
        Rl = .Cells(.Rows.Count, "B").End(xlUp).Row
        ' set the search range in column D, starting in row 2
        Set Rng = Range(.Cells(2, 4), .Cells(Rl, 4))
        For R = 2 To Rl                     ' start in row 2
            If XlFind(Fnd, Rng, .Cells(R, 2).Value, LookAt:=xlPart) Then
                .Cells(R, "C").Value = .Cells(R, "B").Value & " " & Left(Fnd.Value, 2)
            End If
        Next R
    End With
    Application.ScreenUpdating = True
End Sub

Function XlFind(Fnd As Range, _
                Where As Range, _
                ByVal What As Variant, _
                Optional ByVal LookIn As Variant = xlValues, _
                Optional ByVal LookAt As Long = xlWhole, _
                Optional ByVal SearchBy As Long = xlByColumns, _
                Optional ByVal StartAfter As Long, _
                Optional ByVal Direction As Long = xlNext, _
                Optional ByVal MatchCase As Boolean = False, _
                Optional ByVal MatchByte As Boolean = False, _
                Optional ByVal MatchPosition As Long, _
                Optional ByVal After As Range, _
                Optional ByVal FindFormat As Boolean = False) As Boolean
    ' 09 Dec 2017
    ' Fnd is a return range
    ' Settings LookIn, LookAt, SearchOrder, and MatchByte
    ' are saved each time the Find method is used

    Dim Search As Range
    Dim FirstFnd As Range

    Set Search = Where
    With Search
        If After Is Nothing Then
            If StartAfter Then
                StartAfter = WorksheetFunction.Min(StartAfter, .Cells.Count)
            Else
                StartAfter = .Cells.Count
            End If
            Set After = .Cells(StartAfter)
        End If

        If MatchPosition > 1 Then LookAt = xlPart
        Set Fnd = .Find(What:=What, After:=After, _
                        LookIn:=LookIn, LookAt:=LookAt, _
                        SearchOrder:=SearchBy, SearchDirection:=Direction, _
                        MatchCase:=MatchCase, MatchByte:=MatchByte, _
                        SearchFormat:=FindFormat)
        If Not Fnd Is Nothing Then
            Set FirstFnd = Fnd
            Do
                If MatchPosition Then
                    If InStr(1, Fnd.Value, What, vbTextCompare) = MatchPosition Then
                        Exit Do
                    Else
                        Set Fnd = .FindNext(Fnd)
                    End If
                Else
                    Exit Do
                End If
            Loop While Not (Fnd Is Nothing) And Not (Fnd Is FirstFnd)
        End If
    End With

    XlFind = Not (Fnd Is Nothing)
End Function

The function xlFind is a bit of an overkill here, but it is one which I could take from the shelf, and its extra capabilities may come in handy one day.

answered Dec 23, 2017 at 2:57

Variatus

14.4k2 gold badges16 silver badges35 bronze badges

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

4 Comments

CJanon Over a year ago

This also works. But It also affects all data with similarities, if column b contains similar texts, example AA

Variatus Over a year ago

No I didn't use the Like comparator. Therefore it will only find proper matches.

CJanon Over a year ago

Yes, I think I messed up the code a little but its now ok, thanks for this great help :)

CJanon Over a year ago

Hi, do you think this code could do find value like in an array, not just a first find value. I am thinking about [this] (stackoverflow.com/questions/48202386/…)

Sercho · Accepted Answer · 2017-12-23 03:12:23Z

1

I think what you is after is the Like operator.

In the line where you check if the two values at Lee the same, replace with

If Range2.Value Like “*” & Range1.Value & “*” then

In order to check all rows, imbed another For loop, like:

For IX = 1 to IXLastrow
    For IY = 1 to IXLastRow
        If Range(“D” & IY).Value Like “*” & Range(“B” & IX).Value & “*” Then 
            Range(“C” & IX).Value = Range(“B” & IX).Value & Left(Range(“D” & IY).Value,2)
            Exit For
        End If
    Next IY
Next IX

edited Dec 23, 2017 at 3:12

answered Dec 23, 2017 at 2:20

Sercho

3052 silver badges10 bronze badges

9 Comments

CJanon Over a year ago

Thanks for response. I tried the like operator but as you can see my For Loop would analyze data per row so if they are align and the data is in another row I wouldn't be able to get them.

Sercho Over a year ago

Ok, so the above code should do what you want then...you require two For loops. Just be aware that you will likely have to replace all my quotation marks with fresh ones, as I have had some issues with the iPhone keyboard not working when others copy/paste my answers directly.

CJanon Over a year ago

I tried applying your code, I am getting 'Method 'Range' of object '_Global' failed'. It seems theres a problem with Range selection

CJanon Over a year ago

Yes, I did change them.

Sercho Over a year ago

I think I’ve spotted it. I missed the I in front of the X in the terminal line of the If then statements.

|

GMalc · Accepted Answer · 2017-12-24 07:05:09Z

0

Based on your example, you don't need multiple loops, this is a little bit easier to understand.

Sub Concte()
Dim lRow As Long
lRow = ShtData.Range("B" & Rows.Count).End(xlUp).Row

    For x = 1 To lRow
        If Cells(x, 4).Value <> "" Then
        Cells(x, 3).Value = Cells(x, 2).Value & " " & Cells(x, 4).Value
        End If
    Next x
End Sub

answered Dec 24, 2017 at 7:05

GMalc

2,6381 gold badge11 silver badges16 bronze badges

1 Comment

Sercho Over a year ago

The problem is that the question doesn’t outline the exact problem...The user revealed multiple further requirements in comments and the answer I provided has been edited to account for these. If you look at my original answer before all the edits, you will see what I mean.

Collectives™ on Stack Overflow

Excel VBA find values in another column if it matches

3 Answers 3

4 Comments

9 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

9 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related