Thanks:  0
Likes:  0

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

1. 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. 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. 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. 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. 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. 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

#### Posting Permissions

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