Function to find text in header - and return column range for selection?

JanMantins · May 25th 2016

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!

cytop · May 25th 2016

Re: Function to find text in header - and return column range for selection?

The actual return value you want is not clear and I'm not sure if you mean to use this as a UDF on a worksheet or calling in VBA code but it can be used either way.

It takes 3 parameters:

The range to search
The String to search for
What to return

An example cell formula:

[bfn]=FindAddressColumn(A1:Z1,"TeamIronman", 0)[/bfn]

The 3rd parameter can be 0, 1 or 2. If omitted, or is anything else, then 0 is assumed
0 Returns the Entire Column Address ($H:$H)
1 Returns the cell address where found ($H)
2 Returns the Column number (8)

If the string is not found, the return is blank.

Code

Function FindAddressColumn(FindAddress As Range, FindString As String, Optional ReturnType As Integer = 0) As Variant


   Dim RngAddress       As Range
   Set RngAddress = FindAddress.Find(What:=FindString, Lookat:=xlWhole, MatchCase:=False)


   If RngAddress Is Nothing Then
      'MsgBox "Address column was not found."
      FindAddressColumn = ""
      Exit Function
   End If


   Select Case ReturnType
      Case 0
         FindAddressColumn = RngAddress.EntireColumn.Address
      Case 1
         FindAddressColumn = RngAddress.Address
      Case Else
         FindAddressColumn = RngAddress.Column
   End Select


End Function

Display More

JanMantins · May 25th 2016

Re: Function to find text in header - and return column range for selection?

That's marvellous , thankyou! The think that was throwing me was how to get out the entire column output as a reference! You're a life saver!

The plan is to call it from within a VBA subroutine - I've several sheets that feature specific columns, but often are saved in different ordering - so need to be re-ordered before pulling into a larger sheet.

Function to find text in header - and return column range for selection?

Participate now!

Share