Using a variable to define a row

[ellen0804]

Hi,
I'm hoping that someone here can help -- I'm trying to write a macro to deal with data from an experiment I'm doing in which subjects have 32 trials, but if they get one wrong they have to repeat it until they get it right, so the number of rows varies from 32 to up to 100 or so. I'd like my macro to do a couple things: find the last row (which it can do with the first section of code), then I want to be able to tell it later on to do stuff with the data all the way until the last row. For example, in this one section of code below I want it to determine if the trials are the first time the subjects saw them, or if they are repeats because they got the answer wrong. I want to know this for all trials from the second row (the first is a header) down to the end...
My problem seems to be in the line in red. I thought that I could define the last row as a variable, then use it in a cell later, but clearly I have made a mistake somewhere (or maybe this isn't possible?).
Any thoughts?
Thanks so much!!


'Find the last row
Range("L1").Select
Do
If IsEmpty(ActiveCell) Then
Range(ActiveCell).Select
End If
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 1))

'Define the last row as a variable
Dim LastRow As Long
LastRow = ActiveCell.Row

'Determine if the trials are originals (marked as 1) or repeats (marked as 2) and then sort the trials
Range("M2").Select
ActiveCell.FormulaR1C1 = "=IF(ISNUMBER(SEARCH(""Repeat"",RC[-12])), 2, 1)"
Selection.AutoFill Destination:=Range("M2:M" & LastRow), Type:=xlFillDefault
Columns("M:M").Select
Selection.Sort Key1:=Range("M2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

 

[Dryver14]

I would suggest change LastRow =

to

LastRow = Cells(Rows.Count, 13).End (xlup).Row

then when setting range for fill use

= Cells(2, 13).resize(LastRow, 13)

 

[ellen0804]

Thanks much! I did what you suggested (see my code below), but all it did was to repeat the selection across 13 columns, not down the rows, and not as many as there are rows. Then, later, in the next piece of code (to determine their reaction time only for original trials, not repeats), it quit the macro and gave me an error.
Any ideas? I'm clearly not very good at macros, trying to learn all I can!
Thanks again!

'Find the last row
Range("L1").Select
Do
If IsEmpty(ActiveCell) Then
Range(ActiveCell).Select
End If
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 1))

LastRow = Cells(Rows.Count, 13).End(xlUp).Row

'Determine if the trials are originals are repeats and sort so the originals are first
Range("M2").Select
ActiveCell.FormulaR1C1 = "=IF(ISNUMBER(SEARCH(""Repeat"",RC[-12])), 2, 1)"
Selection.AutoFill Destination:=Cells(2, 13).Resize(LastRow, 13), Type:=xlFillDefault
Columns("M:M").Select
Selection.Sort Key1:=Range("M2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

'Write reaction time in the column if it's an original trial
Range("N2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=1, RC[-2], "" "")"
Selection.AutoFill Destination:=Cells(2, 13).Resize(LastRow, 13), Type:=xlFillDefault

 

[Dryver14]

my bad

should be .resize(lastrow,0)

 

[ellen0804]

Great! Thanks! It wouldn't work with the last bit of code, but I was able to tweak it a bit and get it working... maybe not the most elegantly, but it works!
Thanks again!