VBA Error 9. HELP!

kpurev

New Member
Joined
Nov 14, 2013
Messages
1
Hey guys, when I try to run macro, it is giving me error 9. Please help!!! Here is my coding, and I cannot figure out what is wrong with it.

Sub HR_Amazon()
Dim sVariable As String
Dim vEntry As Variant
Dim iCounter As Integer
Dim iCounter2 As Integer
Dim iCounter3 As Integer
Dim aiRecordLocator() As Integer


For iCounter = 1 To 2500
If Left(Sheets("Sheet1").Range("A1").Offset(iCounter - 1, 0), 6) = "Record" Then
iRecordCounter = iRecordCounter + 1
ReDim Preserve aiRecordLocator(1 To iRecordCounter)
aiRecordLocator(iRecordCounter) = iCounter
End If

Next iCounter

For iCounter = 1 To 400
For iCounter2 = aiRecordLocator(iCounter) To (aiRecordLocator(iCounter + 1) - 2)
For iCounter3 = 1 To (Sheets("Sheet1").Range("A1").Offset(iCounter2, 0).End(xlToRight).Column - 1) Step 2

sVariable = Sheets("Sheet1").Range("A1").Offset(iCounter2, iCounter3)
vEntry = Sheets("Sheet1").Range("A1").Offset(iCounter2, iCounter3 + 1)

If sVariable = "Competing Local Firms" Then
Sheets("Sheet2").Range("A1").Offset(iCounter, 0) = vEntry
ElseIf sVariable = "Avg. Longevity" Then
Sheets("Sheet2").Range("A1").Offset(iCounter, 1) = vEntry
ElseIf sVariable = "Avg. Salary" Then
Sheets("Sheet2").Range("A1").Offset(iCounter, 2) = vEntry
ElseIf sVariable = "Avg. Education (Yrs.)" Then
Sheets("Sheet2").Range("A1").Offset(iCounter, 3) = vEntry
ElseIf sVariable = "Avg. Height" Then
Sheets("Sheet2").Range("A1").Offset(iCounter, 4) = vEntry
ElseIf sVariable = "Avg. Household Size" Then
Sheets("Sheet2").Range("A1").Offset(iCounter, 5) = vEntry
ElseIf sVariable = "Region" Then
Sheets("Sheet2").Range("A1").Offset(iCounter, 6) = vEntry
ElseIf sVariable = "Coast" Then
Sheets("Sheet2").Range("A1").Offset(iCounter, 7) = vEntry
ElseIf sVariable = "Budget Boost" Then
Sheets("Sheet2").Range("A1").Offset(iCounter, 8) = vEntry
ElseIf sVariable = "Proportion Male" Then
Sheets("Sheet2").Range("A1").Offset(iCounter, 9) = vEntry
ElseIf sVariable = "Local Unemployment" Then
Sheets("Sheet2").Range("A1").Offset(iCounter, 10) = vEntry
ElseIf sVariable = "Avg. Age" Then
Sheets("Sheet2").Range("A1").Offset(iCounter, 11) = vEntry
End If

Next iCounter3
Next iCounter2
Next iCounter

End Sub
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

dermie_72

Well-known Member
Joined
Sep 4, 2012
Messages
1,540
try changing For iCounter2 = aiRecordLocator(iCounter) To (aiRecordLocator(iCounter + 1) - 2)
to For iCounter2 = LBound(aiRecordLocator) To UBound(aiRecordLocator)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,005
Messages
5,526,233
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top