Search for a text in column B and past other text to column L if true

Question

I need to search in column B:B for a specific text, then if true paste other text to column L:L, exemple:

Sub teste()
    Application.ScreenUpdating = False

    last = Cells(Rows.Count, "B").End(xlUp).Row

    For i = last To 1 Step -1    
        If (Cells(i, "B").Value) = "string_1" Then    
            Range("L2").Select
            ActiveCell.FormulaR1C1 = "some_text_1"

            'LastRow = Range("A" & Rows.Count).End(xlUp).Row
            'Range("L2").AutoFill Destination:=Range("L2:L" & LastRow)
        End If
    Next i
End Sub

I can only paste the first text if true or fill the column L:L with the same text.

score 0 · Accepted Answer · answered Apr 29 '19 at 12:28

0

You mean something like that?

If column B is string_1 then copy column C to column L

For i = last To 1 Step -1    
    If (Cells(i, "B").Value) = "string_1" Then    

        'copy value from C to L
        Cells(i, "L").Value = Cells(i, "C").Value

    End If
Next i

You might benefit from reading How to avoid using Select in Excel VBA.

answered Apr 29 '19 at 12:28

Pᴇʜ

56,719
10
49
73

Thanks for the help! But the code didn't pass anything, in fact he image is an example, search on A and if true paste a text ( .Value = "some text" ) to C. I'm working with B to L columns. – b8engl Apr 29 '19 at 14:09
Well this is an example how it works in general. You still need to put in some effort and adjust the column names to whatever you need. This code checks if column B is `string_1` then copy column C to column L. If you need other columns change them to your needs. – Pᴇʜ Apr 29 '19 at 14:36
1

With your help, I have created a solution. Sorry, I didn't get it at first look. – b8engl Apr 29 '19 at 14:48

score 0 · Answer 2 · answered Apr 29 '19 at 14:50

Sub teste()
Application.ScreenUpdating = False

s1 = "first_text"
s2 = "second_text"
s3 = "third_text"

last = Cells(Rows.Count, "B").End(xlUp).Row

For i = last To 1 Step -1

If (Cells(i, "B").Value) = "string_1" Then
    Cells(i, "L").Value = s1
ElseIf (Cells(i, "B").Value) = "String_2" Then
    Cells(i, "L").Value = s2
ElseIf (Cells(i, "B").Value) = "string_3" Then
    Cells(i, "L").Value = s3
End If

Next i

End Sub

Search for a text in column B and past other text to column L if true

2 Answers2