How can i get past the overflow error?

[georgesmailuk]

i am writing a macro that looks at 306 cells in column A
each cell contains 1 or 0 (representing off and on)

I am trying to make the value increase each time i give a command -
remaining in the binary format)
I know this would be 2^306 combinations, but i promise this is not what i'm
attempting.

The code i have so far (is wrong), shows how i was trying to keep the 306
figures together as a string so i did not have any overflow errors. I
couldn't think of a way to move it up to the next level so was going to try
to multiply the string by 1 and add 1 then work through each character to try
to get 1 to 10 to 11 etc.
I admit the idea is hairbrained, but i'm stumped as to how i can get this to
work so was clutching at straws.

Any advice appreciated.

Sub test()
Dim x As String
Dim i As Range
Dim a As String

For Each i In Range("A1:A306")
p = i.Address
x = x & i.Value
Next i

1
For b = 1 To Len(x)
a = Right(Left(x, b), 1)
If a = 0 Or a = 1 Then
xbinary = True
Else
x = x + 1
GoTo 1
End If
Next b

z = x
'put data back in
For b = 1 To Len(x)
c = Right(Left(z, b), 1)
Range("A" & b).Value = c
Next b

End Sub

[Tom Ogilvy]

if you want to count the number of 1's and zeros in the string

numBinary = len(x) - len(Replace(Replace(x,"0",""),"1",""))

--
Regards,
Tom Ogilvy


"georgesmailuk" wrote:

> i am writing a macro that looks at 306 cells in column A
> each cell contains 1 or 0 (representing off and on)
>
> I am trying to make the value increase each time i give a command -
> remaining in the binary format)
> I know this would be 2^306 combinations, but i promise this is not what i'm
> attempting.
>
> The code i have so far (is wrong), shows how i was trying to keep the 306
> figures together as a string so i did not have any overflow errors. I
> couldn't think of a way to move it up to the next level so was going to try
> to multiply the string by 1 and add 1 then work through each character to try
> to get 1 to 10 to 11 etc.
> I admit the idea is hairbrained, but i'm stumped as to how i can get this to
> work so was clutching at straws.
>
> Any advice appreciated.
>
> Sub test()
> Dim x As String
> Dim i As Range
> Dim a As String
>
> For Each i In Range("A1:A306")
> p = i.Address
> x = x & i.Value
> Next i
>
> 1
> For b = 1 To Len(x)
> a = Right(Left(x, b), 1)
> If a = 0 Or a = 1 Then
> xbinary = True
> Else
> x = x + 1
> GoTo 1
> End If
> Next b
>
> z = x
> 'put data back in
> For b = 1 To Len(x)
> c = Right(Left(z, b), 1)
> Range("A" & b).Value = c
> Next b
>
> End Sub

[Lucas Swanson]

This is pretty simple with recursion.

I have made the assumption that the top cell is the highest "place" (would
be the rightmost digit if you were reading left-to-right). Also, I only used
10 cells, but you could easily change this to use as many as you wanted. I
added a button to the sheet to run the macro but you can run it any way you
want, just make sure to send the cell with the last digit.

Code:
Option Explicit

Private Sub CommandButton1_Click()

' Add one to the last cell
Call AddOne(Cells(10, 1))
End Sub

Private Sub CommandButton1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Call CommandButton1_Click
End Sub

Sub AddOne(rgTarget As Range)

With rgTarget
' Add one to the value of the current cell
.Value = .Value + 1

' If this makes the value more than one
If (.Value > 1) Then
' Reset the value to zero
.Value = 0

' Add one to the next cell
Call AddOne(.Offset(-1, 0))
End If
End With
End Sub

Hope this helps.

"georgesmailuk" wrote:

> i am writing a macro that looks at 306 cells in column A
> each cell contains 1 or 0 (representing off and on)
>
> I am trying to make the value increase each time i give a command -
> remaining in the binary format)
> I know this would be 2^306 combinations, but i promise this is not what i'm
> attempting.
>
> The code i have so far (is wrong), shows how i was trying to keep the 306
> figures together as a string so i did not have any overflow errors. I
> couldn't think of a way to move it up to the next level so was going to try
> to multiply the string by 1 and add 1 then work through each character to try
> to get 1 to 10 to 11 etc.
> I admit the idea is hairbrained, but i'm stumped as to how i can get this to
> work so was clutching at straws.
>
> Any advice appreciated.
>
> Sub test()
> Dim x As String
> Dim i As Range
> Dim a As String
>
> For Each i In Range("A1:A306")
> p = i.Address
> x = x & i.Value
> Next i
>
> 1
> For b = 1 To Len(x)
> a = Right(Left(x, b), 1)
> If a = 0 Or a = 1 Then
> xbinary = True
> Else
> x = x + 1
> GoTo 1
> End If
> Next b
>
> z = x
> 'put data back in
> For b = 1 To Len(x)
> c = Right(Left(z, b), 1)
> Range("A" & b).Value = c
> Next b
>
> End Sub

[georgesmailuk]

Tom - many thanks for that code - gave me another angle to atttack it from.
Worked on it for about half an hour before i got tied up again. lol

Lucas - wow
I've spent all day trying to work out how to do this and you can come up
with that?
Pure genius.

Many thanks - i can now lock some of the switches on and let the code run
for those remaining. Should be in the clear now - you've really made my day.

thanks again both
George

"Lucas Swanson" wrote:

> This is pretty simple with recursion.
>
> I have made the assumption that the top cell is the highest "place" (would
> be the rightmost digit if you were reading left-to-right). Also, I only used
> 10 cells, but you could easily change this to use as many as you wanted. I
> added a button to the sheet to run the macro but you can run it any way you
> want, just make sure to send the cell with the last digit.
>
> Code:
> Option Explicit
>
> Private Sub CommandButton1_Click()
>
> ' Add one to the last cell
> Call AddOne(Cells(10, 1))
> End Sub
>
> Private Sub CommandButton1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
>
> Call CommandButton1_Click
> End Sub
>
> Sub AddOne(rgTarget As Range)
>
> With rgTarget
> ' Add one to the value of the current cell
> .Value = .Value + 1
>
> ' If this makes the value more than one
> If (.Value > 1) Then
> ' Reset the value to zero
> .Value = 0
>
> ' Add one to the next cell
> Call AddOne(.Offset(-1, 0))
> End If
> End With
> End Sub
>
> Hope this helps.
>
> "georgesmailuk" wrote:
>
> > i am writing a macro that looks at 306 cells in column A
> > each cell contains 1 or 0 (representing off and on)
> >
> > I am trying to make the value increase each time i give a command -
> > remaining in the binary format)
> > I know this would be 2^306 combinations, but i promise this is not what i'm
> > attempting.
> >
> > The code i have so far (is wrong), shows how i was trying to keep the 306
> > figures together as a string so i did not have any overflow errors. I
> > couldn't think of a way to move it up to the next level so was going to try
> > to multiply the string by 1 and add 1 then work through each character to try
> > to get 1 to 10 to 11 etc.
> > I admit the idea is hairbrained, but i'm stumped as to how i can get this to
> > work so was clutching at straws.
> >
> > Any advice appreciated.
> >
> > Sub test()
> > Dim x As String
> > Dim i As Range
> > Dim a As String
> >
> > For Each i In Range("A1:A306")
> > p = i.Address
> > x = x & i.Value
> > Next i
> >
> > 1
> > For b = 1 To Len(x)
> > a = Right(Left(x, b), 1)
> > If a = 0 Or a = 1 Then
> > xbinary = True
> > Else
> > x = x + 1
> > GoTo 1
> > End If
> > Next b
> >
> > z = x
> > 'put data back in
> > For b = 1 To Len(x)
> > c = Right(Left(z, b), 1)
> > Range("A" & b).Value = c
> > Next b
> >
> > End Sub

[Lucas Swanson]

Haha, well thanks. I have always been a fan of recursion & am glad of any
opportunity to show off the power of using it. I am glad that I could help.

"georgesmailuk" wrote:

> Tom - many thanks for that code - gave me another angle to atttack it from.
> Worked on it for about half an hour before i got tied up again. lol
>
> Lucas - wow
> I've spent all day trying to work out how to do this and you can come up
> with that?
> Pure genius.
>
> Many thanks - i can now lock some of the switches on and let the code run
> for those remaining. Should be in the clear now - you've really made my day.
>
> thanks again both
> George
>
> "Lucas Swanson" wrote:
>
> > This is pretty simple with recursion.
> >
> > I have made the assumption that the top cell is the highest "place" (would
> > be the rightmost digit if you were reading left-to-right). Also, I only used
> > 10 cells, but you could easily change this to use as many as you wanted. I
> > added a button to the sheet to run the macro but you can run it any way you
> > want, just make sure to send the cell with the last digit.
> >
> > Code:
> > Option Explicit
> >
> > Private Sub CommandButton1_Click()
> >
> > ' Add one to the last cell
> > Call AddOne(Cells(10, 1))
> > End Sub
> >
> > Private Sub CommandButton1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
> >
> > Call CommandButton1_Click
> > End Sub
> >
> > Sub AddOne(rgTarget As Range)
> >
> > With rgTarget
> > ' Add one to the value of the current cell
> > .Value = .Value + 1
> >
> > ' If this makes the value more than one
> > If (.Value > 1) Then
> > ' Reset the value to zero
> > .Value = 0
> >
> > ' Add one to the next cell
> > Call AddOne(.Offset(-1, 0))
> > End If
> > End With
> > End Sub
> >
> > Hope this helps.
> >
> > "georgesmailuk" wrote:
> >
> > > i am writing a macro that looks at 306 cells in column A
> > > each cell contains 1 or 0 (representing off and on)
> > >
> > > I am trying to make the value increase each time i give a command -
> > > remaining in the binary format)
> > > I know this would be 2^306 combinations, but i promise this is not what i'm
> > > attempting.
> > >
> > > The code i have so far (is wrong), shows how i was trying to keep the 306
> > > figures together as a string so i did not have any overflow errors. I
> > > couldn't think of a way to move it up to the next level so was going to try
> > > to multiply the string by 1 and add 1 then work through each character to try
> > > to get 1 to 10 to 11 etc.
> > > I admit the idea is hairbrained, but i'm stumped as to how i can get this to
> > > work so was clutching at straws.
> > >
> > > Any advice appreciated.
> > >
> > > Sub test()
> > > Dim x As String
> > > Dim i As Range
> > > Dim a As String
> > >
> > > For Each i In Range("A1:A306")
> > > p = i.Address
> > > x = x & i.Value
> > > Next i
> > >
> > > 1
> > > For b = 1 To Len(x)
> > > a = Right(Left(x, b), 1)
> > > If a = 0 Or a = 1 Then
> > > xbinary = True
> > > Else
> > > x = x + 1
> > > GoTo 1
> > > End If
> > > Next b
> > >
> > > z = x
> > > 'put data back in
> > > For b = 1 To Len(x)
> > > c = Right(Left(z, b), 1)
> > > Range("A" & b).Value = c
> > > Next b
> > >
> > > End Sub

[Tom Ogilvy]

Georges,
Guess I wasn't really following what you were doing - Just for completeness,
here is a method to do it without recursion. Assumptions same as Lucas's.
Change j to reflect the number of cells

Sub EFG()
Dim carry As Long, l As Long
Dim j As Long, rng As Range
j = 306
carry = 1
For i = j To 1 Step -1
l = Cells(i, 1)
l = l + carry
If l > 1 Then
l = 0
carry = 1
Else
carry = 0
End If
Cells(i, 1) = l
If carry = 0 Then Exit Sub
Next
End Sub

When all 306 cells are filled with 1's this will roll over to all zeros. If
you wanted it to error out like Lucas's to indicate they are filled then you
could add a command to do that.

--
Regards,
Tom Ogilvy


"georgesmailuk" wrote:

