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 .
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