Using a variable to define a row

ellen0804

New Member
Joined
Jan 12, 2011
Messages
10
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Dryver14

Well-known Member
Joined
Mar 22, 2010
Messages
2,396
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

New Member
Joined
Jan 12, 2011
Messages
10
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
 

ellen0804

New Member
Joined
Jan 12, 2011
Messages
10
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!
 

Forum statistics

Threads
1,141,277
Messages
5,705,463
Members
421,396
Latest member
turkthesnake

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top