CODEWITHSUNDEEP
vbaexcel
jose m ruiz
jose m ruiz

Reputation: 37

Use find.range to get first row in a column

For simplicity let's say I have the following data: - Column A contains a list of seats available on a plane (seats that are not occupied) 1A,1B,1C,1D,1E,1F,2A,2B,2C,etc

When a seat available is allocated to a passenger, I would like to find that seat and delete it from the list.

My code works for every seat that is not the first seat in column A, let's say a passenger wants seat 1A which is Cell(1,1) or Range("A1") when I use find.range I get row 2, therefore it continues available for other passengers when in fact it shouldn't.

What am I doing wrong here?

Private Sub RemoveFromListSeatAllocated(seat As String)
Dim sht As Worksheet
Dim rng, As Range

Set sht = Sheets("This sheet")
Set rng = Range("A1:A192")

'FIND SEAT IN LIST OF SEATS AVAILABLE
With sht.rng
  Set where = .Find(what:=seat, LookIn:=xlValues)
End With

Set rng = Range("A" & where.Row)

'DELETE SEAT FOUND AND SHIFT UP 
sht.rng.Delete Shift:=xlUp

End Sub

Upvotes: 0

Views: 80

Answers (1)

Vityata
Vityata

Reputation: 43575

"What am I doing wrong here?" You are not using Option Explicit, thus the code quality is going low on a level that it does not compile:

Dim rng, As Range - that should be a compiling error, the comma is not needed

With sht.rng - compiling error

Set where = .Find(what:=seat, LookIn:=xlValues) - what is where - compiling error

Advise - write Option Explicit on the top of your module, then Select "Debug>Compile" and see the errors you are getting. Try to work on them, until you are able to compile.

Upvotes: 2

Related Questions