CODEWITHSUNDEEP
vbaexcel
Nant
Nant

Reputation: 569

Copy range from column A and write to column B

I have a range in column A and I want to write for each element of range A something in Cell B. I am using this code but it doesn't seem to work:

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    Set rng2 = Range("A:A" & LastRow).Select

    For Each Cell In rng2
        Cell.Offset(1, 0).FormulaR1C1 = "=isOK"
    Next Cell

What I want to write in column B. The last cell in A is 1887:

enter image description here

Can anyone assist please?

Upvotes: 1

Views: 80

Answers (4)

Synoon
Synoon

Reputation: 2351

Try that better with normal excel formulas:

=if(lookup(A1;C1:Z20)=0;"isOK";"notOK")

copy that into every cell in column B and you will get your expected result ;)

Upvotes: 1

mauek unak
mauek unak

Reputation: 772

I corrected your code, give it a try :

Dim Cell as Range, LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set rng2 = Range("A2:A" & LastRow)

For Each Cell In rng2
    If Cell.Value <> vbNullString Then Cell.Offset(0, 1) = "isOK"
Next Cell

Upvotes: 1

A.S.H
A.S.H

Reputation: 29332

Avoid using Select as much as you can, it is a source of trouble. In this code, you dont need to use it. I suppose that isOK is a user-defined function (UDF):

    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    Set rng2 = Range("A2:A" & LastRow)
    For Each Cell In rng2
        If not IsEmtpy(Cell) Then Cell.Offset(1, 0).Formula = "=isOK()"
    Next Cell

Upvotes: 1

Nathan_Sav
Nathan_Sav

Reputation: 8531

Set rng2 = Range("A:A" & LastRow).Select

this should be Set rng2 = Range("A1:A" & LastRow)

and your offset is the cell underneath, should be (0,1)

Upvotes: 1

Related Questions