Smartgambler
Pro-Punter

Go Back   OZmium Sports Betting and Horse Racing Forums > Public Forums > General Topics
User Name
Password
Register FAQ Search Today's Posts Mark all topics as read

To advertise on these
forums, e-mail us.

Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old 5th April 2012, 07:36 AM
moeee moeee is offline
Suspended
 
Join Date: Jan 1970
Location: Melbourne
Posts: 5,359
Default Excel Macro Help Please

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.
Reply With Quote
  #2  
Old 5th April 2012, 09:27 AM
Shaun Shaun is offline
Member
 
Join Date: Jan 1970
Location: Western Australia
Posts: 3,456
Default

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?
__________________
One Drive

"If the corporates are treating you poorly , just go elsewhere."
"If they need you , they will soon find out."
"If you need them , you will soon find out."
--moeee
_______________________________________________
Reply With Quote
  #3  
Old 5th April 2012, 12:31 PM
moeee moeee is offline
Suspended
 
Join Date: Jan 1970
Location: Melbourne
Posts: 5,359
Default

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.

Last edited by moeee : 5th April 2012 at 12:36 PM.
Reply With Quote
  #4  
Old 5th April 2012, 02:08 PM
Shaun Shaun is offline
Member
 
Join Date: Jan 1970
Location: Western Australia
Posts: 3,456
Default

any chance you could post the sheet?
__________________
One Drive

"If the corporates are treating you poorly , just go elsewhere."
"If they need you , they will soon find out."
"If you need them , you will soon find out."
--moeee
_______________________________________________
Reply With Quote
  #5  
Old 5th April 2012, 02:26 PM
moeee moeee is offline
Suspended
 
Join Date: Jan 1970
Location: Melbourne
Posts: 5,359
Default

I try
Reply With Quote
  #6  
Old 5th April 2012, 02:31 PM
moeee moeee is offline
Suspended
 
Join Date: Jan 1970
Location: Melbourne
Posts: 5,359
Default xls file attachment

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.
Attached Files
File Type: xls Meadows 5.xls (249.5 KB, 605 views)

Last edited by moeee : 5th April 2012 at 02:34 PM.
Reply With Quote
  #7  
Old 6th April 2012, 10:20 AM
Shaun Shaun is offline
Member
 
Join Date: Jan 1970
Location: Western Australia
Posts: 3,456
Default

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.
__________________
One Drive

"If the corporates are treating you poorly , just go elsewhere."
"If they need you , they will soon find out."
"If you need them , you will soon find out."
--moeee
_______________________________________________
Reply With Quote
  #8  
Old 6th April 2012, 10:32 AM
moeee moeee is offline
Suspended
 
Join Date: Jan 1970
Location: Melbourne
Posts: 5,359
Default

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.

Last edited by moeee : 6th April 2012 at 10:35 AM.
Reply With Quote
  #9  
Old 6th April 2012, 10:45 AM
moeee moeee is offline
Suspended
 
Join Date: Jan 1970
Location: Melbourne
Posts: 5,359
Default

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
Reply With Quote
  #10  
Old 6th April 2012, 11:13 AM
norisk norisk is offline
Member
 
Join Date: Dec 2011
Posts: 334
Default

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

Last edited by norisk : 6th April 2012 at 11:16 AM.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is On
Forum Jump



All times are GMT +10. The time now is 10:50 AM.


Powered by: vBulletin Version 3.0.3
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
©2008 OZmium Pty. Ltd. All rights reserved . ACN 091184655