CODEWITHSUNDEEP
vbaexcel
falco
falco

Reputation: 27

Nested If Then and For Statements

I know how to fix the error in the macro below, but not the why behind the fix. Why is the " _" causing problems in the second If...Then statement?

I can fix it by either deleting the " _" or inserting a line below it. However, I can't move the For Statement to the end of the If...Then statement.

Sub Loop_Link_Change_ROE()

Dim MyPath As String
Dim MyFile As String
Dim sLink As String
Dim sNewName As String
Dim tNewName As String
Dim varLinks As Variant
Dim i As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

MyPath = "P:\Department\Actuarial Archive\Reserves\2018Q2\Documentation\Data Recon\"
MyFile = Dir(MyPath)

Do While MyFile <> ""
If MyFile Like "*Analysis_Regions_Phys_CM_*.xl*" Then
Workbooks.Open Filename:=MyPath & MyFile, UpdateLinks:=0

varLinks = ActiveWorkbook.LinkSources(xlExcelLinks)

If Not IsEmpty(varLinks) Then _
For i = 1 To UBound(varLinks)

    If InStr(1, varLinks(i), "4Q17") Then sNewName = Replace(varLinks(i), "4Q17", "2Q18")
    If InStr(1, varLinks(i), "GA") Then tNewName = Replace(sNewName, "GA", "Mid(varLinks(i), 30, 2)")
    If InStr(1, varLinks(i), "GA") Then ActiveWorkbook.ChangeLink Name:=varLinks(i), NewName:=tNewName, Type:=xlExcelLinks

ActiveWorkbook.Close True

Next

End If

End If

MyFile = Dir

Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True

End Sub

Thanks in advance for any help!

Upvotes: 0

Views: 74

Answers (2)

Tim Williams
Tim Williams

Reputation: 166735

There are two basic forms using If in VBA

All one one line:

If {test} Then {something}

...in which case subsequent lines have no connection with the If

Using End If and multiple lines:

If {test} Then
   {something} 'one or more lines of code
End If

Your _ is not doing anything useful there

Upvotes: 1

BateTech
BateTech

Reputation: 6526

Underscore is a line continuation in vb so if you use it then you do not need an "end if" for that if statement because the for loop line below is treated as if it were part of the previous line.

Upvotes: 1

Related Questions