CODEWITHSUNDEEP
vbaexcelfor-loop
Emma
Emma

Reputation: 13

For loop in VBA Code

NOTE: I was able to upload a link to a picture in the comments.

I am using a for loop in an excel macro in an attempt to compile data.

I have the following VBA code:

Sub MoveData()
'
' MoveData Macro
'

'
Dim rng As Range
Set rng = Macro.Application.Range("R1:X1000")
With rng


    For i = 1 To 200
        a = 5 * i
        b = i + 1

        Range(a, 18).Select
        Selection.Copy
        Range(b, 3).Select
        ActiveSheet.Paste

    Next i

End With

End Sub

I get the following error message: Run-time error '424':

Object required

Any clarification would be much appreciated! I've been stumped on this for quite a while!

Upvotes: 1

Views: 330

Answers (1)

Vityata
Vityata

Reputation: 43575

Try like this and make sure your values are on the activesheet.

Sub MoveData()

    Dim rng As Range

    Set rng = ActiveSheet.Range("R1:X1000")

    With rng
        For i = 1 To 200
            a = 5 * i
            b = i + 1

            Debug.Print "copying from =>" & .Cells(b, 3).Address
            Debug.Print "copyting to =>" & .Cells(a, 18).Address

            Stop 'and take a look at the immediate window
            ' what is there?

            .Cells(b, 3) = .Cells(a, 18)
        Next i
    End With

End Sub

Upvotes: 1

Related Questions