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

[Cosmos75]

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??

[Mark O'Brien]

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.

[Mark W.]

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.

[Mark O'Brien]

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 ]

[Cosmos75]

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 ]

[Mark O'Brien]

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.