Excel help please

[Mark M]

Doing a bit of a project in my lunch breaks on Excel to try and get to know some of the "non basic" things a bit better

What I want to do is to be able to enter a score into a cell at the top, say A1, and then press a button which copies this from A1 to the bottom of the previous scores list. Ive taken a screen shot as that didnt explain it very well!

I assume this will have to be done via a macro and a form button but the question is how do I make it so that the new score goes into the correct box?

Ie, 100 goes into the first score box and the next score goes into the cell next to number 2.

Thanks for any help, Mark

 

[Mark M]

Erm, you will have to excuse my newbieness but Ive never written any macros only ever recorded them.

What does ActiveCell.FormulaR1C1 mean?

And why has it got "100" in? What happens if the next score isnt 100? Will score 2 in the score section end up as 100 also?

Thanks again.

 

[Mark M]

Ok I tried that macro and its nearly there.

It does always copy 100 as apposed to the amount you enter into A1 but it also adds the latest score on top of the numbers, ie

100
100
100
100
1
2
3
4
5
6
7

Actually looking at the code its inserting a new line so its pushing the rest of the numbers down.

 

[Mark M]

Any chance you could briefly go through that as I have no idea what the hell its means

I was hoping to use this project to pick up some knowledge and not just rely on you experts

Thanks

 

[Mark M]

Ok Ive evntually got round to trying to implement the above code and its almost there but with a slight problem.

Down the left (column A) I have shot number 1, 2, 3, 4 etc and I want the score of that shot to go into column B next to the shot number. Currently however the score gets inserted into column B next to the last entry in column A

The *.xls file can be downloaded from here www.mark-m.co.uk/Score_macro.xls

I have changed VBA code slightly but still cant get it working.

Sub Test_2()
'
' Test_2 Macro
'


    Dim LastRow As Long
        LastRow = Cells.Find(What:="*", After:=[a1], _
              SearchOrder:=xlByRows, _
              SearchDirection:=xlPrevious).Row
        LastRow = LastRow + 1
        
        ActiveSheet.Range("A1").Copy _
              Destination:=ActiveSheet.Range("b" & LastRow)
    

    
End Sub

 

[Mark M]

Sorry its not the best of descriptions.

Down colum A I want the shot numbers ie

1
2
3
4
5
6
7
8
9
10

These never change

In column B I want the score from shot 1 to go next to shot number 1, and then the next shot to go underneath that one, ie

1 100
2 50
3 110
4
5
6
7
8
9
10

I think if we ignore colum A as you say then it may work but I couldnt really work out how to do it. Have you looked at the workbook I linked to above? Once you see whats happening Im sure you will understand a bit more.

 

[Mark M]

I think I had already tried that or something similar which gave me the same wrong result unfortunately. It copies the score from A1 and pastes it into B2, but when you put in a new score and run the macro froa 2nd time it doesnt do anything?

Thanks again,

 

[Mark M]

Sir you are a star. Thanks a million!

Im moving onto the stats page next so keep checking back as I will most probably require some further help

 

[Mark M]

And it was going so well

I tried copying the code from that workbook into the main work book that Ive been using for the project and when I run the macro now it comes up with an error:

Subscript out of range

When I debug it highlights the following;

Sheets(strSheetName).Cells(65536, intColumnToCheck).Select

 

[Mark M]

I was just about to come back on and say I had worked that out for myself

Sheetname is called score on the main one hence it not working.

Thanks again.

 

[Mark M]

Thanks mate. Really appreciate it but Im doing most of the work on it during my lunches at work where I dont have msn. But I'll add you for when Im at home.