CODEWITHSUNDEEP
excelvba
Geographos
Geographos

Reputation: 1496

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

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.

enter image description here

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?

Upvotes: 0

Views: 1546

Answers (2)

braX
braX

Reputation: 11755

You can make the If statement case insensitive this way:

Sub Flag()
  Dim lRow As Long
  Dim lLastRow As Long

  lLastRow = Range("U" & Rows.Count).End(xlUp).Row

  For lRow = 2 To lLastRow 
    If UCase$(Range("U" & lRow).Value) = "MISSING POLE" Then
      
      ' do what you want here

    End If
  Next
End Sub

Upvotes: 1

Sumit Singh
Sumit Singh

Reputation: 38

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

Upvotes: 1

Related Questions