For loop not initilizing when identical loop runs fine

S

srentiln

New Member
Joined
Dec 10, 2017
Messages
15
While continuing my journey in teaching myself macro scripting, I've run into an error that has me a bit confused.

What the macro should do:

Populate and format a worksheet (Training Audit) based on information from a second worksheet (Reference Data).

There are three categories (rau, qual, and ehs) that need to iterate in a nearly identical manner, so I have been working by getting just rau to function first. I then copy the working code to qual, make adjustments and test it, and repeat for qual.

The code I have so far:
Code: 
Sub tableBorders(where As Range, bold As Boolean, size As Integer)
With where
  .Merge
  .WrapText = True
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlCenter
  .BorderAround Weight:=xlThick
  .Font.size = size
  If bold Then
    .Font.FontStyle = "Bold"
  End If
End With
End Sub

Sub shift()
Dim sheeta As Worksheet, sheetb As Worksheet
Dim list1a As String, list2a As String, list3a As String, search1 As String, list1b() As String, list2b() As String, list2c() As String
Dim rau As Range, qual As Range, ehs As Range, spot1 As Range, spot2 As Range, c1 As Range, c2 As Range, sRange As Range, names As Range
Dim i1 As Variant, i2 As Variant
Set sheeta = Worksheets("Reference Data")
Set sheetb = Worksheets("Training Audit")
'clear previsou formatting
sheetb.Range("B5:N500").Clear
sheetb.Range("B5:N500").Font.size = 10
'formatting and titles
tableBorders sheetb.Range("B5:N5"), True, 10
tableBorders sheetb.Range("B6"), True, 10
tableBorders sheetb.Range("C6:F6"), True, 10
tableBorders sheetb.Range("G6:J6"), True, 10
tableBorders sheetb.Range("K6:N6"), True, 10
sheetb.Range("B5").Value = "Summary of Remaining Trainings"
sheetb.Range("B6").Value = "Name"
sheetb.Range("C6").Value = "Read and Understood"
sheetb.Range("G6").Value = "Qualified"
sheetb.Range("K6").Value = "EHS Documents"
'select applicable range
Set sRange = sheeta.Range("N5:AE5")
Set sRange = sRange.Resize(sheeta.Cells(Rows.Count, "N").End(xlUp).Row, sRange.Columns.Count)
Set c1 = sRange.Find("", , xlValues, , xlColumns)
Set sRange = sRange.Resize(c1.Row - 5, sRange.Columns.Count)
Set c1 = sRange.Find("", , xlValues, , xlRows)
Set sRange = sRange.Resize(sRange.Rows.Count, c1.Column - 14)
'set names
With sheeta.Cells
  Set c1 = .Find(sheeta.Range("B12"), .Range("C2"), xlValues, , xlColumns)
  If Not c1 Is Nothing Then
    Set names = c1.Offset(1, 0)
  End If
End With
Set names = names.Resize(9, 1)
Set c1 = names.Cells.SpecialCells(xlCellTypeBlanks)
Set names = names.Resize(c1.Row - names.Row, 1)
'populate summary names and formatting
Set spot1 = sheetb.Range("B7")
For Each c1 In names.Cells
  Set spot2 = spot1.Resize(3, 1)
  spot1.Value = c1
  tableBorders spot2, False, 9
  Set spot1 = spot1.Offset(0, 1)
  Set spot2 = spot1.Resize(3, 4)
  tableBorders spot2, False, 9
  Set spot1 = spot1.Offset(0, 1)
  Set spot2 = spot1.Resize(3, 4)
  tableBorders spot2, False, 9
  Set spot1 = spot1.Offset(0, 1)
  Set spot2 = spot1.Resize(3, 4)
  tableBorders spot2, False, 9
  Set spot1 = spot1.Offset(1, -9)
