How to search a column and return the value in the next column with specific value

Question

I have an Excel worksheet that contains columns and multiple sheets. I want to copy column A from Sheet1 to Sheet2. Then once I've copied column A, I want column B from Sheet2 to have a specific value (in this case, "D") adjacent to each filled cell in A. I am only stuck with the code below.

Example:

Sheet1 contains:

Column A      B      C
       Arthur 1      34
       Bertha 2      35

Sheet2 must have:

Column A      B
       Arthur "D"
       Bertha "D"

Stuck with code:

Worksheets("Sheet1").Columns("A").Copy ActiveCell.Columns("A:A").Columns("A")

FYI: the problem with you code lies in "ActiveCell". As a good programming practice, try to avoid use of ActiveCell, ActiveSheet in macros. They can be a headache in big macros. If you must, just Activate the concerned cell before using those objects. — tumchaaditya

Siddharth Rout Siddharth Rout · Accepted Answer · 2012-06-09T04:00:57

Is this what you are trying?

Option Explicit

Sub Sample()
    Dim wsI As Worksheet, wsO As Worksheet
    Dim lRow As Long

    Set wsI = Sheets("Sheet1")
    Set wsO = Sheets("Sheet2")

    wsI.Columns(1).Copy wsO.Columns(1)

    lRow = wsO.Range("A" & wsO.Rows.Count).End(xlUp).Row

    wsO.Range("B1:B" & lRow).Value = "D"
End Sub

How to search a column and return the value in the next column with specific value

1 Answers