So I have a column of data (player names) that I pasted from another Excel file. Cell A2
has the first player name and then there are several blank rows (the number of blank rows isn't the same for every roster sheet), and I am trying to determine the row number of the next non-empty cell with:
nextPlayerRow = rosterSh.Columns("A").Find(What = "*", after:=rosterSh.Range("A2"), LookIn:=xlValues).row
where nextPlayerRow
is declared as an Integer
. This, however, returns a Runtime Error 91.
What am I doing wrong?
CodePudding user response:
Run-time error 91 is Object variable not set. Possibly either you have not done a set to initialize rosterSh, or your .find is finding nothing, and therefore unable to return .row.
When you use the .find method, (or any time you need a property of a method) you should first retrieve the object with a Set, and then check for the existence of the object, before trying to access one of its properties as in:
Set nextPlayerCell = rosterSh.Columns("A").Find(What = "*", after:=rosterSh.Range("A2"), LookIn:=xlValues)
If Not nextPlayerCell is nothing then
nextPlayerRow = nextPlayerCell.row
End If
CodePudding user response:
Instead of using Find
you could use End(xlDown)
.
nextPlayerRow = rosterSh.Range("A2").End(xlDown).Row