Numerology Challenge

[bigmacfann]

Hello, I have a HUGE list of either 2 digit or 3 digit numbers in column I. I'm trying to set up a macro that would convert this whole column into a single digit number by using the numerology method. This is it in a nutshell: If cell I1 was to have '157' in it, I would like cell J1 to contain '13' (created by adding each digit in I1: 1+5+7), then in cell J1 I would like it to contain '4' (created by adding each digit in J1: 1+3). I know a macro can do this quickly but I have very little programming experience and that experience stops at QBasic. Thank you for your help in advance. Here are some more examples of how to get a single digit number by using numerology if my above example wasn't good enough:
78 -> 15 -> 6
115 -> 7
100 -> 1
899 -> 26 -> 8
177 -> 15 -> 6
204 -> 6

Thanks again.

 

[Scott Huish]

This can be done with a formula:
=MOD(I1-1,9)+1

 

[dafan]

Try this:

Private Sub CommandButton1_Click()

Dim Length, Sum, AmountNumbers, FirstNumber, SecondNumber, ThirdNumber As Integer
Dim TotalNumber As String


AmountNumbers = Range("A65536").End(xlUp).Row
For I = 1 To AmountNumbers
    TotalNumber = Range("A" & I).Text
    Length = Len(TotalNumber)
    
    If Length = 2 Then
        FirstNumber = Val(Mid(TotalNumber, 1, 1))
        SecondNumber = Val(Mid(TotalNumber, 2, 1))
        
        Sum = FirstNumber + SecondNumber
        Range("B" & I).Value = Sum
        
        If Sum > 9 Then
            FirstNumber = Val(Mid(Sum, 1, 1))
            SecondNumber = Val(Mid(Sum, 2, 1))
            
            Sum = FirstNumber + SecondNumber
            Range("C" & I).Value = Sum
        End If
        
    ElseIf Length = 3 Then
        FirstNumber = Val(Mid(TotalNumber, 1, 1))
        SecondNumber = Val(Mid(TotalNumber, 2, 1))
        ThirdNumber = Val(Mid(TotalNumber, 3, 1))
        
        Sum = FirstNumber + SecondNumber + ThirdNumber
        Range("B" & I).Value = Sum
        
        If Sum > 9 Then
            FirstNumber = Val(Mid(Sum, 1, 1))
            SecondNumber = Val(Mid(Sum, 2, 1))
            
            Sum = FirstNumber + SecondNumber
            Range("C" & I).Value = Sum
        End If
        
    End If
        
    
Next I

End Sub

Works with your example!

 

[Scott Huish]

As a macro:

Sub test()
Dim c As Range
For Each c In Range("I1:I" & Range("I" & Rows.Count).End(xlUp).Row)
    If c > 0 Then
        c.Offset(, 1) = ((c - 1) Mod 9) + 1
    Else
        c.Offset(, 1) = 0
    End If
Next
End Sub

 

[dafan]

This can be done with a formula:
=MOD(I1-1,9)+1

You've got to be kidding me -_-
Just spent 20 mins building that Sub lol!

But mine prints the inbetween too when >9

Edit: My god you even build a great macro ;(

 

[bigmacfann]

Thank you for both of your responses. I haven't tested out the macro yet but the formula "=MOD(I1-1,9)+1" worked perfectly. I don't know exactly how it works but the fact that it does is great! I understand the 1,9 part (being that we're looking for numbers between 1 through 9) but why I1-? Also why +1 at the end?

 

[Trebor76]

Ah,

I can see my learned friends have beaten me to the punch

Nonetheless, here's my suggested VBA solution:

Sub Macro1()

Dim lngLastRow As Long
Dim intNumNo As Integer

lngLastRow = Cells(Rows.Count, "I").End(xlUp).Row

'Assumes the data (digits) start at cell I2 as row 1 is used _
for headings.  Change as required.
For Each cell In Range("I2:I" & lngLastRow)

intNumNo = 0

    If IsNumeric(cell) = True And Len(cell) = 2 Then
        intNumNo = _
            Val(Left(cell, 1)) + Val(Right(cell, 1))
        If intNumNo > 9 Then
            intNumNo = _
                Val(Left(intNumNo, 1)) + Val(Right(intNumNo, 1))
            cell.Offset(0, 1).Value = intNumNo
        Else
            cell.Offset(0, 1).Value = intNumNo
        End If
        
    ElseIf IsNumeric(cell) = True And Len(cell) = 3 Then
        intNumNo = _
            Val(Left(cell, 1)) + Val(Mid(cell, 2, 1)) + Val(Right(cell, 1))
        If intNumNo > 9 Then
            intNumNo = _
                Val(Left(intNumNo, 1)) + Val(Right(intNumNo, 1))
            cell.Offset(0, 1).Value = intNumNo
        Else
            cell.Offset(0, 1).Value = intNumNo
        End If
        
    Else
    
        MsgBox "Cannot apply the numerology method to cell " & _
            cell.Address(False, False) & " as " & _
            """" & cell.Value & """ is beyond calculation constraints.", _
            vbExclamation, "Numerology Conversion Editor"
        
    End If
    
Next cell

End Sub

HTH

Robert

 

[Scott Huish]

The -1 and +1 is about ensuring that numbers add up to 9 return 9 not 0.
For example, if you just did this, if I1 held 10, and you did this =MOD(I1,9) this would return 1 which is correct, but if I1 held 9, =MOD(I1,9) would return 0, not correct.

By subtracting 1 first, this is what happens:
9-1=8, 8 MOD 9 = 8, 8 + 1 = 9, the correct answer.

If I1 was 18:
18-1=17, 17 MOD 9 = 8, 8 + 1 = 9, the correct answer

If I1 was 37:
37-1=36, 36 MOD 9 =0, 0 + 1 =1, the correct answer

 

[Yogi Anand]

Just a slight modification to HOTPEPPER's superHOT formula ...

=MOD(I1,9)+(I1=9)*9

this will handle cases of the original number being 0, and 9 as well.

 

[codesmith]

I do all my excel work with vba ... but I see there are all these nifty formulas ... how would i learn them all?

I dont even know how vlookup works yet!