Next
'change names to give reference for populating summary
Set c2 = sheetb.Range("B7:" & spot1.Address)
Set names = Nothing
For Each c1 In c2.Cells
  If Not IsEmpty(c1.Value) Then
    If Not names Is Nothing Then
      Set names = Union(names, sheetb.Range(c1.Address))
    Else
      Set names = sheetb.Range(c1.Address)
    End If
  End If
Next
For Each c1 In names.Cells
  'rau
  With sheeta.Cells
    Set c2 = .Find(c1, .Range("M3"), xlValues, , xlRows)
    If Not c2 Is Nothing Then
      Set sRange = c2.Offset(2, 0)
    End If
  End With
  Set sRange = sRange.Resize(sheeta.Cells(Rows.Count, sRange.Column).End(xlUp).Row, 1)
  Set spot1 = sRange.Find("", , xlValues)
  Set sRange = sRange.Resize(spot1.Row - 4, 1)
  For Each c2 In sRange.Cells
    If c2.Value = 0 And Not sheeta.Range("H" & c2.Row).Value = "MP0405" Then
      If Not rau Is Nothing Then
        Set rau = Union(rau, sheeta.Range("H" & c2.Row))
      Else
        Set rau = sheeta.Range("H" & c2.Row)
      End If
    End If
  Next
  Set spot2 = c1.Offset(0, 1)
  If rau Is Nothing Then
    spot2.Value = "No Read and Understood Gaps"
  Else
    For Each c2 In rau
      If spot2.Value = "" Then
        spot2.Value = c2.Value
      Else
        spot2.Value = spot2.Value & " " & c2.Value
      End If
    Next
  End If
  'qual
  Set sRange = sRange.Offset(0, 1)
  For Each c2 In sRange.Cells
    If c2.Value = 0 And Not sheeta.Range("H" & c2.Row).Value = "MP0405" And Not sheeta.Range("H" & c2.Row).Value = "Tour" Then
      If Not qual Is Nothing Then
        Set qual = Union(qual, sheeta.Range("H" & c2.Row))
      Else
        Set qual = sheeta.Range("H" & c2.Row)
      End If
    End If
  Next
  Set spot2 = c1.Offset(0, 5)
  If qual Is Nothing Then
    spot2.Value = "No Qualified Gaps"
  Else
    For Each c2 In qual
      If spot2.Value = "" Then
        spot2.Value = c2.Value
      Else
        spot2.Value = spot2.Value & " " & c2.Value
      End If
    Next
  End If
  'ehs
  With sheeta.Cells
    Set c2 = .Find(c1, .Range("BO3"), xlValues, xlRows)
    If Not c2 Is Nothing Then
      Set sRange = c2.Offset(1, 0)
    End If
  End With
  Set sRange = sRange.Resize(sheeta.Cells(Rows.Count, sRange.Column).End(xlUp).Row, 1)
  Set spot1 = sRange.Find("", , xlValues)
  Set sRange = sRange.Resize(spot1.Row - 4, 1)
  For Each c2 In sRange.Cells
    If c2.Value = 0 Then
      If Not ehs Is Nothing Then
        Set ehs = Union(ehs, sheeta.Range("BI" & c2.Row))
      Else
        Set ehs = sheeta.Range("BI" & c2.Row)
      End If
    End If
  Next
  Set spot2 = c1.Offset(0, 9)
  If ehs Is Nothing Then
    spot2.Value = "No EHS Gaps"
  Else
    For Each c2 In ehs
      If c2.Row = 5 Then
        spot2.Value = "na1"
      ElseIf c2.Row = 6 Then
        If spot2.Value = "" Then
          spot2.Value = "na2"
        Else
          spot2.Value = spot2.Value & " na2"
        End If
      Else
        If spot2.Value = "" Then
          spot2.Value = c2.Value
        Else
          spot2.Value = spot2.Value & " " & c2.Value
        End If
      End If
    Next
  End If
  Set ehs = Nothing
  Set rau = Nothing
  Set qual = Nothing
