Hyperlink replacement value question?

[hardroader]

Excel Guru:
I am attempting to use an Excel macro to replace a certain value in a hyperlink of a cell with a known value in certain worksheets.

Background:Using Excel 2010 with Windows7 (64bit)
1 Workbook contains 40 worksheets or so
Hyperlinks are located in the follow ranges:
A351:A373
E351:E388
J351:J353
M351:M360
Q351:Q355
Z351:Z400
AE351:AE378


The Plan:User to input the name of the worksheet to become the active worksheet.Then replacepart of the hyperlink value.In every worksheet, cell A2 contains part of the replacement value for the hyperlink.The replace value is /XXXX/ in the hyperlink ranges above.The slashes are part of the replace value.

My code is producing a run-time error code 1004 (Application-defined or object-defined error). Was trying to follow previous example for replacing various normal cell formulas. Thanking you in advance for your assistance.

Coding is:

Dim FirstRow As Long

Dim UserEntry As String



' Select the ticker value you wish to review

' Redo:

Do

UserEntry = InputBox("Enter the worksheet name to review")

If UserEntry = "" Then Exit Sub ' User cancelled

If Evaluate("ISREF('" & UserEntry & "'!A1)") Then Exit Do ' Test if worksheet name exists

MsgBox "Unable to find sheet named: " & UserEntry, , "Sheet Not Found"
Loop


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Range("A351").Select
FirstRow = 351
X = 1
With Worksheets(UserEntry)
Range("A2").Copy Destination:=.Range(FirstRow, 0).Selection.Hyperlinks(1).Address
For X = 1 To 23
Rows.Range(FirstRow, 0).Replace What:=("XXXX"), _
Replacement:=.Range("A2").Value, _
LookAt:=xlPart, _
MatchCase:=False

FirstRow = FirstRow + 1


Next X
End With

FirstRow = 351


Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

 

[knigget40]

I am not exactly sure with the information provided if my code changes will meet your needs, but they will remove the errors and hopefully help you see the actual results of the code, and then perhaps you can modify the code to meet your end goals.
</SPAN>
My base assumption is that a sheet such as Sheet1 is where the macro is executed, and then the user selects a second sheet such as Sheet2. I will not make any other assumptions at this time.</SPAN>

In the row with “Range("A2").Copy Destination:=.Range(FirstRow, 0).Selection.Hyperlinks(1).Address”:</SPAN>
First, “Range” will not work with “row, cell” parameters. You should use “Cell” instead of “Range”. </SPAN>
Also, the values must be positive, so 0 will not work. </SPAN>
I do not believe you need the part which is “.Selection.Hyperlinks(1).Address”. </SPAN>
Finally, I believe from your description you want to copy A2 from the current sheet to A351 on the destination sheet (i.e., the user selected sheet?). If that is the case then you need to add a “.” in from of the destination reference so the pasted data ends up on Worksheets(UserEntry)</SPAN>
So the formula would be: Range("A2").Copy Destination:=.Cells(FirstRow, 1)</SPAN>

Then in the FOR loop:</SPAN>
As above, use “.CELLS” vs. “RANGE”.</SPAN>
Also, I assume instead of the 0 in the formula you want to use the value of X from the FOR loop? And you may want to use a different X value so you do not overwrite the cell you initially copied to the new sheet. Also, the current logic changes destination cells in a diagonal pattern which may not meet your needs.</SPAN>
And in this case, if you are referencing A2 from the source worksheet (e.g., Sheet1) instead of Worksheets(UserEntry) (e.g., Sheet2), then you would not want to use the “.” reference in front of Range("A2").Value. If my assumption is incorrect, then you can leave it as is.</SPAN>
So the formula would be: .Cells(FirstRow, X).Replace What:=("XXXX"), Replacement:=Range("A2").Value, _ ).</SPAN>

So, the resulting updated lines would be as follows. Based on your actual intent you may need to change some of the logic:</SPAN>

Range("A351").Select</SPAN>
FirstRow = 351</SPAN>
X = 1 ' note, this line is not needed</SPAN>
With Worksheets(UserEntry)</SPAN>
Range("A2").Copy Destination:=.Cells(FirstRow, 1) </SPAN>
For X = 1 To 23</SPAN>
.Cells(FirstRow, X).Replace What:=("XXXX"), _</SPAN>
Replacement:=.Range("A2").Value, _</SPAN>
LookAt:=xlPart, _</SPAN>
MatchCase:=False</SPAN>

FirstRow = FirstRow + 1</SPAN>
Next X</SPAN>
End With</SPAN>

 

