Convert to Binary

Hi - I am trying convert a table to binary. I have the following code, but the only issue is that it replaces nulls with 0 if the nulls are after a 0 in the same row. I would like all nulls to stay nulls. I attached a spreadsheet as an explanation. The "Example" should change into what is shown in the "Example Output" tab. Does this make sense? Any help would be much appreciated. I think it should just be a minor tweak to this code. Thanks!

Please Login or Register  to view this content.

You have blank rows, and the current region stopped at row 10 and did not pick the rows after the blank.

Please Login or Register  to view this content.

Thanks, AB33! I'm still a bit confused because the code still replaces nulls with 0 if the nulls are after a 0 in the same row. Am I missing something?

Thank you!

Well, I have only checked the output and seems to the same as you had expected.

Can you send me the total code with your code included? Maybe I'm making some dumb mistake. When I run this code, I7 through K7 populate with 0, even though they are supposed to stay null. Thank you so much for all your help!

This is true on

x(i, iii) = "" And InStr(1, x(2, iii), "_") > 0

when iii is 9 and
then
If x(i, ii) = x(1, iii) is false as x(I,ii) is 0 and x(1, iii) is 2- both conditions have to be true, if not, it returns 0
I would not be able to reply until I get an internet connection.

Ah that makes sense. Thank you for explaining. I'm not totally sure what the final code should be then to solve for this? Sorry I'm still trying to learn vba.

I do not understand the algorithm.
Look at rows 6 and 8 and show me what is the difference and how do you get the result?

Yeah, I don't think this will work. Do you have any recommendations/ideas how to solve this? Thanks AB33

Hi Norie, what I am trying to do is convert existing columns into binary or null. So, in the attached file in my first post, there are columns with numbers and blank columns to the right of them. The number of blank columns are equal to the max value of the column before it. I want to populate the blank columns with nulls, 0, or 1 based on the previous column with numbers. So for example, in the attachment, column A has a max value of 3, so there are currently 3 blank columns next to it. If a cell in column A is null (A4), then B4-D4 should remain null. If a cell in column A is 1 (A3), then cells B3 should change to 1 and C3-D3 should be 0. If a cell in column A is 2 (A10), then cell C10 should be 1 and B2 and D2 should be 0. And so on... The code should do this to the entire table (btw the table in the attached is just an example - the numbers/columns/rows will all change, so the code should not stop after column J or Row 13). Does this make sense? Can you see my attachment?

I know that was a long winded answer, but I hope it makes it more clear!

Thanks