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

Cosmos75

Active Member
Joined
Feb 28, 2002
Messages
359
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??
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
This should do it:

<pre>
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</pre>

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

If you've got any problems, just repost.
 
Upvote 0
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.
 
Upvote 0
That's fun?
What kind of fun do you have? :biggrin:

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. :)
_________________<font color = green> Mark O'Brien
This message was edited by Mark O'Brien on 2002-04-09 12:12
 
Upvote 0
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
 
Upvote 0
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:<pre>
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</pre>

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.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top