Excel Macro Help Please

[moeee]

882 938 0 0 0 982
918 0 0 0 0 982
246 339 421 542 697 983
258 319 399 490 790 983
471 546 635 743 872 983

These are Row examples of Columns B to G
I need a Macro that will copy and paste (Special,Values) C to G onto positions B to F
But only if F is greater than zero
I have perhaps 2000 Rows that need doing

I need a Macro that will save me manually doing this task.

[Shaun]

I am confused, it looks like you want to copy an item then paste over the top of the same item.

Do you want this to copy the items one by one after looking to see if particular data equals zero?

[moeee]

Hi Shaun
Thanks for your interest.

What I have is the past 5 runs of an animal with the oldest run in Column B and the latest run in Column F
As this animal has a new run , I need to introduce this new run and eliminate the run 6 starts back.

So in the Worksheet , I need to move all the figures in a row , 1 to the left.
If the animal only has 4 or less runs , then I dont wish to do that.


EDIT:
The Manual version of what I do is I look to see if the Cell in Column F is more than zero.
If it is , then I select Cells C to G , and copy and paste them over the top of Cells B to F.

[Shaun]

any chance you could post the sheet?

[moeee]

I try

[moeee]

try this

EDIT : And as a New Meeting has concluded , I will fill in the appropriate Numbers into Column G and then do the Macro that I ain't got but at the moment I do it manually.

[Shaun]

i had a look and not sure a macro would do it, reason being that is a data base so unless they all ran at the same time i can't see a way to work out what runners need updating and what don't.

[moeee]

Seems your not getting it Shaun.

Picture this.

The Sheet I posted is actually a physical sheet of paper.
At the end of the day I need to add a new set of figures to Column G.
Some Cells will have numbers and some will remain blank.
If a Row now has 6 numbers next to its name , I need to make it only 5 , and this is accomplished by clearing Cell B and transferring all the numbers across to the left.
So B will now hold the previous C , and C will hold the previous D and so on.

If the Row does not have 6 numbers , I leave that Row alone.


EDIT:
I stated in earler Post that this happens when a Meeting has concluded.
This is where I messed up and got you into trouble.
It should be when the "Day" has Concluded.
I do this so the Sheet will updated and be ready for tomorrows Studies.

[moeee]

This I haven't tried , but is pretty much what I want happening

Sub Macro1()

SELECT.SHEET = "DATABASE"

FOR SELECTION = 1 TO 2000
IF CELL (SELECTION,F) = 0 THEN

Range("C17:G17").Select
Selection.Cut
Application.CutCopyMode = False
Selection.Copy
Range("B17").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
END IF
NEXT SELECTION

End Sub

[norisk]

moeee, maybe an easier approach would be record a macro while creating a set of formulas in say columns K to P which duplicate the figures in B to G but only bring across the last 5 runs, & then copy/paste special/values columns K to P over B to G.

