Why doesn't the macro I created work on all of my sheets.

[bfdrahul]

I have an excel file which consists of 50 sheets. 49 of these sheets are identical to one another, although each of them have a different sheet name to the other. The first sheet is completely different to these other 49 sheets


First sheet information:
The first sheet is named “Acquirer Listings” and contains a list of acquirer listings. This sheet contains nine columns with their names (listed in quotes) and their column letters shown in the list below:
“URL” -Column A
“Name”-Column B
“Sheet Name”-Column C
“Profile”-Column D
“Category”-Column E
“Location”-Column F
“Stake”-Column G
“accredited”-Column H
“listed”-Column I
“Valuation Budget”-Column J


This sheet contains 49 rows of acquirer listings that showcase information about each acquirer with respect to each column.


Second sheet information:
This sheet was used as a blank template that would be duplicated to create the remaining 48 sheets.
This sheet contains a table of acquirer preferences from cell Q2:T10. These preferences are made up of four categories (“Category” in column Q, “Valuation Budget” in column R, “Location” in column S, “Stake” in column T). Each of these categories offer a selection of options in the following format:


Category:
SaaS
eCommerce
Mobile
Content
Marketplace
Agency
Fintech
Web3


Valuation Budget:
$100,000
$1,000,000
$10,000,000


Location:
EMEA
AMER
APAC


Stake:
Full
Minority
Majority






Other sheet information (third sheet onwards):
These sheets are each named after an Acquirer within the “Acquirer Listings” sheet. For example, an acquirer with the sheet name “Acquirer2” will have their own sheet with the sheet name “Acquirer2”


The other sheets each contain a table of acquirer preferences from cell Q2:T10. These preferences are made up of four categories (“Category” in column Q, “Valuation Budget” in column R, “Location” in column S, “Stake” in column T). Each of these categories offer a selection of options in the following format:


Category:
SaaS
eCommerce
Mobile
Content
Marketplace
Agency
Fintech
Web3


Valuation Budget:
$100,000
$1,000,000
$10,000,000


Location:
EMEA
AMER
APAC


Stake:
Full
Minority
Majority


Based on the information listed within the columns for each acquirer in the “Acquirer Listings” sheet, I have used Excel VBA to write a macro which will highlight the relevant options within each respective acquirer preferences table that is exist within each acquirers sheets.


This macro analyses the “Acquirer Listings” sheet and locate the “sheet names” column to determine which excel sheet the succeeding information should be highlighted within. Afterwards, the macro analyses the matching column headings within the “Acquirer Listings” sheet and the other sheets named after respective acquirers to determine which options should be highlighted within each individual acquirers preferences table.


For example, imagine that there is an acquirer listed in the “Acquirer Listings“ sheet that has “Acquirer5” within its “sheet name” column, “SaaS Marketplace” within its “Category” column, “AMER” within its “Location” column, “Full Majority Minority” within its “Stake” column, and “$100,000” within its “Valuation Budget” column. This macro opens the sheet named “Acquirer5” and highlights the cells which read “SaaS” and “Marketplace” under the “Category” column, highlights the cell which reads “AMER” under the “Location” column, highlights the cells which read “Full”, “Majority” and “Minority” under the “Stake” column, and highlights the cell which reads “$100,000” under the “Valuation Budget” column.


For another example, imagine that there is an acquirer listed in the “Acquirer Listings“ sheet that has “Acquirer9” within its “sheet name” column, “SaaS eCommerce Mobile Content Fintech Marketplace Web3 Agency” within its “Category” column, “EMEA AMER APAC” within its “Location” column, “Full Majority Minority” within its “Stake” column, and “$100,000” within its “Valuation Budget” column. This macro opens the sheet named “Acquirer9” and highlights the cells which read “SaaS”, “Marketplace” “eCommerce”, “Mobile”, “Content”, “Fintech”, “Marketplace”, “Web3” and “Agency” under the “Category” column, highlights the cells which read “AMER”, “EMEA” and “APAC” under the “Location” column, highlights the cells which read “Full”, “Majority” and “Minority” under the “Stake” column, and highlights the cell which reads “$100,000” under the “Valuation Budget” column.


Whenever I run this macro it carries out these tasks perfectly for there columns named “Valuation Budget” in column R, “Location” in column S, “Stake” in column T within each respective acquirer preferences table that is exist within each acquirers sheets.

However, for the “Category” in column Q of these preferences tables, the macro either only correctly highlights the relevant options within this column for SOME acquirer preferences tables or does not highlight any options at all for the other acquirer preferences tables.

Attached to this thread is an example file that showcases this issue.


