Hello all,
Scenario: I have two columns, A & B both filled with values. In column B, there are many cells with "NULL" as their value. I want to write a macro that looks for all instances of "NULL" in the entire range of column B, and replaces them with the value of the cell in the same row as Column A. Thus:
A B
1 NULL
2 Hello
3 NULL
would become
A B
1 1
2 Hello
3 3
Is there any easy way to do this without a for loop, perhaps a built-in vba function that can do this? Thanks everyone.
Last edited by HughManatee; 09-13-2013 at 04:13 PM.
Formula will do
Please Login or Register to view this content.
Thanks for the response, but I'm not clear as to how to implement that in VBA. Keep in mind, I'm trying to do this over an entire range in a column (for ~20000 cells). I want something quicker than a for loop. Could you elaborate a bit?
IMO, you can do it with out a loop if the replacement of a cell does not come from another cell, but you are not.
Mine is just a simple formula like substitute, but it will not work on column A. You have to do the formula in different column, say in column C and then copy values only back in to column A.
You can also probably sort the data by column B and all NULL values will be at the top, then copy manually column A in to B It may not be ****, but will avoid looping.
You can avoid the loop if you use EVALUATE()
Please Login or Register to view this content.
Gary's Student
Jak,
Brilliant idea!
Jut anoter approach..
Select Column B:B
Find what : NULL
Replace with : =indirect("A" & row())
give a try.. "copy n paste as value" if required..
Regards!
=DEC2HEX(3563)
If you like someone's answer, click the star to give them a reputation point for that answer...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks