If cell begins with given text then return value

[[email protected]]

Hi Friends,

I am trying to write macro for the below condition
If column A begins with “A1 & A2” then Column “B” to be filed with Left(A1,5) and Column “C” to be updated as “Apple” Range is equal to ColumnA

If column A begins with “B1 & B2” then Column “B” to be filed with Left(A1,5) and Column “C” to be updated as “Banana” Range is equal to Column A

If column A begins with “O1 & O2” then Column “B” to be filed with Left(A1,5) and Column “C” to be updated as “Orange” Range is equal to Column A

If column A done not begins with any of the above then Column B & C should be blank

Kindly help to get VBA code

[Marc L]

Hi !

Where is your try ? Share your code !

[[email protected]]

[Marc L]

Let's start with

If column A begins with “A1 & A2” then Column “B” to be filed with Left(A1,5) and Column “C” to be updated as “Apple” Range is equal to ColumnA

         Dim Rg As Range
    For Each Rg In Range("A2", Cells(Rows.Count, 1).End(xlUp))
          If Rg.Value Like "A[12]*" Then
             Rg(1, 2).Resize(, 2).Value = Array(Left(Rg.Value, 5), "Apple")
'          elseif …
          
          End If
    Next  

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[Marc L]

As a beginner starter :

    Dim L, V, Rg As Range, W 
        L = [{"A","B","O"}] 
        V = [{"Apple","Banana","Orange"}] 
    For Each Rg In Range("A2", Cells(Rows.Count, 1).End(xlUp)) 
            W = Application.Match(Left(Rg.Value, 1), L, 0) 
        If IsNumeric(W) Then 
            If Rg.Value Like "*[12]*" Then Rg(1, 2).Resize(, 2).Value = Array(Left(Rg.Value, 5), V(W)) 
        End If 
    Next  

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[[email protected]]

Thanks!

One small clarification, if i want to update "V" value ("Apple","Banana","Orange") in "H" column(instead of Column "C") then where is should amend.

[Marc L]

Thanks for the rep' ! Mod for column H :

    Dim L, V, Rg As Range, W 
        L = [{"A","B","O"}] 
        V = [{"Apple","Banana","Orange"}] 
    For Each Rg In Range("A2", Cells(Rows.Count, 1).End(xlUp)) 
            W = Application.Match(Left(Rg.Value, 1), L, 0) 
        If IsNumeric(W) Then 
            If Rg.Value Like "*[12]*" Then 
               Rg(1, 2).Value = Left(Rg.Value, 5) 
               Rg(1, 8).Value = V(W) 
            End If 
        End If 
    Next  

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[[email protected]]

Thanks. It is working great. I want to add one more condition like If the column "H" has any blank cells then the above code is to apply on the on blank cells without affecting non blanks cells.

Is this possible?

[Marc L]

At beginner level so you should achieved it yourself !

    Dim L, V, Rg As Range, W 
        L = [{"A","B","O"}] 
        V = [{"Apple","Banana","Orange"}] 
    For Each Rg In Range("A2", Cells(Rows.Count, 1).End(xlUp)) 
          If Rg(1, 8).Value = "" Then 
                W = Application.Match(Left(Rg.Value, 1), L, 0) 
            If IsNumeric(W) Then 
                If Rg.Value Like "*[12]*" Then 
                   Rg(1, 2).Value = Left(Rg.Value, 5) 
                   Rg(1, 8).Value = V(W) 
                End If 
            End If 
          End If 
    Next  

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !