VBA- Cell is a number then copy it

473 Views Asked by At

I have in E, F and I columns something written. In E and F its a name and in I its a number. In I column there are some cells with number and rest are blank. When ever its a number, I should copy the names from E1, F1 and copy it to C3 and D3 respectively. I have written a code but its not functioning properly. Could you please help me.

Option Explicit

Sub copy()
    Dim cell As Long
    Dim nr As Integer
    Dim rng As range
    Dim i As Long

    range("G1:G15").Select
    Selection.CurrentRegion.Select
    nr = Selection.Rows.Count
    For i = 2 To nr
        'test if cell is empty
        If ActiveCell(i, 7) = "" Then
            'write to adjacent cell
            ActiveCell = ActiveCell + 1
        Else
            ActiveCell.Offset(-3, -2).Value.copy ActiveCell.Offset(-3, 0)
        End If
    Next i
End Sub
1

There are 1 best solutions below

0
On
Option Explicit

Sub copy()
    Dim Row As Long
    Dim Col As Long
    
    Row = 2
    Col = 9
    Do Until Cells(Row, 5).Value = ""
        If IsNumeric(Cells(Row, Col)) = True Then
            Range("E" & Row & ":F" & Row).copy
            Range("C" & Row).PasteSpecial (xlPasteValues)
        End If
        Row = Row + 1
    Loop
    Application.CutCopyMode = False
End Sub