Syntax Question

[stuartgb100]

The following code snippet works well:
Sheets("Index").Range("B2").Hyperlinks.Add _
Anchor:=Sheets("Index").Range("B2"), Address:="", _
SubAddress:="'Artists 1'!B2", _
TextToDisplay:=Sheets("Artists 1").Range("B2").Value

However, when I try to use variables, it does not work:
Sheets("Index").Range("B" & TargetRw).Hyperlinks.Add _
Anchor:=Sheets("Index").Range("B" & TargetRw), Address:="", _
SubAddress:="'" & SourceSht.Name & "'" & .Range("B" & StartRw), _
TextToDisplay:=Sheets(SourceSht).Range("B" & StartRw).Value

I think there's a Type Mismatch in the last but 1 line.
Any help would be much appreciated.
Thanks.

 

[Joe4]

Are you getting some sort of error message?
What is the exact number and message of that error?

Have you verified the value of all your variables, to make sure that they are returning the values you expect, i.e.
- TargetRw
- SourceSht
- StartRw

You haven't posted the section of your code where these values are being populated, so we cannot determine if you may have made some sort of error there.

 

[Alex Blakenburg]

Try this:

    With SourceSht
        Sheets("Index").Range("B" & TargetRw).Hyperlinks.Add _
            Anchor:=Sheets("Index").Range("B" & TargetRw), Address:="", _
            SubAddress:="'" & .Name & "'!" & .Range("B" & StartRw).Address, _
            TextToDisplay:=SourceSht.Range("B" & StartRw).Value
    End With

 

[stuartgb100]

Thanks Alex,
However, this returns Run-Time Error 1004
Application-defined or object-defined error
.

 

[stuartgb100]

Thanks Joe4,
Apologies for the incomplete information.
The Error Message is Run-Time Error 1004
Application-defined or object-defined error
The (edited) section of code is ............

Sub CreateSub_MenuOfHyperlinksToAllWorksheets()
Dim objSheet As Worksheet, SourceSht As Worksheet
Dim TargetSht As Worksheet, TargetRw As Long
Dim StartRw As Long, EndRw As Long

Set TargetSht = Sheets("Index")

For Each objSheet In ActiveWorkbook.Worksheets
If objSheet.Name <> "Index" Then
Set SourceSht = objSheet
TargetRw = 1
With SourceSht
Sheets("Index").Range("B" & TargetRw).Hyperlinks.Add _
Anchor:=Sheets("Index").Range("B" & TargetRw), Address:="", _
SubAddress:="'" & SourceSht.Name & "'" & .Range("B" & StartRw), _
TextToDisplay:=Sheets(SourceSht).Range("B" & StartRw).Value
End With
End If
Next
End Sub

 

[Joe4]

There is your problem right there!
You are not setting "StartRw" to anything, so its value is zero, and cell "B0" is not a valid cell!

 

[Alex Blakenburg]

The item @Joe4 picked up is certainly fatal.
There are more issues though.
1) Please use the code buttons, I find the VBA button the easiest.
2) You are not incrementing the target row

TargetRw = 1
TargetRw = TargetRw + 1

3) You did not use the code I gave you, replace the code below with what I gave you in post#3

With SourceSht
           Sheets("Index").Range("B" & TargetRw).Hyperlinks.Add _
               Anchor:=Sheets("Index").Range("B" & TargetRw), Address:="", _
               SubAddress:="'" & SourceSht.Name & "'" & .Range("B" & StartRw), _
               TextToDisplay:=Sheets(SourceSht).Range("B" & StartRw).Value
End With

Here is post#3 again modified to use TargetSht

    With SourceSht
        TargetSht).Range("B" & TargetRw).Hyperlinks.Add _
            Anchor:=TargetSht.Range("B" & TargetRw), Address:="", _
            SubAddress:="'" & .Name & "'!" & .Range("B" & StartRw).Address, _
            TextToDisplay:=SourceSht.Range("B" & StartRw).Value
    End With

 

[stuartgb100]

Sorry, hit 'Post Reply' too early <doh>
Variables are declared as follows ( at the point of running the Hyperlink line:
objSheet Worksheet/Sheet1
SourceSht Worksheet/Sheet1
TargetSht Worksheet/Sheet8
TatgetRw 1 as Long
StartRw 0 as Long
EndRw 0 as Long

 

[Joe4]

The issue isn't the variable declaration!

The issue I mentioned is that nowhere in your code are you setting StartRw to anything!
So, it would default to a value of 0. And you cannot have a row with number 0. That is invalid and result in an error!

 

[stuartgb100]

Joe4 & Alex,
Please ignore my last few posts.
Sincere apologies for wasting your time.
I did have the variables correctly defined, but did not post the correct code snippet
Must be getting too old for this !
Alex, that worked fine, thanks.

I dip in and out of vba, sometimes with a year or more between episodes.
I forget so much - even the simple things !

Very many thanks both, for bearing with me.
Cheers.