CODEWITHSUNDEEP
excelvba
Robert
Robert

Reputation: 11

how to select the top of a column in Excel

I have a spreadsheet that records dates of musical works rehearsed by a choir. Column A list the works and columns C,D,etc the date. I place a figure 1 in the cell that corresponds to the work and date. eg

   A          B        C      D       E        
1                   05 Jan  12 Jan  19 Jan
2 Song 1                1
3 Song 2                1       1       1
4 Song 3                        1       1

I run simple totals of the number of times a song has been rehearsed but would like to automatically copy into column B the date on which the song was last rehearsed i.e. for song 2 "12 Jan". The spreadsheet has many date columns and over 200 songs.

The following Macro works for songs 1 and 2 but not for song 3 when it returns the figure 1 from cell E3

Keyboard Shortcut: Ctrl+a

ActiveCell.Select
Selection.End(xlToLeft).Select
Selection.End(xlUp).Select
Selection.Copy
Range("L2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Range("L13").Select

End Sub

How can I modify it to work on every song? I'd appreciate advice.

Upvotes: 1

Views: 3460

Answers (2)

E. A. Bagby
E. A. Bagby

Reputation: 930

Here's an option:

Sub Test2()

    Dim RngFind As Range
    Dim x As Integer
    Dim y As Integer

    'Set a range, either hard-code it in like I did or use a named range
    Set RngFind = Sheets(1).Range("C2:E4")

    'iterate through each row and column and capy date in column of last cell with a value
    'It takes the date from the top row and the same column.  You can use "1" or "X" or something else
    'in the range for tracking when the song was rehearsed
    For y = RngFind.Row To (RngFind.Row + RngFind.Rows.Count)
        For x = RngFind.Column To (RngFind.Column + RngFind.Columns.Count)
         If Sheets(1).Cells(y, x).Value <> "" Then Sheets(1).Cells(y, "B").Value = Sheets(1).Cells(1, x).Value
        Next x
    Next y

End Sub

Upvotes: 1

Dick Kusleika
Dick Kusleika

Reputation: 33165

It looks like Song 2 was last rehearsed on 19 Jan, not 12 Jan. You also say you want to put the date in column B, but that's not what your code does. Anyway, if you want the last rehearsed data in col B, but this formula in B2

=INDEX(C$1:E$1,1,MATCH(2,C2:E2,TRUE))

and fill down for however many songs you have. Also, adjust the E reference for the numbers of dates you have.

Trying to Match 2 when all your data are 1's will give you the last '1' in the list. Then you can feed that position into the INDEX function to get that date from row 1.

Upvotes: 0

Related Questions