Hello all! I have a lot of column re-ordering to do on multiple sheets, and rather than repeat code, I thought I'd have a try at a UDF, to select columns based on a string , and then allow me to select that range.
Having trawled old posts etc, I found a sub I wanted to modify into a function, however it's returning the value of the header cell, instead of the range.
The ideal output of this would be to return Range("A:A") - if the 'TeamIronman' string was on A1.
FindAddressColumn("A1:Z1","TeamIronman")
Code
Function FindAddressColumn(FindAddress As Range, FindString As String) As Range
Dim RngAddress As Range
Set RngAddress = FindAddress.Find(FindString)
If RngAddress Is Nothing Then
'MsgBox "Address column was not found."
Set FindAddressColumn = Range("A1")
Exit Function
End If
Set FindAddressColumn = RngAddress
'FindAddressColumn = Columns(1, RngAddress.Column)
End Function
Display More
The commented part is the bit that failed. Any pointers would be most welcome!