Create range for entire column from a cell

[scottbass]

Hi,

I have this code:

Modules --> Module1:

Function Find(strSearch As String) As Range
    Dim aCell As Range


    Set aCell = ActiveSheet.Rows(1).Find(What:=strSearch, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    
    If Not aCell Is Nothing Then
        Find = Range(Columns(aCell.Column), Columns(aCell.Column))
    End If
End Function

Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoops
    Application.EnableEvents = False
    
    If Not Intersect(Target, Find("Applicable")) Is Nothing Then
       If Not Target.HasFormula Then
          Target.Value = UCase(Target.Value)
       End If
    End If
Whoops:
    Application.EnableEvents = True
End Sub

My problem is in the Find = statement in the function.

I've walked this through the debugger. The Find function is finding the "Applicable" column fine (column 2). But how do I convert the aCell object to a range so that Intersect will be true, and will uppercase the cell value that was updated? If I were hardcoding this, I would return Range("B:B").

Thanks,
Scott

 

[pgc01]

Hi Scott

First a remark: it's not good practice to use names that are already used in vba, so instead of Find() that is used as a method in lots of objects I used FindColumn() that is what I understand you need

Try:

Function FindColumn(strSearch As String) As Range
    Dim aCell As Range


    Set aCell = ActiveSheet.Rows(1).Find(What:=strSearch, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    
    If Not aCell Is Nothing Then Set FindColumn = aCell.EntireColumn

End Function

 

[scottbass]

Hi PGC,


Thanks for that. I'd just copied code I found via Google, but obviously couldn't modify properly. Thanks for the tip and code correction.


Another issue: I'd like to keep my utility functions in a code module, so they can be called by multiple routines. So, I've changed my code as below, but am getting a compile error "Expected: =" from Worksheet_Change.


Module1:

Option Explicit
 
Function UserName()
    UserName = Environ$("UserName")
End Function


Function FindColumn(strSearch As String) As Range
    Dim aCell As Range


    Set aCell = ActiveSheet.Rows(1).Find(What:=strSearch, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    
    If Not aCell Is Nothing Then Set FindColumn = aCell.EntireColumn
End Function


Sub SelectionUCase()
    Dim Rng As Range
    For Each Rng In Selection.Cells
        If Rng.HasFormula = False Then
            'Use this line for UpperCase text; change UCase to LCase for LowerCase text.
            Rng.Value = UCase(Rng.Value)
        End If
    Next Rng
End Sub


Sub ChangeUCase(strColumn As String, Target As Range)
    If Not Intersect(Target, FindColumn(strColumn)) Is Nothing Then
       If Not Target.HasFormula Then
          Target.Value = UCase(Target.Value)
       End If
    End If
End Sub

Sheet1 (or any sheet where I want to process the Worksheet_Change event):

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoops
    Application.EnableEvents = False
   
    ' Code goes here
    ChangeUCase("Application",Target)
    
Whoops:
   Application.EnableEvents = True
End Sub

SelectionUCase is available for the end user to explicitly convert to upper case any selected text.


ChangeUCase is meant to be bound to the Worksheet_Change event. It searches for the "named column" (header row in column 1), then converts the entire column to upper case.

Finally, what scoping should I use for the helper functions/routines in Module1? Can Sheet1 code call Module1 code if that code is scoped Private? For example, I want the end user to see SelectionUCase when s/he hits Alt-F8, but not see ChangeUCase, but I want ChangeUCase callable from Worksheet_Change event processing.


Any help greatly appreciated.

(Sorry for all the posts, I'm working my way through "Excel 2007 Power Programming with VBA" by John Walkenbach (all 1000+ pages) now...)


Regards,
Scott