Please help me correct the VBA code for this macro (it is in the text below this request to ensure that ) or identify the issue within the acquirer sheets that it does not work for, to ensure that the macro will only correctly highlight the relevant options within “Category” column heading in column Q for ALL acquirer preferences tables. The column heading (“Category”) is within cell Q2 and all the options in this column are within the cell range Q3:Q10.


The VBA code is as follows:

Sub HighlightPreferences()
    Dim wsListings As Worksheet
    Dim acquirerRow As Long, prefRow As Long
    Dim acquirerName As String
    Dim categoryStr As String, locationStr As String, stakeStr As String, budgetStr As String
    Dim categoryArr() As String, locationArr() As String, stakeArr() As String, budgetArr() As String
    Dim acquirerSheet As Worksheet

    ' Set the main "Acquirer Listings" sheet
    Set wsListings = ThisWorkbook.Sheets("Acquirer Listings")

    ' Loop through each row in "Acquirer Listings" sheet
    For acquirerRow = 2 To wsListings.Cells(wsListings.Rows.Count, "A").End(xlUp).Row
        ' Get acquirer's sheet name
        acquirerName = wsListings.Cells(acquirerRow, "C").Value

        ' Check if the acquirer's sheet exists
        If SheetExists(acquirerName) Then
            ' Set the acquirer's sheet
            Set acquirerSheet = ThisWorkbook.Sheets(acquirerName)

            ' Get preferences from the "Acquirer Listings" sheet
            categoryStr = wsListings.Cells(acquirerRow, "E").Value
            locationStr = wsListings.Cells(acquirerRow, "F").Value
            stakeStr = wsListings.Cells(acquirerRow, "G").Value
            budgetStr = wsListings.Cells(acquirerRow, "J").Value

            ' Split preference strings into arrays
            categoryArr = Split(categoryStr, " ")
            locationArr = Split(locationStr, " ")
            stakeArr = Split(stakeStr, " ")
            budgetArr = Split(budgetStr, " ")

            ' Loop through each preference and highlight in the acquirer's sheet
            For prefRow = 2 To 10 ' Assuming preferences start from row 2
                If Not IsEmpty(acquirerSheet.Cells(prefRow, "Q").Value) Then
                    If IsInArray(acquirerSheet.Cells(prefRow, "Q").Value, categoryArr) Then
                        acquirerSheet.Cells(prefRow, "Q").Interior.Color = RGB(255, 255, 0) ' Yellow
                    End If
                End If

                If Not IsEmpty(acquirerSheet.Cells(prefRow, "R").Value) Then
                    If IsInArray(acquirerSheet.Cells(prefRow, "R").Value, budgetArr) Then
                        acquirerSheet.Cells(prefRow, "R").Interior.Color = RGB(255, 255, 0) ' Yellow
                    End If
                End If

                If Not IsEmpty(acquirerSheet.Cells(prefRow, "S").Value) Then
                    If IsInArray(acquirerSheet.Cells(prefRow, "S").Value, locationArr) Then
                        acquirerSheet.Cells(prefRow, "S").Interior.Color = RGB(255, 255, 0) ' Yellow
                    End If
                End If

                If Not IsEmpty(acquirerSheet.Cells(prefRow, "T").Value) Then
                    If IsInArray(acquirerSheet.Cells(prefRow, "T").Value, stakeArr) Then
                        acquirerSheet.Cells(prefRow, "T").Interior.Color = RGB(255, 255, 0) ' Yellow
                    End If
                End If
            Next prefRow
        End If
    Next acquirerRow
End Sub


Function SheetExists(sheetName As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets(sheetName)
    On Error GoTo 0
    SheetExists = Not ws Is Nothing
End Function


Function IsInArray(value As String, arr() As String) As Boolean
    Dim i As Long
    For i = LBound(arr) To UBound(arr)
        If arr(i) = value Then
            IsInArray = True
            Exit Function
        End If
    Next i
End Function

[Aflatoon]

Your code is splitting on a normal space (character code 32) but the text in your col E is delimited by non-breaking spaces (character code 160) so your array is just one item, and there are no matches for that.

[p45cal]

the text in your col E is delimited by non-breaking spaces (character code 160)

Yes! and sometimes a mixture of that and normal spaces (Acquirer3).
A clue, somewhere:

categoryStr = Replace(categoryStr, Chr(160), " ")

ps. always a good idea to thank people for their previous help just after you've posted wanting more help.

[bfdrahul]

I see, thank you for replying, will change my code to account for this.

[bfdrahul]

Thank you for your response! I really do appreciate your advice on this thread and the last. Taking into account what you have taught me, I have made the relevant changes to the VBA code and the macro now works as intended.