VBA: InStr(x,Keyword) where keyword is a variable! My code doesn't work

[dnicholsby]

Hello,

I have 6 keywords that I would like to cycle through with the following code but I can't seem to get it to work. Any ideas?

Sub ChangeLinks()


Dim wb As Workbook
Set wb = Application.ActiveWorkbook
Dim Path As String
Path = Application.ActiveWorkbook.Path
Dim NewName As String
Dim KeyWord As Variant


For Each link In wb.LinkSources(xlExcelLinks)
    If InStr(link, KeyWord) Then
        For Each cl In Sheets("Start Here").Range("b6:b12")
        If cl.Value = KeyWord Then
            NewName = Path & "\" & cl.Offset(0, -1).Value
            wb.ChangeLink Name:=link, NewName:=NewName, Type:=xlExcelLinks
        End If
        Next cl
    Next KeyWord
    End If
Next link


End Sub

 

[Fluff]

You haven't given KeyWord a value.
Also you have

Next KeyWord

but there is nothing to start that loop

 

[dnicholsby]

Ah sorry - copied the wrong bit. Do i need to add a for each keyword somwhere?

Dim wb As WorkbookSet wb = Application.ActiveWorkbook
Dim Path As String
Path = Application.ActiveWorkbook.Path
Dim NewName As String
Dim KeyWord As Variant
KeyWord = Array("Cautious", "Balanced", "Growth", "Global", "UK", "Bond")


For Each link In wb.LinkSources(xlExcelLinks)
    If InStr(link, KeyWord) Then
        For Each cl In Sheets("Start Here").Range("b6:b12")
        If cl.Value = KeyWord Then
            NewName = Path & "\" & cl.Offset(0, -1).Value
            wb.ChangeLink Name:=link, NewName:=NewName, Type:=xlExcelLinks
        End If
        Next cl
    Next KeyWord
    End If
Next link


End Sub

 

[Fluff]

Do i need to add a for each keyword somwhere?

Yup, try

   Dim wb As Workbook
   Set wb = Application.ActiveWorkbook
   Dim Path As String
   Path = Application.ActiveWorkbook.Path
   Dim NewName As String
   Dim KeyWord As Variant

   For Each Link In wb.LinkSources(xlExcelLinks)
      For Each KeyWord In Array("Cautious", "Balanced", "Growth", "Global", "UK", "Bond")
         If InStr(Link, KeyWord) Then
            For Each Cl In Sheets("Start Here").Range("b6:b12")
               If Cl.Value = KeyWord Then
                  NewName = Path & "\" & Cl.Offset(0, -1).Value
                  wb.ChangeLink Name:=Link, NewName:=NewName, Type:=xlExcelLinks
               End If
            Next Cl
         End If
      Next KeyWord
   Next Link

 

[dnicholsby]

Ah bingo! Now we're getting somewhere! That worked for the first keyword but then as it went to the next cl i got a run time error. Can i add something like

if error go to next keyword?

 

[Fluff]

It's better to figure out what is causing the error & prevent it.
What was the error message & what row was highlighted?

 

[dnicholsby]

It's better to figure out what is causing the error & prevent it.
What was the error message & what row was highlighted?

So just to paint a picture cells A6:A12 contain the file name and B6:B12 contain the keyword.

If i run the code it in cell B6 is "Balanced" and that file link updates. Cell b7 is "Cautious" and thats when i get the error on this line:

 If cl.Value = KeyWord Then

cl = Error 2015

I also have some #VALUE errors in cells b11 and b12 but just want the code to skip those lines. Could this be causing the error?

 

[Fluff]

This will skip the cells with an error

            For Each cl In Range("b1:b12")
               If Not IsError(cl.Value) Then
                  If cl.Value = KeyWord Then
                     NewName = Path & "\" & cl.Offset(0, -1).Value
                     wb.ChangeLink Name:=link, NewName:=NewName, Type:=xlExcelLinks
                  End If
               End If
            Next cl

But I don't understand why you get an error on that line is there is a word in it.

 

[dnicholsby]

It didn't like the #VALUE errors. I changed my code in the spreadsheet to return "null" if there's an error and that seems to have done the trick.

Thanks for your help, in fact mega thanks!

 

[Fluff]

Glad to help & thanks for the feedback