Excel VBA if statement outputs only 0 value

Question

Here is my code that I am working with. It is really simple, but still not working as expected.

Dim hey As Range
Set hey = Range("A1:A5")

For Each i In hey

If i.Value = "15" Then
i.Offset(0, 2).Value = "15" And i.Offset(0, 3).Value = "15"

ElseIf i.Value = 10 Then
i.Offset(0, 2).Value = "10" And i.Offset(0, 3).Value = "10"

ElseIf i.Value = 5 Then
i.Offset(0, 2).Value = "5" And i.Offset(0, 3).Value = "5"

ElseIf i.Value > 15 And i.Value = "*5" Then
i.Offset(0, 2).Value = "10" And i.Offset(0, 3).Value = "5"

Else: i.Offset(0, 2).Value = 10 And i.Offset(0, 3).Value = 10
End If
Next i

Right now, all it will do is offset by 2 columns and output 0. I cannot distinguish why and I am at the end of my mental rope with this. I'm sure it is something retardedly simple I am overlooking.

I've tried stepping through and it looks like it evaluates properly for each value in my sample, but still gives me the 0 value. Also, I was playing around with the quotations to see if that had to do with it, but they are strings I want to work with.

Thanks for any help.

Take out your And statements outside of the if statements. Break each piece into two lines. — PartyHatPanda
– PartyHatPanda, Commented Aug 16, 2016 at 13:15
I'm no VBA expert but your final Else clause is missing apostrophes for the values — David Andrei Ned
– David Andrei Ned, Commented Aug 16, 2016 at 13:15
Ok, first it look like a nice place where to use Select Case instead of If. Second you are mixing numeric values ant text value, are your cells filled with numbers or text? Could you give a sample of inputs? Third, i.Offset(0, 2).Value = "15" And i.Offset(0, 3).Value = "15" means put in i.Offset(0, 2).Value the result of ` "15" And i.Offset(0, 3).Value = "15"`, that is probably not what you try to do. — Vincent G
– Vincent G, Commented Aug 16, 2016 at 13:18
This question needs clarification on whether the values in the cells are real numbers (e.g. 15) or text-that-look-like-a-number (e.g. "15"). They are not the same thing and boolean comparisons may not produce the correct results unless the proper comparison is made. — user4039065
– user4039065, Commented Aug 16, 2016 at 15:19

PartyHatPanda · Accepted Answer · 2016-08-16 13:37:46Z

6

This is what I mean by removing And Statements:

Dim hey As range
Set hey = range("A1:A5")

For Each i In hey

If i.Value = "15" Then
i.Offset(0, 2).Value = "15"
i.Offset(0, 3).Value = "15"

ElseIf i.Value = 10 Then
i.Offset(0, 2).Value = "10"
i.Offset(0, 3).Value = "10"

ElseIf i.Value = 5 Then
i.Offset(0, 2).Value = "5"
i.Offset(0, 3).Value = "5"

ElseIf i.Value > 15 And i.Value = "*5" Then
i.Offset(0, 2).Value = "10"
i.Offset(0, 3).Value = "5"

Else
i.Offset(0, 2).Value = 10
i.Offset(0, 3).Value = 10
End If
Next i

EDIT:

To a computer program, And is a comparison operator. It does not understand it the exact same way humans do. When discussing with another human, we can give a string of tasks linked together by 'and,' and there will be no confusion on what to do (unless the tasks themselves are confusing!). A computer uses And to check if multiple conditions are true at the same time. In your last Elseif you properly use this to tell the computer: "Check if i.Value > 15 AND Check if i.Value = "*5". If BOTH (because of the And statement) conditions are true, then run the following code."

Hopefully this helps explain the difference!

EDIT 2:

From @litelite 's suggestion, use the Like operator.

ElseIf i.Value > 15 And i.Value Like "*5" Then
i.Offset(0, 2).Value = "10"
i.Offset(0, 3).Value = "5"

edited Aug 16, 2016 at 13:37

answered Aug 16, 2016 at 13:21

PartyHatPanda

7248 silver badges14 bronze badges

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

7 Comments

litelite Over a year ago

I think it would be a good idea to explain to OP what and really means.

SsaSnakebite Over a year ago

Ah yes, I've done this before with the and statement. Logically to me it makes sense, of course, but to the code I remember why that doesn't work out. The code above worked just fine, except for the ElseIf i.Value > 15 And i.Value = "*5" Then line did not evaluate properly.

PartyHatPanda Over a year ago

@litelite Thanks for the suggestion, added it in!

PartyHatPanda Over a year ago

@SsaSnakebite What should that ElseIf do?

litelite Over a year ago

@SsaSnakebite You cannot use wildcard in equaliity. it will check if there is literraly a star. check out the like operator

|

Ross · Accepted Answer · 2016-08-16 13:39:41Z

1

Just to anwser off the back of @PartyHatPanda. You could use ElseIf i.Value > 15 And Right(i.Value, 1) = 5 Then. This Check if i.value's last digit is equal to 5. I this what you want?

edited Aug 16, 2016 at 13:39

answered Aug 16, 2016 at 13:38

Ross

2,4777 gold badges50 silver badges113 bronze badges

2 Comments

Ross Over a year ago

I cant comment sorry but I'm pretty sure that what OP is after

SsaSnakebite Over a year ago

I used the above suggestions of the Like operator and it works well enough for me!

Collectives™ on Stack Overflow

Excel VBA if statement outputs only 0 value

2 Answers 2

7 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related