Next
'detailed information formatting
Set spot1 = spot2.Offset(2, -9)
Set spot2 = spot1.Resize(1, 13)
tableBorders spot2, True, 10
spot1.Value = "Detailed Information"
Set spot1 = spot1.Offset(1, 0)
Set spot2 = spot1.Resize(1, 9)
tableBorders spot2, True, 10
spot1.Value = "SOP Gaps"
Set spot1 = spot1.Offset(0, 1)
Set spot2 = spot1.Resize(2, 4)
tableBorders spot2, True, 10
spot1.Value = "EHS Gaps"
Set spot1 = spot1.Offset(1, -9)
Set spot1 = spot1.Offset(-1, 0)
Set spot2 = spot1.Resize(1, 4)
tableBorders spot2, True, 10
spot1.Value = "Read and Understood"
Set spot1 = spot1.Offset(0, 1)
Set spot2 = spot1.Resize(1, 5)
tableBorders spot2, True, 10
spot1.Value = "Qualified"
Set spot1 = spot1.Offset(1, -4)
tableBorders spot1, True, 10
spot1.Value = "Number"
Set spot1 = spot1.Offset(0, 1)
Set spot2 = spot1.Resize(1, 2)
tableBorders spot2, True, 10
spot1.Value = "Title"
Set spot1 = spot1.Offset(0, 1)
tableBorders spot1, True, 10
spot1.Value = "People"
Set spot1 = spot1.Offset(0, 1)
tableBorders spot1, True, 10
spot1.Value = "Number"
Set spot1 = spot1.Offset(0, 1)
Set spot2 = spot1.Resize(1, 2)
tableBorders spot2, True, 10
spot1.Value = "Title"
Set spot1 = spot1.Offset(0, 1)
tableBorders spot1, True, 10
spot1.Value = "People"
Set spot1 = spot1.Offset(0, 1)
tableBorders spot1, True, 10
spot1.Value = "Trainers"
Set spot1 = spot1.Offset(0, 1)
tableBorders spot1, True, 10
spot1.Value = "Number"
Set spot1 = spot1.Offset(0, 1)
Set spot2 = spot1.Resize(1, 2)
tableBorders spot2, True, 10
spot1.Value = "Title"
Set spot1 = spot1.Offset(0, 1)
tableBorders spot1, True, 10
spot1.Value = "People"
'list creation
Set spot1 = spot1.Offset(1, -12)
Set sRange = sheetb.Range("C7:" & spot1.Offset(-8, 1).Address)
For Each c1 In sRange.Cells
  If Not c1.Value = "" And Not c1.Value = "No Read and Understood Gaps" Then
    If Not list1a = "" Then
      list1a = list1a & " " & c1.Value
    Else
      list1a = c1.Value
    End If
  End If
Next
Set sRange = sRange.Offset(0, 4)
For Each c1 In sRange.Cells
  If Not c1.Value = "" And Not c1.Value = "No Qualified Gaps" Then
    If Not list1a = "" Then
      list2a = list1a & " " & c1.Value
    Else
      list2a = c1.Value
    End If
  End If
Next
Set sRange = sRange.Offset(0, 4)
For Each c1 In sRange.Cells
  If Not c1.Value = "" And Not c1.Value = "No EHS Gaps" Then
    If Not list1a = "" Then
      list3a = list1a & " " & c1.Value
    Else
      list3a = c1.Value
    End If
  End If
