Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 638
  • 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 BurtonCommented:
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 BurtonCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Rob HensonIT & Database AssistantCommented:
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 BurtonCommented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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