I would like to take a 16-bit integer value, convert it to binary and then
break the individual 1s and 0s into seperate columns.
For example, a value of 5 in column A to break out into columns C,D,E,F as
0,1,0,1 respectivly.
I need to be able to use the individual 1s and 0s later in the sheet.
Your example shows 0101 as the value of 5... technically, that is correct,
but it is *not* a 16-bit binary value (it's a 4-bit binary value)... the
16-bit binary values would be 0000000000000101. So you will need to clarify
what exactly you want returned. In anticipation that your 16-bit statement
was correct (and 0101 was due to hurried typing), you can use the VBA macro
(which relies on the accompanying function) after my signature to populate
the cells you indicated you wanted filled in. Simply change the values being
assigned in the Const statement (if they end up being different from what
posted) as necessary (the WorksheetName constant definitely needs to be
looked at to see if you need to).
If you are not familiar with macros and functions, you would implement my
code by going into the VBA editor (press Alt+F11 from any worksheet), click
Insert/Module from the editor's menu bar, and copy paste all the code after
my signature into the code window that opened up. Now, go back to the
worksheet you want to have this functionality on (Sheet1 as set in my code),
fill in some integer values in column A (starting at Row 2 as set in my
code), press Alt+F8 and select Distribute1sAnd0s from the list, and click
the Run button.
Rick
Sub Distribute1sAnd0s()
Const DataStartRow As Long = 2
Const DataStartCol As String = "A"
Const BinStartColumn As String = "C"
Const WorkSheetName As String = "Sheet1"
Dim X As Long
Dim Z As Long
Dim LastRow As Long
Dim BinValue As String
With Worksheets(WorkSheetName)
LastRow = .Cells(Rows.Count, DataStartCol).End(xlUp).Row
For X = DataStartRow To LastRow
BinValue = Dec2Bin(.Cells(X, DataStartCol).Value, 16)
For Z = 1 To Len(BinValue)
.Cells(X, DataStartCol).Offset(0, Z + 1).Value = Mid(BinValue, Z, 1)
Next
Next
End With
End Sub
Function Dec2Bin(ByVal DecimalIn As Variant, _
Optional NumberOfBits As Variant) _
As String
Dec2Bin = ""
DecimalIn = CDec(DecimalIn)
Do While DecimalIn <> 0
Dec2Bin = Trim$(Str$(DecimalIn - 2 * Int(DecimalIn / 2))) & Dec2Bin
DecimalIn = Int(DecimalIn / 2)
Loop
If Not IsMissing(NumberOfBits) Then
If Len(Dec2Bin) > NumberOfBits Then
Dec2Bin = "Error - Number too large for bit size"
Else
Dec2Bin = Right$(String$(NumberOfBits, "0") & _
Dec2Bin, NumberOfBits)
End If
End If
End Function
