CODEWITHSUNDEEP
excelvariablesvba
jufg
jufg

Reputation: 163

Use excel functions on a variable

I'm trying to get the right most letter from a variable I have set and then assign either Male or Female to the result. Below is a snippet of the code I've tried.

Set Gender = rFirst.Offset(1, 5)

Worksheets("E'ee Details").Range("E1").Value = "=if(right(" & Gender & ",1)=""M"",""Male"",if(right(" & Gender & ",1)=""F"",""Female"",""""))"

This is resulting in #NAME? being displayed.

Upvotes: 0

Views: 64

Answers (2)

SJR
SJR

Reputation: 23081

Amend as below

Worksheets("E'ee Details").Range("E1").Value = "=if(right(EmployeeAll!" & gender.Address & ",1)=""M"",""Male"",if(right(EmployeeAll!" & gender.Address & ",1)=""F"",""Female"",""""))"

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

Your approach involves creating a worksheet equation and then using Evaluate() to calculate it. Here is an alternative approach:

Sub jufg()
    Dim Gender As Range, sexCode As String
    Dim rFirst As Range
    Set rFirst = Range("A1")

    Set Gender = rFirst.Offset(1, 5)
    sexCode = Right(Gender.Value, 1)

    With Worksheets("E'ee Details").Range("E1")
        If sexCode = "M" Then
            .Value = "Male"
        ElseIf sexCode = "F" Then
            .Value = "Female"
        Else
            .Value = ""
        End If
    End With
End Sub

Upvotes: 1

Related Questions