Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can i get past the overflow error?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can i get past the overflow error?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can i get past the overflow error?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can i get past the overflow error?
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can i get past the overflow error?
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can i get past the overflow error?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Overflow error.. why? | Excel Discussion (Misc queries) | |||
Overflow error | Excel Programming | |||
Overflow Error | Excel Programming | |||
Overflow error | Excel Programming | |||
Help! Overflow Error 6 | Excel Programming |