Dear BZOIRO,

Yes, you can modify the SearchAll function in the given VBA code to search for each word in the cells separately. I have added the modified code in the Excel file below. This modification splits the search value into words using the Split function and loops through each word to find it in the range. If all words are found, the function merges the found ranges into a single range and thoroughly returns it. If any of the words are not found, the function exits and returns Nothing. Here’s how to use this for your case:

1. Initially, assign Macro “SearchMultipleSheetsIndividually” in the Search command box and click Run.

2. Afterward, type the word you wish to look for. Also, don’t forget to use the Wildcards (*, ? etc) while searching. For instance, if you wish to look for a cell of 3 words and has “Emily” in the middle, type *Emily* and then perform searching. Alternatively, if it happens to be the first word, try Emily*.

Download the Excel file to get the modified VBA code and practice by yourself. I hope it works for you.
https://www.exceldemy.com/wp-content/uploads/2023/01/Search-Box-for-Multiple-Sheets.xlsm
Best Regards,
Yousuf Khan (ExcelDemy Team)

Dear TONY,

It’s possible that the delay is due to the amount of data being processed by the validation formula. To reduce the delay, you can try the following:

1. Turn off screen updating and calculation during the execution of the macro to reduce the time it takes to display the changes.
2. Consider using a different method for data validation, such as using a custom function or a Macro.

As requested, I have added a revised version of the VBA code with the optimizations under Private Sub Worksheet_Change in Module 3. I hope, these modifications will improve the performance of the code, as it minimizes the amount of visual updates that occur during the process. This will reduce the amount of time it takes for the changes to be made in the sheet as you type in. Download the Excel file and use the desired code.
https://www.exceldemy.com/wp-content/uploads/2023/01/Multiple_Selection_from_Drop-Down.xlsm
Best Regards,
Yousuf Khan (ExcelDemy Team)

Dear GORAN ZORIC,

Thank you for your comment! We are glad you took the time to review the article. Regarding the trade direction (BUY or SELL), it is actually represented in the Long / Short (L/S) column in the trading journal template. The trade direction entry can be either a “Buy” (long) or “Sell” (short) and the template will automatically add this field after you enter your entry and exit prices. We apologize for any confusion and hope this clarifies the matter. Thank you for bringing this to our attention.

Regards,
Yousuf Khan (ExcelDemy Team)

Hello FAHIM,

Yes, it is possible to highlight rows and columns of multiple active cells using conditional formatting in Microsoft Excel. To do so, you can use the following steps:

1. Initially, select the cells you want to apply the conditional formatting to.
2. Afterward, go to the Home tab and click the Conditional Formatting button in the Styles group.
3. Later, choose New Rule from the drop-down menu.
4. Next, select “Use a formula to determine which cells to format.”
5. Meanwhile in the formula box, enter the following formula:
=ROW()>=MIN(ROW($C$4:$F$10))
6. Further, click the Format button and select the fill color you want to use.
7. Lastly, click OK to close the Format Cells dialog box.
This approach uses a relative reference for the row and column in the formula, allowing the conditional formatting to adjust automatically as cells are selected.

Hello Mike,

Thank you for sharing your experience with us!

I understand you are getting non-clickable link text when you are copying the URL from web browsers. Also, the Paste Special command is not responding which would solve the issue in your case. Unfortunately, Paste Special command does not offer the Paste Link option while pasting web URLs.

But Excel directly inserts a hyperlink to a copied URL when you:

1. Copy the URL from a web browser >> select the cell where you want to paste >> paste the URL in the Formula Bar.

2. Alternatively, you can directly paste the URL to a cell >> go to Formula Bar >> press Enter.

This will convert the Text link to Hyperlink. You can also use the HYPERLINK function to get clickable URL links(read the article).

How to Use Excel HYPERLINK Function

Regards,
Yousuf Shovon

Hello Anthony,

Please, see the forum post to ignore cells when applying the SORT function.

