Perhaps this approach?
Assuming your data begins in A2I have a spreadsheet that shows the scores a player gets in my game.
If the score is 5 or more player gets 1 point, he gets none if score is less than 5.
I need a macro to do this..... what is his scores and how much is his total score?
This will show you where the player scores a pointCode:Sub UseAFormula() Dim LastRow As Long LastRow = Range("A" & Rows.Count).End(xlUp).Row Range("B2").Formula = "=IF(A2<5,0,1)" Range("B2:B" & LastRow).FillDown End Sub
This will give you his/her total scoreCode:Sub AgainUseAFormula() Dim LastRow As Long LastRow = Range("A" & Rows.Count).End(xlUp).Row Range("C" & LastRow).Formula = "=SUM(B2:B" & LastRow & ")" End Sub
If you just need the total score then you could try this
Hope this helpsCode:Sub OrJustUseThisFormula() Dim LastRow As Long LastRow = Range("A" & Rows.Count).End(xlUp).Row Range("B" & LastRow).Formula = "=COUNTIF(A2:A" & LastRow & "," & """>=5""" & ")" End Sub
Bingo!, the OP has a macro to do the job and the formulae to boot!....
Hopefully the clue might just be in the Sub names......
I think that often the OP doesn't have a choice with the layout, either in a highly regulated environment, or the spreadsheet belongs to a customer/vender/other department, etc. I think it's important to inform the OP that they are working with an inferior design and suggest that it be fixed. I'm not sure I buy the "My boss wants it that way" as a legitamate excuse. Show the boss a good working spreadsheet and they will probably change their mind.
I wouldn't mind putting a good link to a site for proper spreadsheet design hints in my sig if anyone has one. I'm going to read the one that Broro suggested but I think 102 pages is a bit much for the average OP.
ChemistB
My 2¢
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Heavens! I wish that were true. But the small amount of "power" exercised by many management types I've had to deal with over the years included being very closed to suggestions that their "stuff" can be improved.Originally Posted by ChemistB
I once suggested what I thought was a very simply design change to a "Form" a manager was trying to use as a sort of database and he basically said, "If this is too hard for you, I'm sure I can find someone more skilled to add the stuff I need."
It's sad, but only half of the improvements I suggest to management types are even considered.
Now, when they give me a project to "get something done", I have much better success. But any manager who views themselves as an Excel "user" ultimately is a pain in the backside.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Too true. Luckily for me, I have always (thus far) been in a position to say "if you want it done your way, you do it; if you want me to do it, don't tell me how."
Generally I try to only work for people who don't understand what I do...
So long, and thanks for all the fish.
I tell the people I work with I'm likely to be the most talented Excel user they'll ever meet. They believe me.
LOL
CC
If you like a post, please rate with the scales icon (top right).
If you feel really indebted please consider a donation to charity. My preferred charity is ActionAid but there are plenty of worthy alternatives.
Hence Cheeky & not just Charlie!
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel consulting, free examples and tutorials visit Excel Consulting-Excel VBA
Check out the free Excel Toolbar
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
Code Tags: Make your code easier for us to read
my daughter is getting married this Saturday.
Anyone got a macro that can do my speech?
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
You probably need a macro to pay the bill
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel consulting, free examples and tutorials visit Excel Consulting-Excel VBA
Check out the free Excel Toolbar
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
Code Tags: Make your code easier for us to read
If that is a true statement, then my congratulations to her and her husband and to the parents
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Congratulations Martin. You don't look old enough to have a married daughter.
ChemistB
My 2¢
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
http://www.weddingspeechdigest.com/
You didn't say Excel template, did you?
For the father of the bride...
http://weddingspeech4u.com/fatherofb...e-speeches.php
thanxs all,yep it's true. just been to rehearsal. should be a laugh on the day if that was anything to go by!
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Oh, guys, I just realised, I left my life somewhereCode:Sub message() Dim s As String GoTo Cheeky will: s = s & "n" GoTo ever Charlie: s = s & "o" GoTo has 2: s = s & "r" GoTo be Cheeky: s = s & "C" GoTo Charlie programmer: s = s & "l" GoTo nd the: s = s & "t" GoTo greatest person: s = s & "i" GoTo you got: s = s & "g" GoTo 2 has: s = s & "n" GoTo got be: s = s & "a" GoTo the you: s = s & "o" GoTo will greatest: s = s & "u" GoTo programmer ever: s = s & "s" GoTo meet nd: s = s & "a" GoTo indeed indeed: s = s & "t" GoTo person meet: MsgBox s End Sub
CC
If you like a post, please rate with the scales icon (top right).
If you feel really indebted please consider a donation to charity. My preferred charity is ActionAid but there are plenty of worthy alternatives.
Martin,
You're gaining a son!
Code:Sub HappyDad() Dim I As Long Dim s As String For I = 0 To 15 s = s & _ Chr(-8.43145506689903E-08 * I ^ 15 + 9.47769013093849E-06 * I ^ 14 + _ -4.83441278563011E-04 * I ^ 13 + 1.48075937919645E-02 * I ^ 12 _ + -0.303701107427047 * I ^ 11 + 4.40271676872031 * I ^ 10 + _ -46.4058436707215 * I ^ 9 + 360.1861635668 * I ^ 8 + _ -2061.11023950731 * I ^ 7 + 8614.0153828836 * I ^ 6 + _ -25734.7878885401 * I ^ 5 + 52905.9125438716 * I ^ 4 + _ -70072.2384020787 * I ^ 3 + 52812.451571046 * I ^ 2 + _ -16738.1366367785 * I ^ 1 + 67 * I ^ 0) Next I MsgBox s End Sub
@MarvinP ... that's really impressive! That'd be great for encrypting data, passwords maybe ... any chance you can enlighten me as to how you come up with those numbers?
Regards
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks