Get range column index

Question

In the next subcode, I want to limit what it does (replacing a substring in hyperlinks) with a specific column. I wrote in * * that my idea is for a quick fix. But I cannot find a good way to get the value of a cell column 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 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 

Answer 1

You can simply call the Column property:

 If MyCell.Column = 1 Then ... 

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

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

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