IF Function vba

2019-09-07 12:23发布

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!

3条回答
放荡不羁爱自由
2楼-- · 2019-09-07 12:37

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.

查看更多
Bombasti
3楼-- · 2019-09-07 12:44

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?

查看更多
神经病院院长
4楼-- · 2019-09-07 12:46
=IF(AB5="", U5, AC5)

try that, should work

查看更多
登录 后发表回答