hardroader
New Member
- Joined
- Aug 1, 2013
- Messages
- 37
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
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