Macro - assign a number to number in a value, multiply then
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Macro - assign a number to number in a value, multiply then

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I need a macro to do this

    Say I have the number 79298. I need to number each number to the left of the last number from 1 to 4 (or more if there are more numbers), from left to right.

    79298
    4321

    Then multiply the number by the number assigned to it and add them up.

    (9*1) + (2*2) + (9*3) + (7*4) = 9 + 4 +27 +28 = 68

    After that I need to check that the last number in the sum (i.e. 68) is equal to the last number in the original number (79298) which in this case it is.

    p.s. Also, any idea for a shorter title??

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This should do it:


    Public Sub main()

    Dim iNumber As Long
    Dim i As Integer
    Dim iProduct As Integer

    iNumber = 79298

    For i = 1 To (Len(iNumber))
    iProduct = iProduct + (i * Mid(iNumber, (Len(iNumber) + 1 - i), 1))
    Next

    If Right(iNumber, 1) = Right(iProduct, 1) Then
    MsgBox "Everything's groovy and checks out OK.", , "Valid Number"
    Else
    MsgBox "Everything's not groovy.", , "Invalid Number"
    End If
    End Sub


    Suggestion for title: "Arghhhh!!!!!!! I hate Excel!!!!!!!"

    If you've got any problems, just repost.

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Just for fun... here's the worksheet formula...

    {=RIGHT(SUM(MID(LEFT(A1,LEN(A1)-1),ROW(INDIRECT("1:"&LEN(A1)-1)),1)*(LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)-1)))))=RIGHT(A1)}

    Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

  4. #4
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That's fun?
    What kind of fun do you have?

    Some day I'll need to use something more than "SUM" in an excel spreadsheet.


    EDIT:: Actually, I tested you formula and it is very good. However, I think brain twisters like that one push people towards UDF's in VBA.
    _________________
    [b] Mark O'Brien

    [ This Message was edited by: Mark O'Brien on 2002-04-09 12:12 ]

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mark W,

    Cool! Groovy! THe messagebox was a nice touch. I might only use it to prompt me that a number doesn't check out as I need it to go through a list.

    Mark O'Brien,

    Thanks for the formula. And I must admit, that even though I am a novice at VBA, I do find it somewhat fun to get excel to bend to my will!!

    THANKS!

    Edit: Mark W. - What's UDF??

    [ This Message was edited by: Cosmos75 on 2002-04-09 13:14 ]

  6. #6
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      

    Edit: Mark W. - What's UDF??

    [ This Message was edited by: Cosmos75 on 2002-04-09 13:14 ]
    that was me, there's too many Mark's around here for my liking.

    UDF is User Defined Function.

    To replicate Mark W's array function using VBA you could do this:


    Public Function IsValid(iNumber as Long) as Boolean

    Dim i As Integer
    Dim iProduct As Integer

    For i = 1 To (Len(iNumber))
    iProduct = iProduct + (i * Mid(iNumber, (Len(iNumber) + 1 - i), 1))
    Next

    If Right(iNumber, 1) = Right(iProduct, 1) Then
    IsValid = True
    Else
    IsValid = False
    End If
    End Sub


    Now, to use this. In cell A1 stick in your number 79298 or whatever and in cell B1 put in this formula:

    =IsValid(A1)

    This will return true or false.

    Hope this helps.


User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com