For loop in VBA Code

Question

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!

are copying and pasting in the same sheet? and these ranges Range(a,18) and Range(b,3) are always the same? — paulinhax
– paulinhax, Commented Aug 2, 2017 at 13:51
try changing this Macro.Application.Range to this ActiveSheet.Range — braX
– braX, Commented Aug 2, 2017 at 13:55
Yes, I am copying and pasting in the same sheet. I will always be copying from columns R:X and pasting in columns C:I — Emma
– Emma, Commented Aug 2, 2017 at 13:55
@braX when I make that change I get a new error message, Run-time error '1004': Method 'Range' of object '_Global' failed — Emma
– Emma, Commented Aug 2, 2017 at 13:56
i.sstatic.net/km8Ke.png Here is a link to the screenshots. I want the top photo to look like the bottom photo. — Emma
– Emma, Commented Aug 2, 2017 at 13:57

Vityata · Accepted Answer · 2017-08-02 14:10:45Z

1

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

edited Aug 2, 2017 at 14:10

answered Aug 2, 2017 at 13:58

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Emma Over a year ago

For some reason, that just seemed to remove column t of my data.

Vityata Over a year ago

@Emma - try to code again. Take a look at the immediate window every time the code stops.

Emma Over a year ago

Every round, it prints out: copying from =>$T$2 copying to =>$AI$5

Vityata Over a year ago

Ok. Then try to adjust it a bit to what you want, by changing a, b, 3 and 18, @Emma

Emma Over a year ago

Thank you so much! (I'm new!)

|

Collectives™ on Stack Overflow

For loop in VBA Code

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related