Results 1 to 4 of 4
  1. #1
    Join Date
    May 2012
    Posts
    4

    Angry Unanswered: Loop a macro a variable amount of times.


    I'm trying to run a macro that wil insert anumber of lines dependent on the number of countries entered in to a data dump> I've set it up so that he number of runs needed will be shown iin Sheet "data dump" cell j 2. The closest I can get it is below, which will run the macro once before crashing. any thoughts??



    Sub Add4()
    '
    ' Add4 Macro
    ' Macro recorded 02/05/2012 by GBWG6X
    '

    'Let x = 1

    Do

    Sheets("Sheet1").Select
    Rows("12:15").Select
    Selection.Copy
    Sheets("Imacs").Select
    Rows("16:16").Select
    Selection.Insert Shift:=xlDown
    Range("A16").Select
    x = x + 1


    Loop Until x = Sheets("Data Dump").cell(J, 2)

    End Sub

  2. #2
    Join Date
    May 2012
    Posts
    10
    Code:
    Sub Add4()
    
    Dim A As Long
    
    For A = 1 To Sheets("Data Dump").Range("J2").Value
        Sheets("Sheet1").Rows("12:15").Copy
        Sheets("Imacs").Rows(16).Insert xlShiftDown
    Next
    Application.CutCopyMode = False
    
    End Sub

  3. #3
    Join Date
    May 2012
    Posts
    4
    Thank you, but still coming up with a debug.

    If altered the rest of the marco that I need repeated. this is now as far I cna get but its still coming up with a debug error

    Sub Macro1()
    '
    ' Macro1 Macro

    '


    Dim i As Integer

    Dim xyz As Integer

    xyz = Sheets("Data Dump").Cell(J, 2).Value

    For i = 1 To xyz
    Sheets("Sheet1").Select
    Rows("12:15").Select
    Selection.Copy
    Sheets("Imacs").Select
    Cells.Find(What:="Total", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate

    Selection.Insert Shift:=xlDown
    Range("A16").Select

    Next i

    '
    End Sub

  4. #4
    Join Date
    May 2012
    Posts
    10
    Code:
    Sub Add4()
    
    Dim A As Long
    Dim LastRow As Long
    Dim TotalRow As Long
    
        With Sheets("Imacs")
            LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    
            'Finds the row# of 'Total'.  Change Column Letter as required.
            TotalRow = Application.WorksheetFunction.Match("Total", .Range("A1:A" & LastRow))
            If TotalRow > 0 Then
                For A = 1 To Sheets("Data Dump").Range("J2").Value
                    Sheets("Sheet1").Rows("12:15").Copy
                    Sheets("Imacs").Rows(TotalRow).Insert xlShiftDown
                Next
                Application.CutCopyMode = False
            End If
        'Selects the cell at the row of the insertion.
        .Range("A" & TotalRow).Select
        End With
    End Sub
    You seem to be working across three sheets. It is very important that you use fully qualified objects, especially if you insist on using Selection.

    .Cells(1,1).Select will select cells from the Active sheet, but Worksheet(1).Cells(1,1).Select will activate the cell on Sheet(1) regardless of which sheet is active. The macro record uses Selection exclusively, but it is not always the best use of VBA. I encourage you to learn how to use Ranges as quickly as you can. Your code will run much smoother without all the screen jumping.

    David

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •