MICHIEL_BOG
08-10-2023, 12:39 AM
Hello there
I got this code from an AI code generator.
Sub CrossReferenceAndDelete()
Dim oakyFile As Workbook
Dim guestlineFile As Workbook
Dim oakySheet As Worksheet
Dim guestlineSheet As Worksheet
Dim oakyRange As Range
Dim guestlineRange As Range
Dim oakyReservationNumbers As Range
Dim guestlineReservationNumbers As Range
Dim cell As Range
Dim rowNum As Long
' Open the Oaky file
Set oakyFile = Workbooks.Open("C:\Users\rm.bru\Pillows Hotels\IHMG Revenue - Documenten\General\Partners\Oaky\Checking\Oaky.xls")
Set oakySheet = oakyFile.Sheets("Transactions details")
' Open the Guestline file
Set guestlineFile = Workbooks.Open("C:\Users\rm.bru\Pillows Hotels\IHMG Revenue - Documenten\General\Partners\Oaky\Checking\Guestline.xls")
Set guestlineSheet = guestlineFile.Sheets("Guestline")
' Get the range of reservation numbers in Oaky file
Set oakyRange = oakySheet.Range("A2:A" & oakySheet.Cells(Rows.Count, 1).End(xlUp).Row)
Set oakyReservationNumbers = oakyRange.SpecialCells(xlCellTypeConstants)
' Loop through the Guestline file
For Each cell In guestlineSheet.Range("E2:E" & guestlineSheet.Cells(Rows.Count, 5).End(xlUp).Row)
' Check if the reservation number is not found in Oaky file
If Application.CountIf(oakyReservationNumbers, cell.Value) = 0 Then
rowNum = cell.Row
' Check if the row contains any of the specified words in column D
If Not ContainsWords(guestlineSheet.Cells(rowNum, 4).Value) Then
' Delete the entire row
guestlineSheet.Rows(rowNum).Delete
End If
End If
Next cell
' Save and close the files
guestlineFile.Save
guestlineFile.Close
oakyFile.Close
MsgBox "Cross-reference and deletion completed!"
' Cleanup memory
Set oakyRange = Nothing
Set oakyReservationNumbers = Nothing
Set oakySheet = Nothing
Set oakyFile = Nothing
Set guestlineRange = Nothing
Set guestlineReservationNumbers = Nothing
Set guestlineSheet = Nothing
Set guestlineFile = Nothing
End Sub
Function ContainsWords(ByVal text As String) As Boolean
Dim wordsToCheck As Variant
Dim word As Variant
wordsToCheck = Array("Balcony Room Upgrade", "Bicycle", "Breakfast Child Special Rate", "E Bike", "Lof Restaurant Reservation", "Olivier Bistro Reservation", "Parking", "Room Upgrade", "Room with a bath upgrade", "Special Breakfast Rate")
For Each word In wordsToCheck
If InStr(1, text, word, vbTextCompare) > 0 Then
ContainsWords = True
Exit Function
End If
Next word
ContainsWords = False
End Function
So the code runs and I don't receive any errors but the rows that don't have these words don't get deleted. I have tried to search why but I don't get any further.
Thank you in advance for the help.
I got this code from an AI code generator.
Sub CrossReferenceAndDelete()
Dim oakyFile As Workbook
Dim guestlineFile As Workbook
Dim oakySheet As Worksheet
Dim guestlineSheet As Worksheet
Dim oakyRange As Range
Dim guestlineRange As Range
Dim oakyReservationNumbers As Range
Dim guestlineReservationNumbers As Range
Dim cell As Range
Dim rowNum As Long
' Open the Oaky file
Set oakyFile = Workbooks.Open("C:\Users\rm.bru\Pillows Hotels\IHMG Revenue - Documenten\General\Partners\Oaky\Checking\Oaky.xls")
Set oakySheet = oakyFile.Sheets("Transactions details")
' Open the Guestline file
Set guestlineFile = Workbooks.Open("C:\Users\rm.bru\Pillows Hotels\IHMG Revenue - Documenten\General\Partners\Oaky\Checking\Guestline.xls")
Set guestlineSheet = guestlineFile.Sheets("Guestline")
' Get the range of reservation numbers in Oaky file
Set oakyRange = oakySheet.Range("A2:A" & oakySheet.Cells(Rows.Count, 1).End(xlUp).Row)
Set oakyReservationNumbers = oakyRange.SpecialCells(xlCellTypeConstants)
' Loop through the Guestline file
For Each cell In guestlineSheet.Range("E2:E" & guestlineSheet.Cells(Rows.Count, 5).End(xlUp).Row)
' Check if the reservation number is not found in Oaky file
If Application.CountIf(oakyReservationNumbers, cell.Value) = 0 Then
rowNum = cell.Row
' Check if the row contains any of the specified words in column D
If Not ContainsWords(guestlineSheet.Cells(rowNum, 4).Value) Then
' Delete the entire row
guestlineSheet.Rows(rowNum).Delete
End If
End If
Next cell
' Save and close the files
guestlineFile.Save
guestlineFile.Close
oakyFile.Close
MsgBox "Cross-reference and deletion completed!"
' Cleanup memory
Set oakyRange = Nothing
Set oakyReservationNumbers = Nothing
Set oakySheet = Nothing
Set oakyFile = Nothing
Set guestlineRange = Nothing
Set guestlineReservationNumbers = Nothing
Set guestlineSheet = Nothing
Set guestlineFile = Nothing
End Sub
Function ContainsWords(ByVal text As String) As Boolean
Dim wordsToCheck As Variant
Dim word As Variant
wordsToCheck = Array("Balcony Room Upgrade", "Bicycle", "Breakfast Child Special Rate", "E Bike", "Lof Restaurant Reservation", "Olivier Bistro Reservation", "Parking", "Room Upgrade", "Room with a bath upgrade", "Special Breakfast Rate")
For Each word In wordsToCheck
If InStr(1, text, word, vbTextCompare) > 0 Then
ContainsWords = True
Exit Function
End If
Next word
ContainsWords = False
End Function
So the code runs and I don't receive any errors but the rows that don't have these words don't get deleted. I have tried to search why but I don't get any further.
Thank you in advance for the help.