Yet another Excel VBA 404 error

Question

I want this script to check the cells on column A if there is a URL-link in them, and if it is true then perform some cut-paste operations.

String #5 returns error 404, please help to solve this!

Sub xxxxxx()
    Worksheets("1 (2)").Activate
    For i = 1 To 2200
        Range("A" & i).Select
        If (cell.Range("A1").Hyperlinks.Count >= 1) Then
            ActiveCell.Offset(1, 0).Range("A1").Select
            Selection.Cut
            ActiveCell.Offset(-1, 2).Range("A1").Select
            ActiveSheet.Paste
        End If
    Next i
End Sub

You haven't defined the sheets like `Dim X as Worksheet: set X = Sheets(1)` thus you should write `sheets()` **and not `worksheets()`**. Hope this quick fixes your problem. It's a best practice to always define the sheets and ranges before use, and discard methods that imply **.Select(ing)** — Takedasama, Nov 26 '13 at 21:21
`Activate/Select` ..[OUCH](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — Siddharth Rout, Nov 26 '13 at 21:23

score 0 · Accepted Answer · edited May 23 '17 at 12:05

0

Per @Siddharth Rout post about not using Activate/Select, I've rewritten your code below. No need to check hyperlinks inside the loop every time since it's always checking cell A1

Sub xxxxxx()
    Dim ws As Worksheet
    Set ws = Worksheets("1 (2)")

    Dim LastRow As Long
    LastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

    If (ws.Range("A1").Hyperlinks.Count > 0) Then
        For i = 2 To LastRow
            Range("A" & i).Offset(-1, 2).Value = Range("A" & i).Value
            Range("A" & i).Clear
        Next i
    End If
End Sub

edited May 23 '17 at 12:05

Community

1
1

answered Nov 26 '13 at 21:21

Automate This

30,726
11
60
82

Updated to not use `Activate` or `Select` – Automate This Nov 26 '13 at 22:31

Yet another Excel VBA 404 error

1 Answers1