CODEWITHSUNDEEP
vbaexcel
Øyvind
Øyvind

Reputation: 979

get the column index of a range

In the following subcode, I want to restrict what it does (replacing substring in hyperlinks) to a specific column. I have writen in '* what my idea is for a quick fix. But I can't seem to find a good way to get the column value of a cell saved as a Range variable.

Dim MyDoc As Worksheet
Dim MyCell As Range
    ...
        For Each MyCell In MyDoc.UsedRange
            If MyCell.Hyperlinks.Count > 0 Then
               '* if mycell's columnnumber = 1 then
                    LinkURL = MyCell(1).Hyperlinks(1).Address
                    FindPos = InStr(1, LinkURL, FindString)
                    If FindPos > 0 Then 'If FindString is found
                        ReplaceLen = Len(FindString)
                        URLLen = Len(LinkURL)
                        PreStr = Mid(LinkURL, 1, FindPos - 1)
                        PostStr = Mid(LinkURL, FindPos + ReplaceLen, URLLen)
                        NewURL = PreStr & ReplaceString & PostStr
                        MyCell(1).Hyperlinks(1).Address = NewURL 'Change the URL
                    End If
               '* End if
            End If
         Next MyCell

Upvotes: 9

Views: 51174

Answers (1)

assylias
assylias

Reputation: 328855

You can simply call the Column property:

If MyCell.Column = 1 Then ...

This is the absolute column (column A of the spreadsheet), not the first column of the range.

If you want to check if it is the first column of the range, you can first calculate it:

firstCol = yourRange.Cells(1, 1).Column
If MyCell.Column = firstCol Then ...

Upvotes: 16

Related Questions