Shouldnt be too hard to do, formula would be something like if(G<>"",G, if F<>"",F, & so on

[moeee]

OMG NoRisk.
If I knew what you were talking about I figure I would have this situation sorted and not have had a Question to ask.

I do know how to record a Macro and then go ahead and cut and paste and select Sheets and stuff like that.
And then stop recording and then perhaps modify the Macro to do it precisely what I want.

The one item that I don't know how to put in a macro unless I edit is the For x= 1 to 100 type.I use this a lot.

But I have never tried doing Formulas , but I will try and see if the Formula is in the Recorded Macro after I type it in a cell whilst recording .



EDIT:
Just reread what you Posted :(
Made perfect sense this time.
I'll give that one a try.

Thanks buddy.

[norisk]

lol, no probs moeee, if u get stuck repost & I will help out,

actually just meddling with some ratings today with no races on :(

[moeee]

Nope.
Some people just have no idea :(

I need to combine 2 Formulas into one

The First Formula is
=IF($F1<>0,B1,C1)
And that is working except it ain't exactly what I want.

What I need to add is a further IF , but I keep getting an error
I try doing this.
=IF($F1<>0 then IF($G1<>0,B1,C1))

Its not an error in the Result but a Syntax Error.
Could it be I need to change the "then" into and "and"?

I'll go try that and if no Luck I'll wait for help.

[Shaun]

=IF($F1<>0,IF($G1<>0,B1,C1),"")

[norisk]

try this moeee, I added a couple of figures highlighted in yellow to get it working (assuming we are on the same page of course;))

[moeee]

Sort of so close NoRisk , but if I insert a Number into Cell G1 , then the duplicate Rows shouldn't change.
And the Reason they shouldn't change is because Cell F1 is a Blank Cell.
Whenever Cell F contains nothing , then that Row of Numbers needs to stay as it is.(Well it doesn't really , but thats another story which may be revealed once this Formula gets sorted out)


EDIT:
Also - If I insert a number into Cell G4 , then the number in K4 becomes a null and it should stay the same as it was.

[norisk]

sounds highly complex moeee, & being on my second & aiming for my third beer probably wont help in finding a solution today;)

but just to clarify, you only enter a new rating in column F or G?

[woof43]

moee
excel isnt the proram for this, but anyway

add a column to your data that will have the formula countnumbers.

then apply conditional formatting so any row that has a 5 = (5 starts) will be formatted red, then add more formatting so that each of the cells with countnumbers are all different colours.

then sort the data using advanced filter , using colour as the filter so now will only have entrants wth 5 starts , then just create a macro to paste your new number, then copy and paste to another sheet
then repeat till you do done them all...then automate it into one macro..

[moeee]

I know you have the answer Woof43.
And I appreciate you trying to help.
But as usual , you really may as well be talking in Chinese.
I can't even understand NoRisk, and at that stage I don't think he even cracked his first tinny.

The New data gets pasted into Column G NoRisk.

Then the Norisk Columns contents get pasted over the top of Columns B to F and then Column G is cleared.

And then we do it all again in 24 hours or so :)

[woof43]

thats ok,
the real crux i'm interested in is the ratings, are they are power type of ratings based on grade and finish.

There's a big difference between racing and dogs class /grade/power ratings

does'nt take much thought but in greyhounds if a dog progressed thru the grades by winning each start and was having it's next start in top grade, and then you had a dog that raced in top grade for 4 weeks and had run second each week, which dog should be on top in the power/grade ratings?

[moeee]

I've created more confusion today then there was exactly 2012 years ago.

These ain't Ratings Woof43.
I'll try to explain.

I have video clips.
Each Video Clip has a number.
The Hard Disk can only hold 1,000 clips.

So at the end of each day , I need to delete a number of clips to allow room for tomorrows recordings.
So if I wish to tape 10 Races tomorrow , I need to delete 10 Clips.
And The Hard disk relabels the original titles Number as the old number minus whatever number I delete.

And the new Clips are then titled , 991 , 992 , and so on.

The idea of the numbers is so I look up a dog in my database and I press one of its Numbers on the dvd player , and I can watch its traits.

I'm not sure if you are aware of my memory , but I barely have one.
Its a terrible handicap to my handicapping.

[woof43]

why bother when dartfish tv has them all for free

[moeee]

I have 1.5 Gig per month to mess with , that gets chewed up much too quickly.
My Brodband is super super slow in this area due to fringe reception with my dongle.
Its just so much more convenient and quicker having my own copy of the race.
I can watch the replay as many times as I wish immediately after the race.

I actually did have a play with that Darfish , but I think it didn't work unless you were actually on line.
Plus I'm not sure if there is a substantial delay from when the race video being provided.
At the minute , I haven't even bothered installing Silverlight.

[woof43]

ok, explains why , when i ran a few of your dogs thru a percentile ranking it just didnt work out

[moeee]

I seem to be having problems with cells and Formulas that depend on the contents of that cell.
When there is a zero in a cell the Formula works.
When there is a Number , and then the number is cleared then sometimes the Formula thinks there is a zero in the cell and sometimes it thinks there is a Null in the cell.
And sometimes if I type a word inthe cell , then the Formula thinks there is a number greater than zero in the cell.

This Formula is preceded by the IF Statement.

[norisk]

moeee, all clear now, tomorrow's a writeoff for me (races then the footy:)), but Sunday I will be happy to sort it, unless some kind soul chips in in the meantime.

[woof43]

copy the formula and paste here, will make it easier to understand

[Raven]

Does this work?

[woof43]

That looks good to me...

good work

[moeee]

OMG RAVEN!!!
I think not only have you answered my original question , You appear to have answered my next question.

U DA MAN.
May that you Raven grow up to be an Emu and kick some HUGE Ass.

[woof43]

I've created more confusion today then there was exactly 2012 years ago.

These ain't Ratings Woof43.
I'll try to explain.


I'm not sure if you are aware of my memory , but I barely have one.
Its a terrible handicap to my handicapping.

Moee, could you not just have an a4 sheet that has a series of tables that you just tick or cross or highlight for dogs of note when you have watched a race once, instead of viewing it a number of times,

[moeee]

I toyed with the idea and bull********ted myself that I was going to write notes beside every dog in my database as to running patterns and quirks.
It never happened.
It never will.

I can't explain why.

I dig holes in the back yard and fill them up with dirt from the front yard.
The backyard dirt I bag and throw in the wheelie bin.

I can't explain why.

My Mother suggested it is her fault that I fell on my head at 4 years of age.
It wasn't.
But the incident could explain why I can't explain.

Racing is back tomorrow.
I totally enjoyed todays break.
I totally enjoyed Ravens assistance.

I am copping a hiding and have been for almost a month.
It ends today.
Look for my posts tomorrow everyone if you want some encouragement.

[moeee]

In an 8 horse field there are 56 different possible Box Trifecta Combinations that can occur

I have a Race with 8 greyhounds in it.
In SHEET 1 I have Column A with the Box Numbers and in Column B I have the animals names.
So A1 contains 1 and B1 contains "DOG NAME"

In SHEET 2 I want to do this.
I have written down all the 56 possible Box Trifectas - 123, 124 , 125....678
That is in Columns A,B,C
What I wish to do is have an animals name appear in a Column E when the animals Box Number is in Column A
And So with Column F and Column G.

What Formula do I put in SHEET2 E1?
and then copy that Formula through Column EFG.

[moeee]

i managed to get something happening with the VLOOKUP Function.
But its not how I wish to do it.

What I want is like this Formula.
=C1
But instead of the 1 its looks in Cell A1 to find the number.
Like =C"A1" or =C(A1) or ="C"(A1)
And A1 would contain a number.

[moeee]

Thanks for your help whoever may have tried.
But no need assistance anymore.

I managed to find something in the EXCEL Help.
Its called the OFFSET Function.
=OFFSET(F1,A1,0) pretty much does it for me.

Cheers :)