> Tom - many thanks for that code - gave me another angle to atttack it from.
> Worked on it for about half an hour before i got tied up again. lol
>
> Lucas - wow
> I've spent all day trying to work out how to do this and you can come up
> with that?
> Pure genius.
>
> Many thanks - i can now lock some of the switches on and let the code run
> for those remaining. Should be in the clear now - you've really made my day.
>
> thanks again both
> George
>
> "Lucas Swanson" wrote:
>
> > This is pretty simple with recursion.
> >
> > I have made the assumption that the top cell is the highest "place" (would
> > be the rightmost digit if you were reading left-to-right). Also, I only used
> > 10 cells, but you could easily change this to use as many as you wanted. I
> > added a button to the sheet to run the macro but you can run it any way you
> > want, just make sure to send the cell with the last digit.
> >
> > Code:
> > Option Explicit
> >
> > Private Sub CommandButton1_Click()
> >
> > ' Add one to the last cell
> > Call AddOne(Cells(10, 1))
> > End Sub
> >
> > Private Sub CommandButton1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
> >
> > Call CommandButton1_Click
> > End Sub
> >
> > Sub AddOne(rgTarget As Range)
> >
> > With rgTarget
> > ' Add one to the value of the current cell
> > .Value = .Value + 1
> >
> > ' If this makes the value more than one
> > If (.Value > 1) Then
> > ' Reset the value to zero
> > .Value = 0
> >
> > ' Add one to the next cell
> > Call AddOne(.Offset(-1, 0))
> > End If
> > End With
> > End Sub
> >
> > Hope this helps.
> >
> > "georgesmailuk" wrote:
> >
> > > i am writing a macro that looks at 306 cells in column A
> > > each cell contains 1 or 0 (representing off and on)
> > >
> > > I am trying to make the value increase each time i give a command -
> > > remaining in the binary format)
> > > I know this would be 2^306 combinations, but i promise this is not what i'm
> > > attempting.
> > >
> > > The code i have so far (is wrong), shows how i was trying to keep the 306
> > > figures together as a string so i did not have any overflow errors. I
> > > couldn't think of a way to move it up to the next level so was going to try
> > > to multiply the string by 1 and add 1 then work through each character to try
> > > to get 1 to 10 to 11 etc.
> > > I admit the idea is hairbrained, but i'm stumped as to how i can get this to
> > > work so was clutching at straws.
> > >
> > > Any advice appreciated.
> > >
> > > Sub test()
> > > Dim x As String
> > > Dim i As Range
> > > Dim a As String
> > >
> > > For Each i In Range("A1:A306")
> > > p = i.Address
> > > x = x & i.Value
> > > Next i
> > >
> > > 1
> > > For b = 1 To Len(x)
> > > a = Right(Left(x, b), 1)
> > > If a = 0 Or a = 1 Then
> > > xbinary = True
> > > Else
> > > x = x + 1
> > > GoTo 1
> > > End If
> > > Next b
> > >
> > > z = x
> > > 'put data back in
> > > For b = 1 To Len(x)
> > > c = Right(Left(z, b), 1)
> > > Range("A" & b).Value = c
> > > Next b
> > >
> > > End Sub

[georgesmailuk]

The plural of "genius" is "geniuses". The form "genii", the plural of the
word in Latin, is the plural of the guardian spirit of Roman mythology

Now when i get asked what 2 geniuses are called, i'll say Tom and Lucas.

Thanks again guys. Tom, it was probably my code that confused you - i know
it confused me :O)

I can see how both work and i must say i am impressed (and embarrassed about
the way i was trying it) i was going to go from 0 , +1 = 1, + 1 = 2 which
is not 1 or 0 so add again etc all the way up. I'm not sure there is any
computer that code could even run on.

You may not be guardian spirits of Roman mythology, but you guys can
definately pull off a few miricles.

Again, with gratitude
George
"Tom Ogilvy" wrote:

