CODEWITHSUNDEEP
excelvba
SuperLoz
SuperLoz

Reputation: 27

Search String and If found then paste value - then loop

I'm trying to build a macro that searches a column for two Strings ("Tip Fee" or "Non-Deal". If it finds this then it pastes a "Y" value in another column. If it doesn't then it pastes "N".

I'm struggling to get it to work and not sure what to do for the "not equal to then "N") part.

Example for just finding "Tip Fee" below:

Sheets("Pipeline simplified").Select

Dim TipFee As String
Dim NonDeal As String
Dim t As Integer
Dim LastRowtip As Long
TipFee = "Tip Fee"
NonDeal = "Non-Deal"
LastRowtip = Cells(Rows.Count, "H").End(xlUp).Row


For t = 7 To LastRowtip

    If Cells(t, 8).Value = TipFee Then
    Cells(t, 30).Value = "Y"
      End If
Next t

Upvotes: 2

Views: 291

Answers (2)

tigeravatar
tigeravatar

Reputation: 26660

Can still use a formula in VBA, that way there's no need to loop. Formula can be made to search for text within the cell, and can also be case insensitive. Then just convert to values afterwards.

Dim ws As Worksheet

Set ws = ActiveWorkbook.Worksheets("Pipeline simplified")

With ws.Range("AD7:AD" & ws.Cells(ws.Rows.Count, "H").End(xlUp).Row)
    .Formula = "=IF(OR(ISNUMBER(SEARCH({""Tip Fee"",""Non-Deal""},H7))),""Y"",""N"")"
    .Value = .Value
End With

Upvotes: 3

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11998

Try this:

If Cells(t, 8).Value = TipFee Or Cells(t, 8).Value = NonDeal Then
    Cells(t, 30).Value = "Y"
Else
    Cells(t, 30).Value = "N"
End If

Also, check how IF sentence works:

If...Then...Else statement

Upvotes: 2

Related Questions