Select Case Issue - Change Gender after finding the gender column

[mmetzinger]

Ok, this should be super easy but for the life of me I can't figure it out. I need some code that will find the column with the header of "Gender" and change the values from "M" and "F" to "Male" and "Female" but I keep running into issues. Can someone give me some super easy code to do this?

I want to use a Select Case statement so I can set all other found values as blanks.

Thanks!

 

[cyrilbrd]

Hi,
Not sure if this wouldn't be faster, why don't you use Find/replace? Select only the Column of your choice replace all M with Male and F with Female should do the trick. No?

 

[mmetzinger]

No, the find & replace method won't work because I need to have all values that are not M or F removed from the list. A lot of the time data such as U or Unknown will get entered which doesn't fit in our CRM system.

 

[mmetzinger]

Figured it out! Apparently I just needed some sleep.

Dim GenderRange As String

Range("A1").Select
Cells.Find(What:="Gender", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

Range(Selection, Selection.End(xlDown)).Select
GenderRange = Selection.Address

Dim oneCell1 As Range

For Each oneCell1 In Range(GenderRange)
Select Case oneCell1.Value
Case "M"
oneCell1.Value = "Male"
Case "F"
oneCell1.Value = "Female"
Case "Male"
oneCell1.Value = "Male"
Case "Female"
oneCell1.Value = "Female"
Case "Gender"
oneCell1.Value = "Gender"
Case Else
oneCell1.Value = ""
End Select
Next oneCell1

 

[cyrilbrd]

Cool, thanks for posting your solution.

 

[lancerj017]

Dim GenderRange As String

    Range("A1").Select
    Cells.Find(What:="Gender", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    
        Range(Selection, Selection.End(xlDown)).Select
       GenderRange = Selection.Address
      
Dim oneCell1 As Range

For Each oneCell1 In Range(GenderRange)
    Select Case oneCell1.Value
                Case "M"
                oneCell1.Value = "Male"
                Case "F"
                oneCell1.Value = "Female"
                Case "Male"
                oneCell1.Value = "Male"
                Case "Female"
                oneCell1.Value = "Female"
                Case "Gender"
                oneCell1.Value = "Gender"
            Case Else
                oneCell1.Value = ""
    End Select
Next oneCell1

Try:

Sub Test()
Dim LastRow As Long
Dim i As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To LastRow
    Select Case Cells(i, 1).Value
    Case "M"
        Cells(i, 1).Value = "Male"
    Case "F"
        Cells(i, 1).Value = "Female"
    Case "Male"
        Cells(i, 1).Value = "Male"
    Case "Female"
        Cells(i, 1).Value = "Female"
    Case Else
        Cells(i, 1).Value = ""
    End Select
Next i
End Sub