Excel help please

Mark M · 17 Aug 2007 at 13:35

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

shine · 17 Aug 2007 at 13:41

The code for the macro will be something like:

Code:

Sub test()
'
' test Macro
'

'
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "100"
    Rows("7:7").Select
    Selection.Insert Shift:=xlDown
    Range("A7").Select
    ActiveCell.FormulaR1C1 = "100"
    Range("A8").Select
End Sub

All you need to do is fire the macro.

Mark M · 17 Aug 2007 at 14:09

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 · 17 Aug 2007 at 14:33

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.

shine · 17 Aug 2007 at 14:58

Sorry I misread what you wanted. To select the contents of A1 using this:

Code:

Range("A1").Select
    Selection.Copy

and to paste the copied data use:

Code:

ActiveSheet.Paste

All you now need is the code to select the last empty cell before you paste, something like this:

Code:

ActiveSheet.Range("a1").End(xlDown).Select

I couldn't get this last bit to work so you'll have to google it. Hope that helps.

A[L]C · 17 Aug 2007 at 15:27

Code:

Sub AddScore()
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("A" & LastRow)
End Sub

Mark M · 17 Aug 2007 at 15:48

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

A[L]C · 17 Aug 2007 at 15:51

It just simulates doing a find for a non blank looking in a specific column. Oh and it starts from the bottom.

Then copies and pastes it

Mark M · 21 Aug 2007 at 12:35

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.

Code:

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

A[L]C · 21 Aug 2007 at 13:52

im confused.

so you'll have 1 to whatever in column A which is the shot number yeah.

You want the macro to copy A1 to column B after the last score?

ie

we can ignore column A, and just check column B for the last entry, then paste A1 to the row below that?

Mark M · 21 Aug 2007 at 13:56

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.

A[L]C · 21 Aug 2007 at 13:57

Code:

Sub Test_2()
'
' Test_2 Macro
'


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

    
End Sub

Mark M · 21 Aug 2007 at 14:01

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,

A[L]C · 21 Aug 2007 at 14:25

messy but seems to work

Code:

Sub AddScore()
Dim LastRow As Long
        Application.ScreenUpdating = False
        LastRow = qexc_GetLastFilledCellInColumn("Sheet1", 2)
        LastRow = LastRow + 1
        ActiveSheet.Range("A1").Copy _
              Destination:=ActiveSheet.Range("B" & LastRow)
        Application.ScreenUpdating = True
End Sub


Function qexc_GetLastFilledCellInColumn(strSheetName As String, intColumnToCheck As Integer) As Integer
    Sheets(strSheetName).Cells(65536, intColumnToCheck).Select
    Selection.End(xlUp).Select
    qexc_GetLastFilledCellInColumn = Selection.Row
End Function

Example

http://paulharman.fmguy.com/Score_macro.xls

Mark M · 21 Aug 2007 at 14:28

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 · 21 Aug 2007 at 14:37

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;

Code:

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

A[L]C · 21 Aug 2007 at 14:40

you need to make sure you are sending the variables correctly......

LastRow = qexc_GetLastFilledCellInColumn("Sheet1", 2)

that needs to have the sheet name in it. 2 refers to column 2 ie B

Mark M · 21 Aug 2007 at 14:51

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.

A[L]C · 21 Aug 2007 at 15:04

np, you can add me to msn if you need to

Mark M · 22 Aug 2007 at 11:25

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.