Search range add value

[Fredg]

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

[Tom Ogilvy]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Fred]

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

[Fred]

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

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>