Next
list1b = Split(list1a, " ")
iist2b = Split(list2a, " ")
list3b = Split(list3a, " ")
'detailed information population
'rau
Set spot2 = spot1
Set sRange = sheetb.Range("C7:" & spot1.Offset(-8, 1).Address)
For Each i1 In list1b
  sheetb.Range("B22").Value = i1
  If Not i1 = spot2.Offset(-1, 0).Value Then
    spot2.Value = i1
    Set spot2 = spot2.Resize(4, 1)
    tableBorders spot2, False, 10
    Set spot2 = spot2.Offset(0, 1)
    spot2.Value = "=INDEX('Reference Data'!$I$5:$I$202,MATCH('Training Audit'!" & spot2.Offset(0, -1).Address & ",'Reference Data'!$H$5:$H$202,0))"
    Set spot2 = spot2.Resize(4, 2)
    tableBorders spot2, False, 9
    Set spot2 = spot2.Offset(0, 1)
    For Each c1 In sRange.Cells
      If InStr(c1.Text, i1) > 0 Then
        If Not spot2.Value = "" Then
          spot2.Value = spot2.Value & ", " & c1.Offset(0, -1).Value
        Else
          spot2.Value = c1.Offset(0, -1).Value
        End If
      End If
    Next
    Set spot2 = spot2.Resize(4, 1)
    tableBorders spot2, False, 9
  End If
  Set spot2 = spot2.Offset(1, -3)
Next
'qual
Set spot2 = spot1.Offset(0, 4)
Set sRange = sheetb.Range("G7:" & spot1.Offset(-8, 5).Address)
[B][COLOR=#ff0000]For Each i2 In list2b[/COLOR][/B]
  sheetb.Range("B22").Value = i2
  If Not i2 = spot2.Offset(-1, 0).Value Then
    spot2.Value = i2
    Set spot2 = spot2.Resize(4, 1)
    tableBorders spot2, False, 10
    Set spot2 = spot2.Offset(0, 1)
    spot2.Value = "=INDEX('Reference Data'!$I$5:$I$202,MATCH('Training Audit'!" & spot2.Offset(0, -1).Address & ",'Reference Data'!$H$5:$H$202,0))"
    Set spot2 = spot2.Resize(4, 2)
    tableBorders spot2, False, 9
    Set spot2 = spot2.Offset(0, 1)
    For Each c1 In sRange.Cells
      If InStr(c1.Text, i2) > 0 Then
        If Not spot2.Value = "" Then
          spot2.Value = spot2.Value & ", " & c1.Offset(0, -5).Value
        Else
          spot2.Value = c1.Offset(0, -1).Value
        End If
      End If
    Next
    Set spot2 = spot2.Resize(4, 1)
    tableBorders spot2, False, 9
  End If
  Set spot2 = spot2.Offset(1, -3)
Next
'ehs
End Sub

At the line in red, I am getting the error:
Run-time error '92':

For loop not initialized
Click to expand...

I initially had both For Each loops using i1 before adding i2 to see if that would solve the issue, but it did not.

The MSDN page for the error code claims that I jumped into the middle of a For...Next loop, but stepping through the code does not look like that is the case from what I am seeing.

What am I missing/overlooking?
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
Where is list2b getting it's value from?
 
Upvote 0
There is a typo on this line:
Code: 
iist2b = Split(list2a, " ")
It should be list2b = ...

Type Option Explicit at the top of the module to trap these type of coding errors. To put Option Explicit automatically at the top of every module, in the VBA editor click Tools > Options > Editor tab > tick Require Variable Declaration.
 
Upvote 0
and now I feel like I need glasses for not seeing that I typed ii instead of li.

Thank you very much for the suggestion, I don't think this will be the only time I overlook that particular typo.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
VBA to check if worksheet exists
Replies
8
Views
345
bdt
B
J
  • Solved
VBA Code Error Unable to get the Match property of the WorksheetFunction class
Replies
8
Views
393
Jiraya_00
J
WERNER SLABBERT
  • Question Question
Run-time error 70 after code changed
Replies
3
Views
121
RoryA
RoryA
G
  • Solved
Run-Time Error 381
2
Replies
12
Views
213
Micron
M
L
  • Question Question
CDO_Mail_Small_Text Run time error
Replies
0
Views
155
Louis chiron
L

Forum statistics

Threads
1,224,917
Messages
6,181,733
Members
453,064
Latest member
robatthe2A

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top