VLOOKUP and delete row if found
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 |
VLOOKUP and delete row if found
Hi Connie,
Have you tried : LookupRange = ("$C$2:" & Rng.Address) Cheers Carim |
VLOOKUP and delete row if found
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" wrote in message oups.com... 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 |
VLOOKUP and delete row if found
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. Bob Phillips wrote: 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" wrote in message oups.com... 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 |
VLOOKUP and delete row if found
Hi,
Just replace Oracle_No by "Oracle_No" ... HTH Carim |
VLOOKUP and delete row if found
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("Or acle_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) "Connie" wrote in message oups.com... 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. Bob Phillips wrote: 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" wrote in message oups.com... 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 |
All times are GMT +1. The time now is 03:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com