Ignoring Null Cells In The Sort Function

Regards,
Yousuf Shovon

Dear Mike,

I understand you wish to insert a range to have the text as input. Here is the modified code to do so.

Public Sub SendReminderMail()
    'Declare the variables
    Dim XDueDates As Range
    Dim XRcptsEmails As Range
    Dim xMailContents As Range
    Dim xMailContent2s As Range

    Dim xDueDate As Range
    Dim xRcptEmail As Range
    Dim xMailContent As Range
    Dim xMailContent2 As Range
    
    Dim xRngDn As Range
    Dim xCrtOut As Object
    Dim xValDateRng As Variant
    Dim xValSendRng As Variant
    Dim k As Long
    Dim xMailSections As Object
    Dim xFinalRw As Long
    Dim CrVbLf As String
    Dim xMsg As String
    Dim xSubEmail As String
    On Error Resume Next
    
    'To select the date column insert an input box
    Set XDueDates = Application.InputBox("Select the range for Deadline/Due Date column:", "ExcelDemy", , , , , , 8)
    If XDueDates Is Nothing Then Exit Sub
    
    'Insert an input box for selecting the recipients
    Set XRcptsEmails = Application.InputBox("Choose the range for the email addresses of the recipients:", "ExcelDemy", , , , , , 8)
    If XRcptsEmails Is Nothing Then Exit Sub
    
    'To enter the task Name, insert an input box
    Set xMailContents = Application.InputBox("In your email, choose the range with the Task Name:", "ExcelDemy", , , , , , 8)
    If xMailContents Is Nothing Then Exit Sub
    
    'To enter the task details, insert an input box
    Set xMailContent2s = Application.InputBox("In your email, choose the range with the Task Details:", "ExcelDemy", , , , , , 8)
    If xMailContent2s Is Nothing Then Exit Sub
    
    'Count rows for the due dates
    xFinalRw = XDueDates.Rows.Count
    
    'Set command to open MS Outlook Application
    Set xCrtOut = CreateObject("Outlook.Application")
    
    'Apply For loop to conduct the operation in each row one by one
    For k = 1 To xFinalRw
        xValDateRng = ""
        xValDateRng = XDueDates.Cells(k).Value
        'Apply If condition for the Due Date values
        If xValDateRng  "" Then
            'Condition set to send mail if the difference between due dates and current date is greater than 1 and less than 7 days
            'Means 1 < X< 7, X = Due Date - Current Date
            If CDate(xValDateRng) - Date  0 Then
                xValSendRng = XRcptsEmails.Cells(k).Value
                'Create the subject, body and text contents with the required variables
                xSubEmail = xMailContents.Cells(k).Value & " on " & xValDateRng
                CrVbLf = ""
                xMsg = ""
                xMsg = xMsg & "Dear " & xValSendRng & CrVbLf
                xMsg = xMsg & "Text: " & xMailContents.Cells(k).Value & CrVbLf
                xMsg = xMsg & "Text: " & xMailContent2s.Cells(k).Value & CrVbLf
                xMsg = xMsg & ""
                'Create the email
                Set xMailSections = xCrtOut.CreateItem(0)
                'Define the position to place the Subject, Body, and Recipients Address
                With xMailSections
                    .Subject = xSubEmail
                    .To = xValSendRng
                    .HTMLBody = xMsg
                    .Display
                    '.Send
                End With
                Set xMailSections = Nothing
            End If
        End If
    Next k
    Set xCrtOut = Nothing
End Sub

Also, you can insert a data range by adding variables. To know more about this, see this comment.

Hello JACK MACEY,

I understand you are facing issues while creating the weekly and monthly sheets using the same given code. The provided code works perfectly for Daily frequency. But for Weekly and Monthly intervals, you have to adjust the parameter of the frequencies in the code accordingly.

In this line below, we have declared the parameter of the frequency.

"https://query1.finance.yahoo.com/v7/finance/download/"&Ticker&"?period1"&StartDate&"&period2"&EndDate

We have to add the interval=1wk or interval=1mo parameters to the URL, which specify the Weekly and Monthly intervals.

Here are the modified codes.

For Weekly frequency,

let
    Ticker = Excel.CurrentWorkbook(){[Name="Ticker"]}[Content]{0}[Column1],
    StartDate = Excel.CurrentWorkbook(){[Name="StartDate"]}[Content]{0}[Column1],
    EndDate = Excel.CurrentWorkbook(){[Name="EndDate"]}[Content]{0}[Column1],
    Source = Csv.Document(Web.Contents("https://query1.finance.yahoo.com/v7/finance/download/"&Ticker&"?interval=1wk&period1"&StartDate&"&period2"&EndDate), [Delimiter=",", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers", {{"Date", type date}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close", type number}, {"Adj Close", type number}, {"Volume", Int64.Type}})
in
    #"Changed Type"

And for Monthly frequency,

let
    Ticker = Excel.CurrentWorkbook(){[Name="Ticker"]}[Content]{0}[Column1],
    StartDate = Excel.CurrentWorkbook(){[Name="StartDate"]}[Content]{0}[Column1],
    EndDate = Excel.CurrentWorkbook(){[Name="EndDate"]}[Content]{0}[Column1],
    Source = Csv.Document(Web.Contents("https://query1.finance.yahoo.com/v7/finance/download/"&Ticker&"?interval=1mo&period1"&StartDate&"&period2"&EndDate), [Delimiter=",", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers", {{"Date", type date}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close", type number}, {"Adj Close", type number}, {"Volume", Int64.Type}})
in
    #"Changed Type"

Try this way. And, let me know if it works.

Regards,
Yousuf Khan Shovon

Dear COLM,

Thank you for your very useful suggestion. I am including the note in the article with much appreciation.

Regards,
Yousuf Khan.

Dear RICH SAUNDERS,

I understand your frustration with trying to find specific INDEX/MATCH assistance for your Excel project. Here, I’ll create the Summary tab using INDEX/MATCH functions to connect the other four tabs.

To begin, let’s assume that your four tabs are named Dataset1, Dataset2, Dataset3, and Summary. To retrieve data from the Dataset1 tab, use the following formula:

=INDEX(Dataset1!$A$1:$O$40,MATCH($A2,Dataset1!$A$1:$A$40,0),MATCH(B$1,Dataset1!$A$1:$O$1,0))

Note: Be sure to update the tab name and range of cells to match the appropriate tab and data range.

I hope this helps! Let me know if you have any further questions.

Regards,
Yousuf Khan Shovon

Hello ARCHIE,

I understand you wish to fix the #VALUE! error for VLOOKUP. In your case, the reason this is returning #VALUE! error for alpha-numeric values is that it is trying to convert the text values to numbers using the VALUE function. The VALUE function is designed to convert text that represents a number to an actual number. When it encounters alpha-numeric values, it cannot convert them to numbers, hence the error.

Fortunately, you can fix this by modifying the formula by replacing the VALUE function with the TEXT function. The TEXT function converts a value to text in a specific number format. Try the modified formula given below:
=IF(ISNA(VLOOKUP(TEXT($A42,"0"),PROJECT_LIST!$A:$C,3,FALSE)),"",VLOOKUP(TEXT($A42,"0"),PROJECT_LIST!$A:$C,3,FALSE)&" ")
Let me know with a demo dataset if you still face issues. Good luck!
Note: Make sure data are sorted in ascending to descending order in the lookup table.
Regards,
Yousuf Khan

Hello ADITYA,

You’re welcome! We are glad to hear that you found the solution to your issue. Unprotecting the workbook can indeed resolve the issue of being unable to set the visible property of the Worksheet class. It’s great to see that your dynamic VBA code is working as expected. Let us know if there’s anything else we can help with.

Regards,
Yousuf Khan