VBA Excel If some value occur in the column, copy it to another column in the same row

Question

I am struggling with the following situation.

I have a set of bulk data in column U, from which I must filter the "missing pole" value. Next, I must copy this "missing pole" value to column BN, exactly to the same row, where it occurs in column U, as you can see below.

I tried:

Sub Flag()
  Dim lRow As Long
  If Range("U2:U" & lRow).Value = "Missing pole" Then
  Range("U2:U" & lRow).Copy
  Range("BN2:BN" & lRow).PasteSpecial xlPasteValues
  End If
End Sub

but I am getting error:

Method 'Range' of object'_Global' failed.

Debugger shows:

  If Range("U2:U" & lRow).Value = "Missing pole" Then

Other threads are here:

Copy Cells in One Column, Based on Criteria in Another Column, to Another Sheet

VBA - IF a cell in column A = Value, then copy column B, C, D of same row to new worksheet

but without a reasonable solution for me.

How can I copy the certain value occurring in the column (throughout a whole range) to another column placing it exactly in the same row?

question from:https://stackoverflow.com/questions/65951829/vba-excel-if-some-value-occur-in-the-column-copy-it-to-another-column-in-the-sa

Answer 1

Here is the VBA code that would work fine.
The Error is because you are trying to value of Range object you just need a For loop to traverse all the rows and then check if there is any value with "Missing Pole"

Here is the code:

Sub Flag()
  Dim LastRow As Long
  With ActiveSheet
        LastRow = .Cells(.Rows.Count, "U").End(xlUp).Row
  End With
  For i = 2 To LastRow
    If ActiveSheet.Range("U" & i) = "Missing pole" Then
        ActiveSheet.Range("BN" & i).Value2 = ActiveSheet.Range("U" & i).Value2
    End If
  Next i
End Sub