Search code examples
excelvbacopypastenumeric

Excel VBA, If cell is not numeric and empty in a column, selected cell copy and paste to another column

A   B         C   
a   rm        rm
b   
c   
d   4000
e   5000
f   r1        r1
g   c1        c1
h   103
i   1.8

For example, in B Column, if cell value are not number, copy the value to C Column

how to make that code?

please, inquiry for that!

Solution

  • At first you need to find the last non blank row number of column A. Then run a loop and check if the value of column A is numeric or not.

    Public Sub M()
Dim sh As Worksheet
Set sh = Worksheets("Worksheet name here")
Dim lastrow As Long, i As Long
lastrow = sh.Cells(1048576, 2).End(xlUp).Row
For i = 1 To lastrow
If IsNumeric(.Cells(i, 2).Value) = False Then
.Cells(i, 3).Value = .Cells(i, 2).Value
End If
Next i

    End Sub