Create range for entire column from a cell

scottbass

New Member
Joined
Sep 3, 2012
Messages
46
Hi,

I have this code:

Modules --> Module1:

Code:
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:

Code:
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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:

Code:
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

New Member
Joined
Sep 3, 2012
Messages
46
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:


Code:
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):


Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,553
Messages
5,636,982
Members
416,953
Latest member
broexc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top