How can i get past the overflow error?
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
|