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??
' Add4 Macro
' Macro recorded 02/05/2012 by GBWG6X
'Let x = 1
x = x + 1
Dim A As Long
Dim LastRow As Long
Dim TotalRow As Long
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
Application.CutCopyMode = False
'Selects the cell at the row of the insertion.
.Range("A" & TotalRow).Select
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.