• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro help

version to test
... press [Demo] to compare
 

Attachments

  • example-macro-lotto.xlsm
    31.6 KB · Views: 2
Last edited:
vletm said:
version to test
... press [Demo] to compare
Click to expand...

Hi vletm:

Your macro working well, thank you. I want additionally the macro show in ball set A,B,C,D,E,F ;that each draw number must locate in new column C:H.

See again the attach file example-macro-lotto.

Excuse me for change the original request.
 
vletm said:
something like this?
next?
Click to expand...

Hi vletm:

I a novice in VBA, trying to understand the problems and so I learn VBA

I want apply your macro in real data file have 336 rows. I copy your macro and paste on real file but have trouble in run( F5), the excel show:

Run-time error 1004

Metod range of objet global failed

Run F5 and choose Debug and then show in highlight yellow color

Sheets("data").Sort.SortFields.Add Key:=Range("J1:J" & y - 1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

Thank you for your help
 
Do that file have sheet name "Data"?
After error,
was there any data (text..) in cells from J to L, from row 1?
Could You send that file for checking?
 
vletm said:
Do that file have sheet name "Data"?
After error,
was there any data (text..) in cells from J to L, from row 1?
Could You send that file for checking?
Click to expand...

Hi vletm:

I tried to send the file but chandoo refuse it to be very large.

Thank you for your help
 
@febausa Sheet "Data" is the result Sheet1.
I collect all data from Sheets A to F.
You have to have raw data (the content draw numbers) in Sheets A to F!
You wrote: on each page A, B, C, D, E, F; is includes the content draw numbers that correspond to each ballset.
Check Your those sheets.
If sheets from A to F are empty then the Data-sheet will be empty too.
 
vletm said:
@febausa Sheet "Data" is the result Sheet1.
I collect all data from Sheets A to F.
You have to have raw data (the content draw numbers) in Sheets A to F!
You wrote: on each page A, B, C, D, E, F; is includes the content draw numbers that correspond to each ballset.
Check Your those sheets.
If sheets from A to F are empty then the Data-sheet will be empty too.
Click to expand...

Hi vletm:

Please excuse me for not explaining well in the example.

I attach handmade same example for logic clarity but explain the logic better.

I want make new worksheet (output worksheet) “A, B, C, D, E, F"; generate from worksheet (input worksheet) “Data".

The numbers worksheet (input worksheet) "Data" must be located in column C.

Information show in the new worksheet (output worksheet) “A, B, C, D, E.F”,

1. in cell A1 will locate name “Date”, rest the row in column A will locate particular numerical Date,

2. column "B1"will locate name “Ballet”, rest the row in column B will locate particular “Bellset”

3. Column "C1:H1" will locate “Ball numbers”. rest the row in column C:H will locate particular “Ball numbers”

Please read important note in all worksheet.

I need your help for make a macro.

Thank you for your time and help.
 
Oh, just opposite way, yes!
There's One Good Thing,
it's easier to do this way.
From 'Data' to 'A:F'
I don't 'touch' 1st rows!
Test, please
 

Attachments

  • example-macro-lotto.xlsm
    33.4 KB · Views: 25
Hi Vletm:

Your macro working very well, until I added new: row 347 and row 348, do not add this information in sheet A. I tried to fix it but could not. Maybe there's a limit to the information (sheet: data)? Please help to repair your macro.

Thank you for your help.
 
Can You send that file?
Screenshot?
or at least those row's data?
I'm testing it now with rows 399 & 400. It works ...
 
Oh yeah, nice!
The reason is that cell [B347] has value 'A ' ( letter-A and space)!
You can see it too, can You?
There should be ONLY one character.
You have to possibilities.
1) delete that extra space away
2) modify code
2a) in the beginning, add one row
Code: 
Sub BallSet()
    Application.ScreenUpdating = False
    max_tabs = Worksheets.Count
2b) find next rows and modify one row; this solves case
Code: 
    If IsDate(Acol) Then
        Bcol = Left(.Cells(y, 2), 1)
        Ccol = .Cells(y, 3)
2c) in the end, add one row
Code: 
    End With
    Application.ScreenUpdating = True
    ans = MsgBox("Ready")
End Sub
 
vletm said:
Oh yeah, nice!
The reason is that cell [B347] has value 'A ' ( letter-A and space)!
You can see it too, can You?
There should be ONLY one character.
You have to possibilities.
1) delete that extra space away
2) modify code
2a) in the beginning, add one row
Code: 
Sub BallSet()
    Application.ScreenUpdating = False
    max_tabs = Worksheets.Count
2b) find next rows and modify one row; this solves case
Code: 
    If IsDate(Acol) Then
        Bcol = Left(.Cells(y, 2), 1)
        Ccol = .Cells(y, 3)
2c) in the end, add one row
Code: 
    End With
    Application.ScreenUpdating = True
    ans = MsgBox("Ready")
End Sub
Click to expand...

Hi Vletm:

I delete extra space and your macro working fine; Thank you
 
vletm said:
version to test
... press [Demo] to compare
Click to expand...

hello vletm:

Why when I run your macro always deletes content in cell A 52, = COUNT ($ A $ 2: $ A $ 50)?

The formula result in cell A 52 is use in lot other formula.

Thank you for help and time.
 
@febausa
Do You mean Cell A52 in sheets A,B,C,D,E ?
Did You told, that You should have some kind of limit that how many rows can copied to those sheets? Or ...
What will happen, then one day there is for example 51st A-BallSet?
That BallSet would use row 52, including cell[A52]!
So far, in the beginning of that [Demo],
all cells except the 1st row will clear (=Old datas away.)
If You are using something else in those sheets,
You can use area from cell[I1] to right!
Okay?
 
vletm said:
@febausa
Do You mean Cell A52 in sheets A,B,C,D,E ?
Did You told, that You should have some kind of limit that how many rows can copied to those sheets? Or ...
What will happen, then one day there is for example 51st A-BallSet?
That BallSet would use row 52, including cell[A52]!
So far, in the beginning of that [Demo],
all cells except the 1st row will clear (=Old datas away.)
If You are using something else in those sheets,
You can use area from cell[I1] to right!
Okay?
Click to expand...

Hi vletm:

Your macro working very well.

I need help in giving instructions to the macro does not delete the formulas located in cell A50: A60 in output sheets A, B, C, D, E, F.

Thank you for your time and help.
 
@febausa
Move Your formulas from [A50:A60] to [I50:I60]!
Anyway, not in columns [A:H]!
Or
You have to ask to LIMIT max BallSets to 48!
Which company's Lotto is this?
Or
There will be a nice challenge, how will Your formulas work, if those rows have to skip. Do You know, how to change those formulas ( datarows are from 2 to 49 and continue from 61 to ... )?
 
You must log in or register to reply here.
Back
Top