CODEWITHSUNDEEP
vbaexcelif-statement
J.Khoo
J.Khoo

Reputation: 33

If function giving me problems

My value keeps becoming false instead of the values i've added even when i enter Male. anyone has any idea?

If gender = "Male" Then
Sheet2.Range("A2").Value = "he" & Sheet2.Range("A3").Value = "him" & Sheet2.Range("A4").Value = "his"
Else
Sheet2.Range("A2").Value = "she" & Sheet2.Range("A3").Value = "her" & Sheet2.Range("A4").Value = "her"
End If

Upvotes: 0

Views: 51

Answers (2)

user3598756
user3598756

Reputation: 29421

Rich Holton gave you the reason why your code didn't work

Here I propose you some different techniques

Option Explicit

Sub main()
    Dim valsArray As Variant '<--| declare a Variant variable to hold an array
    Dim gender As String

    gender = "Male" '<--| gender initialization for testing purposes

    If gender = "Male" Then
        valsArray = Array("he", "him", "his") '<--| fill your Variant with values corresponding to the 'gende'r value
    Else
        valsArray = Array("she", "her", "her") '<--| fill your Variant with values corresponding to the 'gende'r value
    End If
    Sheet2.Range("A2:A4").Value = Application.Transpose(valsArray) '<--| write all values in one shot (you need 'Transpose()' to change the "horizontal" Variant array to a "vertical" one and fit the range you're fill values of        
End Sub

you can also shorten it down even more by using IIf() function:

Option Explicit

Sub main()
    Dim gender As String

    gender = "Male" '<--| gender initialization for testing purposes

    Sheet2.Range("A2:A4").Value = Application.Transpose(IIf(gender = "Male", Array("he", "him", "his"), Array("she", "her", "her")))        
End Sub

Upvotes: 1

Rich Holton
Rich Holton

Reputation: 682

You can't assign a value two time in the same statement. Any = character after the first is treated as a logical operator (is equal to), not as an assignment.

So if you have:

a = b = c

VBA treats the statement as: Assign to A the results of "Is b equal to c". It gets more complicated if you have three equal signs in a statement, but it won't be what you want in any rate.

Additionally, the & operator is for string concatenation. so

a = "top" & "hat"

will assign the string "tophat" to a.

You might want to use the : operator instead of &. That separates statements on a single line. So try this:

If gender = "Male" Then
Sheet2.Range("A2").Value = "he" : Sheet2.Range("A3").Value = "him" : Sheet2.Range("A4").Value = "his"
Else
Sheet2.Range("A2").Value = "she" : Sheet2.Range("A3").Value = "her" : Sheet2.Range("A4").Value = "her"
End If

However, I would urge you not to use : here. There's really no need, and I think it makes the code harder to read. I'd suggest:

If gender = "Male" Then
    Sheet2.Range("A2").Value = "he"  
    Sheet2.Range("A3").Value = "him"
    Sheet2.Range("A4").Value = "his"
Else
    Sheet2.Range("A2").Value = "she"
    Sheet2.Range("A3").Value = "her"  
    Sheet2.Range("A4").Value = "her"
End If

Upvotes: 4

Related Questions