IF Function vba

Question

I need a macro that can do this: If column AB5 has a blank cell, then then column A5 will take the value from column U5. if column AB5 is not a blank cell (contains text), then column A5 will take the value from AC5. This will run for all the data at column AB

I tried to use IF function, =IF(ISBLANK(AB5), U5, AC5) but it will reflect the wrong data if the cell contains text where they will still take U5 value.

Ideally is if it can be done using macro, but IF function is also fine too!

Please help thanks!

yup! When the cell contain text, it will still take the value from U5. It will only reflect the correct value for those with blank cell. — Liyun
– Liyun, Commented Nov 29, 2016 at 7:22
when you have a vlue in AB5 you are not getting anything in A5 ? do you have anything inside cell AC5 ? — Shai Rado
– Shai Rado, Commented Nov 29, 2016 at 7:25
@Liyun When the cell contains text the formula will return value from AC5 not from U5 — Harun24hr
– Harun24hr, Commented Nov 29, 2016 at 7:28
yes I have values inside AC5, but even if there is value in AB5, it will still reflect the value from U5. @ShaiRado — Liyun
– Liyun, Commented Nov 29, 2016 at 7:30

Glitch_Doctor · Accepted Answer · 2016-11-29 08:38:14Z

1

I'd guess that your cells are not truly blank. My guess would be that AB5 has formula in it. Try:

=IF(LEN(AB5)=0,U5,AC5)

Edit

If it has a single space in there rather than being empty:

=IF(LEFT(AB5,1)=" ",U5,AC5)

or

=IF(LEN(AB5)<2,U5,AC5)

Perhaps try reversing the formula:

=IF(LEN(AB5)<>0,AC5,U5)
=IF(AB5<>"",AC5,U5)

As they are all 3 letter acronyms try this one:

=IF(LEN(AB5)>2,AC5,U5)

A foolproof way to be sure that there are no spaces as input errors:

=IF(LEN(SUBSTITUTE(AB5," ",""))=3,AC5,U5)

Let me know if any of these work, I can mock up a VBA function for you if they don;t but they should work.

edited Nov 29, 2016 at 8:38

answered Nov 29, 2016 at 7:33

Glitch_Doctor

3,0443 gold badges19 silver badges31 bronze badges

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

8 Comments

Shai Rado Over a year ago

you forgot the little ) and reference of Len to AB5 not A5

Liyun Over a year ago

Hi its still the same, it will still take the value from U5.

Glitch_Doctor Over a year ago

@Liyun What exactly is in cell AB5? Is there a formula, what values is it putting out? Is it entirely blank? Does it have a single space there?

Liyun Over a year ago

Nope its just a text there showing PLT, with no formula. @Glitch_Doctor

Glitch_Doctor Over a year ago

@Liyun Another edit made, try the substitute formula to get rid of spaces and check whether what is left is 3 digits long

|

Tom Malkin · Accepted Answer · 2016-11-29 07:25:22Z

0

Your function

=IF(ISBLANK(AB5), U5, AC5)

should work fine. If you drag down the formula in the A Column, the numbers in the formula should change:

=IF(ISBLANK(AB6), U6, AC6)

etc. Is this happening? What is your formula in cell A6?

answered Nov 29, 2016 at 7:25

Tom Malkin

2,3242 gold badges20 silver badges36 bronze badges

1 Comment

Liyun Over a year ago

yes it will be like the one you stated when I drag it down. But when I check those cell with text, eg. cell A95, the value will be 0 which is the value from U95

mojo3340 · Accepted Answer · 2016-11-29 09:00:14Z

0

=IF(AB5="", U5, AC5)

try that, should work

answered Nov 29, 2016 at 9:00

mojo3340

5691 gold badge6 silver badges27 bronze badges

Collectives™ on Stack Overflow

IF Function vba

3 Answers 3

8 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related