[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 659
  • Last Modified:

Excel macro to select the first value in cell in a specific column

Hello,

I'm attempting to create a VBA macro in Excel that looks at a specific column (column D) which contains values of "male" and "female".  It then needs to convert those to M and F.  I've created the following macro that does this.  However in it's current construction the user must select the values in the column.  I would like to set the macro to run for a specific column D and change the values there.  Please help.

Thanks

Sub ConvertGender()
Dim c As Range
For Each c In Selection.Cells
    c.Value = UCase(Left(c.Value, 1))
    Next
End Sub
0
wsom-itg
Asked:
wsom-itg
  • 3
  • 2
2 Solutions
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
start with:

Columns(4).select

Open in new window


after Dim c As Range

That will select column D, which I think is all that you are missing.
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Alternatively, you can replace your code with:

    Columns(4).Replace What:="female", Replacement:="F", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Columns(4).Replace What:="male", Replacement:="M", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Open in new window


This will avoid "wrong" cells like "Mole" being replaced with "M".
0
 
wsom-itgAuthor Commented:
Thanks Phillip,

I guess I should of added that it needs to have functionality to make it memory conservative, so that doesn't check the entire million+ rows of excel but only the ones with values in them
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Rob HensonFinance AnalystCommented:
Rather than looping each cell at a time why not do a Find & Replace?

If you do the Female to F first, you won't end up converting incorrectly or you could enable the look at whole cell option.

However selecting whole column may take longer to process. It might be worth specifying the range to speed things up:
Range("D1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Replace What:="male", Replacement:="M", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="female", Replacement:="F", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("A1").Select

Open in new window

Thanks
Rob H
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
To be memory conservative, instead of using Columns(4), use Columns(4).SpecialCells(xlCellTypeConstants)
0
 
wsom-itgAuthor Commented:
Thanks alot for the help.  Both solutions work.  I really like the Rob's find and replace
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now