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

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

Search range add value
 

Thanks Tom!!! Once again you amaze me with your skills!!!

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

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