CODEWITHSUNDEEP
vbaexcel
ksmit144
ksmit144

Reputation: 87

How to use For Loop to Replace Offset Cell

So I wrote a For Loop code that tries to search through a particular column (Column M) of data for cells containing a description starting with "GE90" and replaces an adjecent offset cell (Column C) with "GE90 Hold".

I thought I was using the code correctly but for some reason it does not seem to work.

Dim Cell
For Each Cell In Range("M2:M" & LastRow)
    If Cell.Value = "GE90*" Then
        Cell.Offset(, -10).Value = "GE90 Hold"
    End If

Next Cell

Upvotes: 3

Views: 784

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149297

Cell.Offset(, -10).Value if you want to write to Col C is correct but why not a simplistic approach?

Range("C" & cell.row).Value = "GE90 Hold"

or 

Cells(cell.row, 3).value '<~~ As enderland suggested in the comment

The problem is with your If condition. Change it to

If Cell.Value Like "GE90*" Then

Upvotes: 4

neelsg
neelsg

Reputation: 4842

Your issue is actually that you assume the asterisk in "GE90*" is a wildcard, but actually, your code is looking for the literal value "GE90*". Change your code as follows:

Dim Cell
For Each Cell In Range("M2:M" & lastrow)
    If Left(Cell.Value, 4) = "GE90" Then
        Cell.Offset(, -10).Value = "GE90 Hold"
    End If

Next Cell

Upvotes: 5

Related Questions