Is there a simple way to loop over all used cells in a workbook in VBA?-CodePudding

I'm incredibly new to VBA, and drafted out a bit of code to replace some portions of hyperlinks. It works great, but now I can't seem to figure out how to make it run over the whole workbook. Here's what I've got:

    For Each cell In Range("C13")
        If cell.Hyperlinks.Count > 0 Then
            If InStr(cell.Hyperlinks(1).Address, original) <> 0 Then
                temp = final & Mid(cell.Hyperlinks(1).Address, Len(original)   1)
                cell.Hyperlinks(1).Address = temp
            End If
        End If
    Next cell
End Sub

Right now I just used range("C13") as a test, but ideally it would say something like application.workbooks(1) (but of course that doesn't work). Any ideas? Thanks!

CodePudding user response：

Try:

For Each wb In Application.Workbooks
    For Each ws In wb.Worksheets
        For Each cell In ws.UsedRange

            (your code)

        Next cell
    Next ws
Next wb

Working for me using cell.Value = cell.Value 1 with multiple workbooks/sheets as a simple test.

CodePudding user response：

It seems you are after code to look at the hyperlinks in a workbook

Dim hLink As Hyperlink
Dim iSh As Worksheet

For Each iSh In ThisWorkbook.Worksheets
    For Each hLink In iSh.Hyperlinks

        ' Your code adapted a little bit
        If InStr(hLink.Address, original) <> 0 Then
            temp = Final & Mid(hLink.Address, Len(original)   1)
            hLink.Address = temp
        End If
        ' End of your code       


    Next
Next