VBA to change cell value based on multiple conditions

[tonyc527]

Hey,

I'm looking for some help with this

I would like to set the value of any cell in column D to "Yellow789" where that rows column A value begins with (but may contain much more than) "red123", column B value contains (but may not begin or end with) "blue456", and Column C value is blank.

Cheers!

 

[Joe4]

Welcome to the Board!

You don't need VBA to do this, you could just use the formula (in cell D1 and copy down):

=IF(AND(LEFT(A1,6)="red123",LEFT(B1,7)="blue456",C1=""),"Yellow789","")

 

[Joe4]

If you really want a VBA solution to it, this will do that:

Sub MyPopulateMacro()

    Dim myLastRow As Long
    Dim myRow As Long
    
'   Find last row with data in column A
    myLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through data
    For myRow = 1 To myLastRow
        If (Left(Cells(myRow, "A"), 6) = "red123") _
                 And (Left(Cells(myRow, "B"), 7) = "blue456") _
                 And (Cells(myRow, "C") = "") Then
            Cells(myRow, "D") = "Yellow789"
        End If
    Next myRow
    
End Sub

 

[skywriter]

You can test this out.
It goes in the sheet module which means you have to right click on the sheet tab and choose view code, then paste the code into the white window.
Make sure you have macros enabled.
This could probably be done better, maybe a regular expression, but it's the best I can do.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Columns("A:C")) Is Nothing Then Exit Sub
Application.EnableEvents = False
    If Len(Cells(Target.Row, 2)) < 7 Then
        Cells(Target.Row, 4).Value = Empty
        Application.EnableEvents = True
        Exit Sub
    End If
    If Cells(Target.Row, 1).Value Like "red123*" And _
        Not "blue456" Like Right(Cells(Target.Row, 2).Value, 7) And _
        Not "blue456" Like Left(Cells(Target.Row, 2).Value, 7) And _
        Cells(Target.Row, 2).Value Like "*?blue456?*" And _
        Cells(Target.Row, 3).Value = Empty Then
        Cells(Target.Row, 4).Value = "Yellow789"
    Else
        Cells(Target.Row, 4).Value = Empty
    End If
Application.EnableEvents = True
End Sub

 

[tonyc527]

Thanks Joe. The challenge with just using a formula would be that users would paste data into a sheet where column d may contain values, but it will not contain values when the original conditions are met. I would like those records to be categorized in some way.

Will your and(left(cells function grab any record that contains the value specified or is that just going to look for a value that begins with the value specified? If the latter, could you help me with finding the record where the b column contains "blue456"?

Thanks again

 

[Joe4]

Try this variation, in which we can use the InStr function instead of Left on column B (see: MS Excel: How to use the INSTR Function (VBA)

Sub MyPopulateMacro()

    Dim myLastRow As Long
    Dim myRow As Long
    
'   Find last row with data in column A
    myLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through data
    For myRow = 1 To myLastRow
        If (Left(Cells(myRow, "A"), 6) = "red123") _
                And (InStr(Cells(myRow, "B"), "blue456") > 0) _
                And (Cells(myRow, "C") = "") Then
            Cells(myRow, "D") = "Yellow789"
        End If
    Next myRow
    
End Sub

 

[tonyc527]

Try this variation

Perfect. You're a wizard. Thanks!

 

[skywriter]

So what happened with this?

column A value begins with (but may contain much more than) "red123"

column B value contains (but may not begin or end with)"blue456"

When I type "magicblue456" into column B, then my code rejects is because it ends in "blue456", but Joe's code accepts it, so you didn't want that criteria after all?

 

[Joe4]

When I type "magicblue456" into column B, then my code rejects is because it ends in "blue456", but Joe's code accepts it, so you didn't want that criteria after all?

Hmm... good point. I missed that part originally (trying to do too many things at once here).
You could modify my IF clause like this:

        If (Left(Cells(myRow, "A"), 6) = "red123") _
                And (InStr(Cells(myRow, "B"), "blue456") > 0) _
[COLOR=#ff0000]                And (Right(Cells(myRow, "B"), 7) <> "blue456") _[/COLOR]
                And (Cells(myRow, "C") = "") Then