Use excel functions on a variable

Question

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.

You are putting the value of Gender into your formula, as a cursory examination of the formula will tell you, which the sheet will not understand. You need to use the address property to return the cell reference. — SJR
– SJR, Commented Jan 3, 2018 at 12:28
From your use of set, i'm assuming you're doing this in VBA, why not just use an IF ELSE IF statement to set the value? — Michael Russo
– Michael Russo, Commented Jan 3, 2018 at 12:31
in the formula, change Gender to Gender.address and it should work fine. — Zerk
– Zerk, Commented Jan 3, 2018 at 12:32
Thanks SJR, I have done that by adding .Address after Gender but now it's returning a blank cell — jufg
– jufg, Commented Jan 3, 2018 at 12:40

SJR · Accepted Answer · 2018-01-03 14:05:04Z

1

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"",""""))"

answered Jan 3, 2018 at 14:05

SJR

23.1k6 gold badges21 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jufg Over a year ago

Thanks SJR, this worked perfectly. Thanks everyone else for input

Gary's Student · Accepted Answer · 2018-01-03 13:20:46Z

1

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

answered Jan 3, 2018 at 13:20

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Collectives™ on Stack Overflow

Use excel functions on a variable

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related