CODEWITHSUNDEEP
vbaexcel
Gerle Batde
Gerle Batde

Reputation: 193

How to see if value already exists there by row by row?

Before the Macro :

Column 1| Column 7

PHRT1S | TEST_TEST_TEST_TEST

After the MacrO:

Column 1 | Column 7

PHRT1S | TEST_TEST_TEST_TEST(PHRT1S)

However, what if even though ID has not been appended but there are just random () exists, the macro will skip cause of the logic of if there is () in there.

Ideally, I'd like to be ready for this scenario where:

Before the macro

Column 1 | Column 7

PHRT1S | TEST_TEST_TEST(TEST)

After the Macro

Column 1 | Column 7

PHRT1S | TEST_TEST_TEST(TEST)(PHRT1S)

Skip to append column 1's values to column 7, if column 1 value already exists there rathen than only identifying that by single paranthesis.

My Macro is below: 

With xlSheet


    LastRow = xlSheet.UsedRange.Rows.Count
    Set target = .Range(.Cells(15, 9), .Cells(LastRow, 9))
    values = target.Value

    Set ptype=.Range(.Cells(15,6),.Cells(LastRow,6))
    pvalues=ptype.Value

    For i = LBound(values, 1) To UBound(values, 1)
    'if Statement for test keywords
    If InStr(1,pvalues(i,1),"Package")= 0 AND InStr(1,pvalues(i,1),"Roadblock")= 0 Then 
    If InStr(1,values(i,1),"(")=0 Then
    values(i, 1) = values(i, 1) & "(" & .Cells(i + 15 - LBound(values, 1), 3) & ")"
    End If 
    End If
    Next
    target.Value = values
End With

Upvotes: 2

Views: 143

Answers (1)

Variatus
Variatus

Reputation: 14383

Wouldn't this do the job for you? If Instr(values(I,1),.Cells(i + 15 - LBound(values, 1), 3)) = 0 Then, meaning instead of looking for a bracket make sure that the type hasn't been added yet.

Upvotes: 1

Related Questions