VBA Find Row based on 3 Criteria

[ShawnExcel]

I am trying to make a Macro that goes into another spreadsheet to delete a certain row of data.

In the user spreadsheet, the user enters the following info:

A1: Case ID
A2: Your name
A3: Date

The macro then goes into "Master Database" and finds the row that matches all 3 of the above criteria, makes the values blank, and then closes the file. Here is what I have so far:

Sub DeleteCST()

Dim CaseNumberDelete As String
Dim DateDelete As Date
Dim IntranetIDDelete as String
Dim FindRowNumber As Long

'Set This Woorkbook as a saved variable
UserFormWorkbook = ActiveWorkbook.Name
'ActiveWindow.Visible = True

CaseNumberDelete = ThisWorkbook.Sheets("Whoops").Range("A1").Value
IntranetIDDelete = ThisWorkbook.Sheets("Whoops").Range("A2").Value
DateDelete = ThisWorkbook.Sheets("Whoops").Range("A3").Value

If CaseNumberDelete = "" Then
MsgBox ("You forgot to enter a case number!")
Exit Sub
Else

If MsgBox("ARE YOU SURE YOU WANT TO DELETE THIS CASE?", vbOKCancel, "Confirm") = vbCancel Then
        Exit Sub
    End If

'Find and Locate the Main Master Sheet
Workbooks.Open ("C:/...")
'Windows("Master Database 2016.csv").Visible = False

With WB.Sheets("Master Database 2016")
    Set FindRow = .Range("A:A").Find(What:=CaseNumberDelete, LookIn:=xlValues)
    FindRowNumber = FindRow.Row
End With

'Don't display alerts when closing the master
Application.DisplayAlerts = False
  ActiveWorkbook.Save
Workbooks("Master Database 2016.csv").Close SaveChanges:=True

MsgBox ("You have successfully deleted your case!")

End Sub

Your help is greatly appreciated!

 

[xlstime]

Hi ShawnExcel,

I have changed smaller part rest is same.

I have used two do loop (until to check last cell and while to check duplicate)

Sub DeleteCST()

Dim CaseNumberDelete As String
'Dim DateDelete As Date
'Dim IntranetIDDelete As String
'Dim FindRowNumber As Long

'Set This Woorkbook as a saved variable
'UserFormWorkbook = ActiveWorkbook.Name
'ActiveWindow.Visible = True

CaseNumberDelete = ThisWorkbook.Sheets("Whoops").Range("A1").Value
IntranetIDDelete = ThisWorkbook.Sheets("Whoops").Range("A2").Value
DateDelete = ThisWorkbook.Sheets("Whoops").Range("A3").Value

If CaseNumberDelete = "" Then
MsgBox ("You forgot to enter a case number!")
Exit Sub

Else


If MsgBox("ARE YOU SURE YOU WANT TO DELETE THIS CASE?", vbOKCancel, "Confirm") = vbCancel Then Exit Sub
    End If

'Find and Locate the Main Master Sheet
'Workbooks.Open ("C:/...")
Workbooks.Open ("c:\Users\testUser\Desktop\mb.xlsx")
'Windows("Master Database 2016.csv").Visible = False


Windows("mb.xlsx").Activate
ActiveWorkbook.Sheets("Master Database 2016").Activate
  
i = 1
Do Until Cells(i, 1) = ""
    Do While Cells(i, 1) = CaseNumberDelete And _
    Cells(i, 2) = IntranetIDDelete And _
    Cells(i, 3) = DateDelete
  
    'CaseNumberDelete delete
    Cells(i, 1).ClearContents
    'IntranetIDDelete delete
    Cells(i, 2).ClearContents
    'DateDelete delete
    Cells(i, 3).ClearContents
  
Loop
i = i + 1

Loop

  
  
'    Set FindRow = .Range("A:A").Find(What:=CaseNumberDelete, LookIn:=xlValues)
'   FindRowNumber = FindRow.Row
'End With

'Don't display alerts when closing the master
Application.DisplayAlerts = False
  ActiveWorkbook.Save
Workbooks("Master Database 2016.csv").Close SaveChanges:=True

MsgBox ("You have successfully deleted your case!")

End Sub

I am trying to make a Macro that goes into another spreadsheet to delete a certain row of data.

In the user spreadsheet, the user enters the following info:

A1: Case ID
A2: Your name
A3: Date

The macro then goes into "Master Database" and finds the row that matches all 3 of the above criteria, makes the values blank, and then closes the file. Here is what I have so far:

Sub DeleteCST()

Dim CaseNumberDelete As String
Dim DateDelete As Date
Dim IntranetIDDelete as String
Dim FindRowNumber As Long

'Set This Woorkbook as a saved variable
UserFormWorkbook = ActiveWorkbook.Name
'ActiveWindow.Visible = True

CaseNumberDelete = ThisWorkbook.Sheets("Whoops").Range("A1").Value
IntranetIDDelete = ThisWorkbook.Sheets("Whoops").Range("A2").Value
DateDelete = ThisWorkbook.Sheets("Whoops").Range("A3").Value

If CaseNumberDelete = "" Then
MsgBox ("You forgot to enter a case number!")
Exit Sub
Else

If MsgBox("ARE YOU SURE YOU WANT TO DELETE THIS CASE?", vbOKCancel, "Confirm") = vbCancel Then
        Exit Sub
    End If

'Find and Locate the Main Master Sheet
Workbooks.Open ("C:/...")
'Windows("Master Database 2016.csv").Visible = False

With WB.Sheets("Master Database 2016")
    Set FindRow = .Range("A:A").Find(What:=CaseNumberDelete, LookIn:=xlValues)
    FindRowNumber = FindRow.Row
End With

'Don't display alerts when closing the master
Application.DisplayAlerts = False
  ActiveWorkbook.Save
Workbooks("Master Database 2016.csv").Close SaveChanges:=True

MsgBox ("You have successfully deleted your case!")

End Sub

Your help is greatly appreciated!