VBA code not working

[MICHIEL_BOG]

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.

[June7]

Please post code between CODE tags to retain indentation and readability. Use # icon on post edit toolbar. Can edit your post.

Have you step-debugged?

Suggest you provide file for analysis. Follow instructions at bottom of my post.

[Paul_Hossler]

1. Please use a non-generic title instead of "Code not working"

2. When deleting, it's better to start at the bottom and go up




For i = guestlineSheet.Cells(guestlineSheet.Rows.Count, 5).End(xlUp).Row to 2 Step -1



3. Rows without the dot refers to the active sheet. In this case it doesn't matter since the sheets have the same number of rows, but there are a lot of objects where it does make a difference, so I like to be explicit

[Aussiebear]

Welcome to VBAX MICHIEL_BOG. Sorry for the late welcome to our forum.

[MICHIEL_BOG]

Thank you for the quick reply.
So I'll try and explain what the whole point is of cross referencing these documents.
The "Guestline file" is a posting report of all expenses from hotel guests, the "Oaky" file is a file of all upsells that were done during checkin.

Recently we started an upsell program that gives incentives to front office hosts but for this to work we also need to check, not only if they added the upsell in Oaky but if the upsell was eventually also paid by the guest in "Guestline".
So in both files, the only column that has the same references is the column with the booking reference: Column E in the Guestline file (BookRef) and Column A in the Oaky file (reservation).

The Guestline file, when downloaded is over 8000 row long so in first instance I want to bring that down to a much smaller amount by deleting the rows that don't have the following words ("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") in column D (Description)

Afterwards I want to check the booking references that are in the Oaky file are also found in the Guestline file and that if not these get highlighted in the Oaky file. Also the booking references that have a negative amount in column H (Gross Unit) in the Guestline file should be hightlighted in the Oaky file in a different colour than the previous highlight.

My VBA skills are very limited but there is nobody else in my company but me to have this check implemented. So that is the reason why I hope to find a solution online.

[June7]

If you want a database why not use a database software? Access would be ideal for this. Access can link to these worksheets and a query can then join tables to view related data. Takes about 10 minutes. No VBA.

However, neither BookRef nor reservation field has unique data. There are duplicate values in each field. Need another field to pair with the others so that records in each table can be uniquely identified. At first glance that would appear to be Deal and Description. Unfortunately, the data in these fields are not in agreement. In Description there is "Parking", in Deal it is "Parking deal". Lack of consistency in data is a nightmare for analysis - by any software.

I was able to link worksheets in Access and run a query to show only desired rows from Guestline:
SELECT Guestline.*
FROM Guestline
WHERE (((Guestline.Description) In ("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")));

Here's an easy way to eliminate unwanted rows in Excel without destroying original data. First add a sheet to the workbook. Insert a module and run this procedure.

Sub Purge()
Dim cn As Object, rs As Object
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

Worksheets("Guestline").Rows("1:1").Copy
Worksheets("Sheet1").Paste
Application.CutCopyMode = False

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & ThisWorkbook.FullName & ";" & _
"Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text"""

rs.Open "SELECT [Guestline$].* FROM [Guestline$] WHERE Description " & _
"IN('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')", cn, 3, 3, 1

Worksheets("Sheet1").Range("A2").CopyFromRecordset rs
Worksheets("Sheet1").Activate
Worksheets("Sheet1").Range("A1").Select
End Sub

[MICHIEL_BOG]

Thank you for the tip. I'll look into this. I haven't used Access in a long time but it's good to maybe follow a refreshing course :yes.
I tried to run the procedure but it is giving me an error on the "rs.open" saying that 'Guestline' is not a valid name. I don't get it.

[June7]

I can't explain that. I ran the procedure in the file you posted.

[Aflatoon]

Re your initial code, as Paul mentioned before, you need to loop backwards when deleting, so:


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("Oaky.xls")
Set oakySheet = oakyFile.Sheets("Transactions details")
' Open the Guestline file
Set guestlineFile = Workbooks("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 rowNum = guestlineSheet.Cells(Rows.Count, "E").End(xlUp).Row To 2 Step -1
Set cell = guestlineSheet.Cells(rowNum, "E")
' Check if the reservation number is not found in Oaky file
If Application.CountIf(oakyReservationNumbers, cell.Value) = 0 Then
' Check if the row contains any of the specified words in column D
If Not ContainsWords(guestlineSheet.Cells(rowNum, "D").Value) Then
' Delete the entire row
guestlineSheet.Rows(rowNum).Delete
End If
End If
Next rowNum
' 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