> Georges,
> Guess I wasn't really following what you were doing - Just for completeness,
> here is a method to do it without recursion. Assumptions same as Lucas's.
> Change j to reflect the number of cells
>
> Sub EFG()
> Dim carry As Long, l As Long
> Dim j As Long, rng As Range
> j = 306
> carry = 1
> For i = j To 1 Step -1
> l = Cells(i, 1)
> l = l + carry
> If l > 1 Then
> l = 0
> carry = 1
> Else
> carry = 0
> End If
> Cells(i, 1) = l
> If carry = 0 Then Exit Sub
> Next
> End Sub
>
> When all 306 cells are filled with 1's this will roll over to all zeros. If
> you wanted it to error out like Lucas's to indicate they are filled then you
> could add a command to do that.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "georgesmailuk" wrote:
>
> > Tom - many thanks for that code - gave me another angle to atttack it from.
> > Worked on it for about half an hour before i got tied up again. lol
> >
> > Lucas - wow
> > I've spent all day trying to work out how to do this and you can come up
> > with that?
> > Pure genius.
> >
> > Many thanks - i can now lock some of the switches on and let the code run
> > for those remaining. Should be in the clear now - you've really made my day.
> >
> > thanks again both
> > George
> >
> > "Lucas Swanson" wrote:
> >
> > > This is pretty simple with recursion.
> > >
> > > I have made the assumption that the top cell is the highest "place" (would
> > > be the rightmost digit if you were reading left-to-right). Also, I only used
> > > 10 cells, but you could easily change this to use as many as you wanted. I
> > > added a button to the sheet to run the macro but you can run it any way you
> > > want, just make sure to send the cell with the last digit.
> > >
> > > Code:
> > > Option Explicit
> > >
> > > Private Sub CommandButton1_Click()
> > >
> > > ' Add one to the last cell
> > > Call AddOne(Cells(10, 1))
> > > End Sub
> > >
> > > Private Sub CommandButton1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
> > >
> > > Call CommandButton1_Click
> > > End Sub
> > >
> > > Sub AddOne(rgTarget As Range)
> > >
> > > With rgTarget
> > > ' Add one to the value of the current cell
> > > .Value = .Value + 1
> > >
> > > ' If this makes the value more than one
> > > If (.Value > 1) Then
> > > ' Reset the value to zero
> > > .Value = 0
> > >
> > > ' Add one to the next cell
> > > Call AddOne(.Offset(-1, 0))
> > > End If
> > > End With
> > > End Sub
> > >
> > > Hope this helps.
> > >
> > > "georgesmailuk" wrote:
> > >
> > > > i am writing a macro that looks at 306 cells in column A
> > > > each cell contains 1 or 0 (representing off and on)
> > > >
> > > > I am trying to make the value increase each time i give a command -
> > > > remaining in the binary format)
> > > > I know this would be 2^306 combinations, but i promise this is not what i'm
> > > > attempting.
> > > >
> > > > The code i have so far (is wrong), shows how i was trying to keep the 306
> > > > figures together as a string so i did not have any overflow errors. I
> > > > couldn't think of a way to move it up to the next level so was going to try
> > > > to multiply the string by 1 and add 1 then work through each character to try
> > > > to get 1 to 10 to 11 etc.
> > > > I admit the idea is hairbrained, but i'm stumped as to how i can get this to
> > > > work so was clutching at straws.
> > > >
> > > > Any advice appreciated.
> > > >
> > > > Sub test()
> > > > Dim x As String
> > > > Dim i As Range
> > > > Dim a As String
> > > >
> > > > For Each i In Range("A1:A306")
> > > > p = i.Address
> > > > x = x & i.Value
> > > > Next i
> > > >
> > > > 1
> > > > For b = 1 To Len(x)
> > > > a = Right(Left(x, b), 1)
> > > > If a = 0 Or a = 1 Then
> > > > xbinary = True
> > > > Else
> > > > x = x + 1
> > > > GoTo 1
> > > > End If
> > > > Next b
> > > >
> > > > z = x
> > > > 'put data back in
> > > > For b = 1 To Len(x)
> > > > c = Right(Left(z, b), 1)
> > > > Range("A" & b).Value = c
> > > > Next b
> > > >
> > > > End Sub