Copy last 3 char of text in one column to another column if cell is blank in excel spreadsheet with-CodePudding

If cell in Range("H1:H104000") is "" Then
Range("H1:H104000) = LEFT(Range("D1:D104000), 3
End If

This is the code I am trying with no sucess.

CodePudding user response：

You're missing the Loop. You need to write the loop to iterate through the collection of cells, the code can't do it implicitly even if you compare a single range to a collection of ranges.

Also, to compare values use =. The Is operator is only used for Objects.

Dim Cell As Range
For Each Cell In Range("H1:H104000").Cells
    If Cell.Value = "" Then
        Cell.Value = Right(Cell.Offset(0, -4).Value, 3)
    End If
Next

Once you're iterating through the column H. An easy way to refer to "column D in the current row" is by using Offset, which will return a cell, relative to your given starting position. In this case, we just need to move 4 columns to the left so I do .Offset(0,-4)