[hardroader]

I am not exactly sure with the information provided if my code changes will meet your needs, but they will remove the errors and hopefully help you see the actual results of the code, and then perhaps you can modify the code to meet your end goals.

My base assumption is that a sheet such as Sheet1 is where the macro is executed, and then the user selects a second sheet such as Sheet2. I will not make any other assumptions at this time.

In the row with “Range("A2").Copy Destination:=.Range(FirstRow, 0).Selection.Hyperlinks(1).Address”:
First, “Range” will not work with “row, cell” parameters. You should use “Cell” instead of “Range”.
Also, the values must be positive, so 0 will not work.
I do not believe you need the part which is “.Selection.Hyperlinks(1).Address”.
Finally, I believe from your description you want to copy A2 from the current sheet to A351 on the destination sheet (i.e., the user selected sheet?). If that is the case then you need to add a “.” in from of the destination reference so the pasted data ends up on Worksheets(UserEntry)
So the formula would be: Range("A2").Copy Destination:=.Cells(FirstRow, 1)

Then in the FOR loop:
As above, use “.CELLS” vs. “RANGE”.
Also, I assume instead of the 0 in the formula you want to use the value of X from the FOR loop? And you may want to use a different X value so you do not overwrite the cell you initially copied to the new sheet. Also, the current logic changes destination cells in a diagonal pattern which may not meet your needs.
And in this case, if you are referencing A2 from the source worksheet (e.g., Sheet1) instead of Worksheets(UserEntry) (e.g., Sheet2), then you would not want to use the “.” reference in front of Range("A2").Value. If my assumption is incorrect, then you can leave it as is.
So the formula would be: .Cells(FirstRow, X).Replace What:=("XXXX"), Replacement:=Range("A2").Value, _ ).

So, the resulting updated lines would be as follows. Based on your actual intent you may need to change some of the logic:

Range("A351").Select
FirstRow = 351
X = 1 ' note, this line is not needed
With Worksheets(UserEntry)
Range("A2").Copy Destination:=.Cells(FirstRow, 1)
For X = 1 To 23
.Cells(FirstRow, X).Replace What:=("XXXX"), _
Replacement:=.Range("A2").Value, _
LookAt:=xlPart, _
MatchCase:=False

FirstRow = FirstRow + 1
Next X
End With

Thank you for your reply. I have a clearer picture between cells and ranges. I used a bad example. For clarification, lets say cell A351 has a text value of "Z-score". Cell A351 also has comments that were inserted using the right mouse button option. Also using the right mouse button for cell A351 was inserted a hyperlink. The value of the hyperlink is currently http://www.gurufocus.com/term/zscore/XXXX/Gilead+Sciences%. In cell A2 is a value of ED. I am trying to replace the XXXX in the hyperlink to read http://www.gurufocus.com/term/zscore/ED/Gilead+Siences%.

From your coding you have given me do how does it know to change the value in the hyperlink portion of the cell?

 

[knigget40]

Try the function below - call it with something like this: Call changeHyperlink(Range("A351"), "XXXX", "EAD")

Public Function changeHyperlink(inRange As Range, inReplaceWhat As String, inReplaceWith As String) As Boolean
Dim hyperlinkValue As String
Dim newHyperlinkValue As String

hyperlinkValue = inRange.Hyperlinks(1).Address
newHyperlinkValue = Replace(hyperlinkValue, inReplaceWhat, inReplaceWith, , , vbTextCompare)

inRange.Hyperlinks(1).Address = newHyperlinkValue
End Function

 

[knigget40]

Sorry, that did not need to be a Function. I was going to return the string but decided against it. I can instead be a Sub as follows:

Public Sub changeHyperlink(inRange As Range, inReplaceWhat As String, inReplaceWith As String)
Dim hyperlinkValue As String
Dim newHyperlinkValue As String

hyperlinkValue = inRange.Hyperlinks(1).Address
newHyperlinkValue = Replace(hyperlinkValue, inReplaceWhat, inReplaceWith, , , vbTextCompare)

inRange.Hyperlinks(1).Address = newHyperlinkValue
End Sub

 

[knigget40]

Add if for some reason you need to loop through all hyperlinks on the worksheet:

Public Sub loopThroughAllHyperlinks()
Dim ws As Worksheet
Dim aLink
Set ws = ActiveSheet

For Each aLink In ws.Hyperlinks
Debug.Print aLink.Address
' Add you code here to act on the addresses, such as: Call changeHyperlink(Range("A351"), "XXXX", "EAD")
Next aLink
End Sub