Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search range add value
I am sure this question has been asked in some form or another... searched the forums and could not put it together. Here is what I need to do: I have a column with numbers, 0-6 A -- 2 3 6 1 0 4 4 5 What I need to do is search this column and based on the number in th column add the bit oriented value (e.g. 4 = 001000) in the column t the right. I need to end up with this: A | B -------------- 2 | 000010 3 | 000100 6 | 100000 1 | 000001 0 | 000000 4 | 001000 4 | 001000 5 | 010000 Note that the resulting column needs to be text. I can do this real quick and easy with a formula, but I wanted to do i with VBA. Thanks!!! Fre -- Fred ----------------------------------------------------------------------- Fredg's Profile: http://www.excelforum.com/member.php...fo&userid=2632 View this thread: http://www.excelforum.com/showthread.php?threadid=39920 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search range add value
Sub EEE()
For Each cell In Selection If cell.Value = 0 Then cell.Offset(0, 1) = "'000000" Else sStr = "'000000" Mid(sStr, 8 - cell.Value, 1) = "1" cell.Offset(0, 1).Value = sStr End If Next End Sub worked for me. -- Regards, Tom Ogilvy "Fredg" wrote in message ... I am sure this question has been asked in some form or another... I searched the forums and could not put it together. Here is what I need to do: I have a column with numbers, 0-6 A -- 2 3 6 1 0 4 4 5 What I need to do is search this column and based on the number in the column add the bit oriented value (e.g. 4 = 001000) in the column to the right. I need to end up with this: A | B -------------- 2 | 000010 3 | 000100 6 | 100000 1 | 000001 0 | 000000 4 | 001000 4 | 001000 5 | 010000 Note that the resulting column needs to be text. I can do this real quick and easy with a formula, but I wanted to do it with VBA. Thanks!!! Fred -- Fredg ------------------------------------------------------------------------ Fredg's Profile: http://www.excelforum.com/member.php...o&userid=26328 View this thread: http://www.excelforum.com/showthread...hreadid=399205 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search range add value
Thanks Tom!!! Once again you amaze me with your skills!!!
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search range add value
Fredg wrote:
I am sure this question has been asked in some form or another... I searched the forums and could not put it together. Here is what I need to do: I have a column with numbers, 0-6 A -- 2 3 6 1 0 4 4 5 What I need to do is search this column and based on the number in the column add the bit oriented value (e.g. 4 = 001000) in the column to the right. I need to end up with this: A | B -------------- 2 | 000010 3 | 000100 6 | 100000 1 | 000001 0 | 000000 4 | 001000 4 | 001000 5 | 010000 Note that the resulting column needs to be text. I can do this real quick and easy with a formula, but I wanted to do it with VBA. Thanks!!! Fred -- Toms sugestion was awesome... but I have text mixed in with some cells. Actually this is what I had in mind... and it works perfect: Sub convert_bit() For Each cell In Selection Select Case (cell.Value) Case 0: cell.Offset(0, 1) = "000000" Case 1: cell.Offset(0, 1) = "000001" Case 2: cell.Offset(0, 1) = "000010" Case 3: cell.Offset(0, 1) = "000100" Case 4: cell.Offset(0, 1) = "001000" Case 5: cell.Offset(0, 1) = "010000" Case 6: cell.Offset(0, 1) = "100000" End Select Next End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search range add value
Sub EEE()
For Each cell In Selection if isnumeric(cell) then If cell.Value = 0 Then cell.Offset(0, 1) = "'000000" Else sStr = "'000000" Mid(sStr, 8 - cell.Value, 1) = "1" cell.Offset(0, 1).Value = sStr End If End if Next End Sub -- Regards, Tom Ogilvy "Fred" wrote in message oups.com... Fredg wrote: I am sure this question has been asked in some form or another... I searched the forums and could not put it together. Here is what I need to do: I have a column with numbers, 0-6 A -- 2 3 6 1 0 4 4 5 What I need to do is search this column and based on the number in the column add the bit oriented value (e.g. 4 = 001000) in the column to the right. I need to end up with this: A | B -------------- 2 | 000010 3 | 000100 6 | 100000 1 | 000001 0 | 000000 4 | 001000 4 | 001000 5 | 010000 Note that the resulting column needs to be text. I can do this real quick and easy with a formula, but I wanted to do it with VBA. Thanks!!! Fred -- Toms sugestion was awesome... but I have text mixed in with some cells. Actually this is what I had in mind... and it works perfect: Sub convert_bit() For Each cell In Selection Select Case (cell.Value) Case 0: cell.Offset(0, 1) = "000000" Case 1: cell.Offset(0, 1) = "000001" Case 2: cell.Offset(0, 1) = "000010" Case 3: cell.Offset(0, 1) = "000100" Case 4: cell.Offset(0, 1) = "001000" Case 5: cell.Offset(0, 1) = "010000" Case 6: cell.Offset(0, 1) = "100000" End Select Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search range | Excel Worksheet Functions | |||
Search range for text not in another range | Excel Discussion (Misc queries) | |||
How to search a range for a suburb in a large range | Excel Programming | |||
Search for #'s in range | Excel Programming | |||
Range Search | Excel Programming |