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

dnicholsby

New Member
Joined
Jan 24, 2017
Messages
26
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?

Code:
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
You haven't given KeyWord a value.
Also you have
Code:
Next KeyWord
but there is nothing to start that loop
 
Upvote 0
Ah sorry - copied the wrong bit. Do i need to add a for each keyword somwhere?

Code:
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
 
Upvote 0
Do i need to add a for each keyword somwhere?
Yup, try
Code:
   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
 
Upvote 0
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?
 
Upvote 0
It's better to figure out what is causing the error & prevent it.
What was the error message & what row was highlighted?
 
Upvote 0
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:

Code:
 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?
 
Last edited:
Upvote 0
This will skip the cells with an error
Code:
            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.
 
Upvote 0
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!
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

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