VLOOKUP and delete row if found

[Connie]

I have a spreadsheet in which payroll clerks enter data for hundreds of
employees. One of the fields entered is the Oracle number of the
employee. I want to check a range in the sheet "Upload Data" for the
existence of the Oracle number. If the Oracle number is there, I want
to delete the row. There may be multiple rows with the Oracle number,
so I must check the entire range.

I am using a function called GetRealLastCell to determine the lookup
range.

Following is the code. I am stil in the developmental stage as I am
not able to get a match on the Oracle number even when it's there. I
believe there's something wrong with my vlookup statement. Once I find
a matching row, what syntax do I use to delete that row?

Since I have to delete all rows with the Oracle number, is it better to
loop through the range and check for the existence of the Oracle number
and delete the row if there's a match?

Private Sub Check_For_Existing_Oracle_No_Click()
Dim rng As Range
Dim Test As Variant
Sheets("Upload Data").Select
Set rng = GetRealLastCell(ActiveSheet)
LookupRange = ("$C$2:" + rng.Address)
Test = Application.VLookup(Oracle_No, LookupRange, 0, False)
If IsError(Test) Then
MsgBox "It wasn't found"
Else
MsgBox "it was found"
End If
End Sub

Function GetRealLastCell(sh As Worksheet) As Range
Dim RealLastRow As Long
Dim RealLastColumn As Long
On Error Resume Next
RealLastRow = _
sh.Cells.Find("*", sh.Range("A1"), , , xlByRows, xlPrevious).Row
RealLastColumn = _
sh.Cells.Find("*", sh.Range("A1"), , , xlByColumns,
xlPrevious).Column
Set GetRealLastCell = sh.Cells(RealLastRow, RealLastColumn)
End Function

 

[Carim]

Hi Connie,

Have you tried :

LookupRange = ("$C$2:" & Rng.Address)

Cheers
Carim

 

[Bob Phillips]

Private Sub Check_For_Existing_Oracle_No_Click()
Dim rng As Range
Dim Test As Variant
Sheets("Upload Data").Select
Set rng = GetRealLastCell(ActiveSheet)
LookupRange = "$C$2:" + rng.Address
Test = Application.VLookup(Oracle_No, Range(LookupRange), 0, False)
If IsError(Test) Then
MsgBox "It wasn't found"
Else
MsgBox "it was found"
End If
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Connie]

Still not working, and the reason is that its not recognizing the
Oracle_no (when I debug, the Oracle_no is blank.) Oracle_no is a
defined name in my spreadsheet. How do I pass the Oracle_no to the
command button?. I also changed the 3rd parameter to 1, as 0 is not
allowed. Thanks and I'm getting closer. Once I am able to get the
vlookup to work, how do I delete the row? Thanks.

 

[Carim]

Hi,

Just replace Oracle_No by "Oracle_No" ...

HTH
Carim

 

[Bob Phillips]

Private Sub Check_For_Existing_Oracle_No_Click()
Dim rng As Range
Dim Test As Variant
Sheets("Upload Data").Select
Set rng = GetRealLastCell(ActiveSheet)
LookupRange = "$C$2:" + rng.Address
Test = Application.VLookup(Worksheets("Sheet2").Range("Oracle_No"),
Range(LookupRange), 0, False)
If IsError(Test) Then
MsgBox "It wasn't found"
Else
MsgBox "it was found"
End If
End Sub

change the worksheet toi the appropriate sheet

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)