Yousuf Khan

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 Allen,
Thank you for sharing your experience with us. I understand you want to know how the lag segments work to properly configure your digitized vibration sensor data from rotating machinery.
Here are factors to consider:
1. Longer data means you can use more lag values without losing reliability.
2. More frequent data collection might need more lag values to find detailed patterns.
3. If you expect specific patterns, use enough lag values to catch them.
4. To detect repeating patterns, use enough lags to cover potential periods.
5. More lags require more calculations, potentially slowing processing. Balance accuracy and efficiency based on available resources.
6. With your data resolution, you could start with around 40-50 lags to capture potential patterns within a rotation.
7. Finally, there’s no universally optimal lag length. It depends on your data, analysis objectives, and computational constraints.

Let’s calculate the autocorrelation with a data range similar to yours using a different technique. Since you have significantly large data, you can consider this method. I am going to calculate autocorrelation using Excel VBA to make the process faster and more accurate. Follow the steps:
1. Line up your data.
2. Run the below VBA code:

Sub CalculateAutocorrelation()
'Developed by ExcelDemy
    Dim dataRange As Range
    Set dataRange = Range("A1:A1808")
    Dim autocorrelationValues As Variant
    ReDim autocorrelationValues(1 To dataRange.Cells.Count)

    For i = 1 To dataRange.Cells.Count
        autocorrelationValues(i) = CalculateAutocorrelationForLag(dataRange, i - 1)
    Next i
    Range("B1").Resize(UBound(autocorrelationValues), 1).Value = autocorrelationValues
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlXYScatter
    ActiveChart.SetSourceData Source:=Range("A1:B1808")
End Sub

Function CalculateAutocorrelationForLag(dataRange As Range, lag As Integer) As Double
    Dim mean As Double
    Dim numerator As Double
    Dim denominator As Double
    mean = Application.WorksheetFunction.Average(dataRange)
    For i = 1 To dataRange.Cells.Count - lag
        numerator = numerator + (dataRange.Cells(i) - mean) * (dataRange.Cells(i + lag) - mean)
        denominator = denominator + (dataRange.Cells(i) - mean) ^ 2
    Next i
    CalculateAutocorrelationForLag = numerator / denominator
End Function 

As a result, you will obtain the scatter chart visualizing your output.

You can use the effective methods mentioned in the article too. Hope this helps!

Regards,
Yousuf Khan Shovon

Hello Roge,

Thanks for your feedback! I understand you want to create a line chart race horizontally. Yes, it is possible to do so using Excel’s built-in animation features, VBA, or Flourish Studio. Though, I would recommend using Flourish Studio because it is so much easier.

To create a line chart race horizontally with Excel data and Flourish Studio, follow these steps:

1. Login into Flourish Studio.
2. Create a new project. Scroll down and select any template.

3. Line up your Excel data and copy.

4. Go to the site again. Click Data and feed your Excel data by pasting.

5. Now, you can preview your data and modify it according to your requirements.
6. Finally, click on Export & Publish.

If you still need to implement this in Excel, post your problem at our Exceldemy Forum with sample data. Our team will reach out to you as soon as possible.

Regards,
Yousuf Khan Shovon

Hello M E,

The above steps are the right ways to show leader lines in the Pie chart when not working. It worked perfectly for me when I manually dragged them but in your case, it did not work probably for these reasons:
1. Best Fit is your selected option.
2. You are not dragging the labels in the correct way.
The leader lines in any chart will only appear if the labels are positioned with Best fit or manually dragged outside of the pie wedges if other options (like Outside End) are selected.
Even then the leader lines appear if the Best fit has to move the label position because they are further from the point they are labeling.
Solution 1:
Leader lines may not appear if Excel with the Best Fit option selected feels they are unimportant.
Excel uses the Best Fit option to arrange the labels without overlapping as best it can. If the Pie wedges are large enough, the labels go to the Inside End. Alternatively, they go to the Outside End to prevent overlaps, in which case the leader lines appear automatically.

Therefore, you sometimes have to manually drag them to appear.
Solution 2:
The correct way to drag (upon Show Leader Lines checked) is to double-click on a data label to select an individual data label. Then, drag the label further from its original point when you see the four headed arrow.

These steps will show you the leader lines in your Pie chart. Further, if you have any difficulties in this case, share your working file here or in our ExcelDemy Forum and we will go through it.

Regards,
Yousuf Khan Shovon

Hello HUONGPT,

Thanks for sharing your experience with us!

The code shared in this article is designed to import data from a Google Sheets web version to an Excel offline version. It uses a Google Sheets key to access the data.

To modify this code for Microsoft 365 Excel online version, you would need to adapt it to use the appropriate URL for your Microsoft 365 Excel file. Microsoft 365 Excel doesn’t use a key like Google Sheets, but rather the URL of the shared document. Use the below VBA code instead:

Sub Import_Sheets_to_Excel()
    Dim QRT As QueryTable, ul As String

    ' Enter the URL of your online Excel
    ul = "https://1drv.ms/x/s!AhXZPAkXAN3YmQlrC8RvdAWRh31H?e=XwaZc4"

    If ActiveSheet.QueryTables.Count > 0 Then ActiveSheet.QueryTables(1).Delete
    ActiveSheet.Cells.Clear

    Set QRT = ActiveSheet.QueryTables.Add(Connection:="URL;" & ul, _
        Destination:=Range("$A$4"))

    With QRT
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .Refresh
    End With
End Sub

You can also import data from online Excel without VBA quite easily. Here is how:

1. When you open your Online Excel file there is an option called Edit.
2. Select Edit in Online Excel >> then it will open in Desktop Excel.
3. Now, you will have all the options. It is as easy as it sounds!

Hope these suggestions help. Keep excelling.

Regards,
Yousuf Khan Shovon

Hello RYAN,

Thank you for sharing your experience.

I believe you want to auto-populate Word documents from multiple Excel data like a purchase form. The reason you were able to display only one item after following the above steps is that the Mailings feature merges through only one entity for each line.

However, it is possible to display every line of the Purchase form using 2 easy steps.
Let’s start creating the dataset as per your requirements:

Afterward, let’s create another dataset in Word and follow the steps 3 to 9 of this article. Thus, we get the first line data according to your dataset. Now, we can toggle through each line data using the toggle buttons.

Or, we can display the desired number of lines in a single Word file.
Go to Finish & Merge >> Edit Individual Documents.

Put the number of lines you want to populate >> OK.

As a result, you get all the lines of data in your Word file.

Regards,
Yousuf Khan Shovon

Hello JOE,

Thank you for sharing your experience with us. Since you have no Bounds command in your Excel 2016 version, let’s find an alternative way. Today, I am going to show you how to zoom your charts using VBA applicable in any Excel version. Let’s dive into it.

1. First, create (+) and (-) buttons in your workbook >> name them Zoom_Button in the NameBox. Or, you can copy them from the attached workbook and paste them into your chart.

2. Enter the below VBA code to your module >> Assign the Zoom_Chart macro in the buttons:
(If you do not understand this step, see this article)
How to Write VBA Code in Excel
The VBA code:

Sub Zoom_Chart()

Dim i As Integer
Dim strButtonName As String
Dim strChartName As String
Dim strZoomState As String
Dim dZoomInWidth As Double
Dim dZoomInHeight As Double
Dim dOutWidth As Double
Dim dOutHeight As Double
Dim rngZoom As Range
Dim rngChart As Range
Dim strZoomInText As String
Dim strZoomOutText As String

        strZoomInText = "+"
        strZoomOutText = "-"
                                              
        dZoomInWidth = 3
        dZoomInHeight = 2
        dOutWidth = 1 / dZoomInWidth
        dOutHeight = 1 / dZoomInHeight
    
    strButtonName = Application.Caller
    
  
    Set rngZoom = Range(ActiveSheet.Shapes(strButtonName).TopLeftCell.Address)
    
    With ActiveSheet
        
        For i = 1 To .Shapes.Count
            strChartName = .Shapes(i).Name
            Set rngChart = Range(.Shapes(i).TopLeftCell.Address)
            
            If Not Intersect(rngZoom, rngChart) Is Nothing Then
                strZoomState = .Shapes(strButtonName).TextFrame.Characters.Text
                With ActiveSheet.Shapes(strChartName)
                    
                    
                    If strZoomState = strZoomInText Then
                        .ZOrder msoBringToFront
                        .ScaleWidth dZoomInWidth, msoFalse, msoScaleFromTopLeft
                        .ScaleHeight dZoomInHeight, msoFalse, msoScaleFromTopLeft
                        ActiveSheet.Shapes(strButtonName).TextFrame.Characters.Text = strZoomOutText
                        ActiveSheet.Shapes(strButtonName).ZOrder msoBringToFront
                   
                    Else
                        .ScaleWidth dOutWidth, msoFalse, msoScaleFromTopLeft
                        .ScaleHeight dOutHeight, msoFalse, msoScaleFromTopLeft
                        ActiveSheet.Shapes(strButtonName).TextFrame.Characters.Text = strZoomInText
                    End If
                End With
                GoTo EndLoop
            End If
        Next i
    End With

EndLoop:

End Sub

3. Click the Plus (+) button to zoom in on your chart.

4. Click the Minus (-) button to zoom out on your chart.

You can set the zoom height and width in the given code in these lines:

dZoomInWidth = 3
dZoomInHeight = 2

Note: Here, 3 means 300% zoom and 2 means 200% zoom.
Download the workbook file here to practice.
Zoom Excel Chart.xlsm
Best Regards,
Yousuf Khan Shovon

Hello MISLED READER,

Thank you for reading our article and your feedback. You have specified an incorrect use of the IPMT function that does not consider the start and end dates. And, you are correct about the fact that the method calculates the interest payable in the 1st period of a 5 year note represented by the hardcoded “1” used in the “PER” section of the formula. The IPMT function returns interest rates based on periods and not specific calendar dates. Thank you for pointing out the article gap to us. We will fix the issue with correct information. For now, we will show you another method here.

You can use the other Excel basic formula to calculate the interest between two dates. Or, enter a custom VBA function as described below.

This function will take the loan details, including start and end dates, as input arguments and return the interest amount.
Here is the syntax and arguments of the CalculateInterest function I have created:

Function CalculateInterest(Principal As Double, AnnualInterestRate As Double, _
    LoanTermInYears As Integer, CompoundingPeriodsPerYear As Integer, _
    StartDate As Date, EndDate As Date) As Double

Here are the steps to implement the function:

1. Save the below VBA code to a Module.

Function CalculateInterest(Principal As Double, AnnualInterestRate As Double, _
    LoanTermInYears As Integer, CompoundingPeriodsPerYear As Integer, _
    StartDate As Date, EndDate As Date) As Double

    Dim MonthlyInterestRate As Double
    Dim TotalPeriods As Integer
    Dim Interest As Double
    
    MonthlyInterestRate = AnnualInterestRate / CompoundingPeriodsPerYear
    
    TotalPeriods = LoanTermInYears * CompoundingPeriodsPerYear
    
    Interest = -Principal * WorksheetFunction.IPmt(MonthlyInterestRate, _
        DateDiff("m", StartDate, EndDate) + 1, TotalPeriods, 1, 0)
    
    CalculateInterest = Interest
End Function

2. Now, enter the VBA function in cell C11 >> press Enter key to get the interest.

This formula calculates the interest between February 22, 2022, and March 24, 2022, based on the provided loan details.

Feel free to let us know your future queries and suggestions as we always appreciate them. Thank you.

Regards,
Yousuf Shovon

Hello Neil,

Yes, there is a way to name the resultant files using Excel VBA. I am modifying the VBA code to split the column into multiple workbooks given above. This code will rename the new files as month names using monthNames () array.

Here is the modified code:

Sub SplitSheet_IntoMultipleWorkbooks_BasedOnColumn_withnames()
    Dim objWorksheet As Excel.Worksheet
    Dim nLastRow, nRow, nNextRow As Integer
    Dim strColumnValue As String
    Dim objDictionary As Object
    Dim varColumnValues As Variant
    Dim varColumnValue As Variant
    Dim objExcelWorkbook As Excel.Workbook
    Dim objSheet As Excel.Worksheet

    Set objWorksheet = ActiveSheet
    nLastRow = objWorksheet.Range("A" & objWorksheet.Rows.Count).End(xlUp).Row

    Set objDictionary = CreateObject("Scripting.Dictionary")

    For nRow = 2 To nLastRow
        strColumnValue = objWorksheet.Range("C" & nRow).Value

        If objDictionary.Exists(strColumnValue) = False Then
            objDictionary.Add strColumnValue, 1
        End If
    Next

    varColumnValues = objDictionary.Keys

    Dim monthNames() As String
    monthNames = Split("January,February,March,April,May,June,July,August,September,October,November,December", ",")

    For i = LBound(varColumnValues) To UBound(varColumnValues)
        varColumnValue = varColumnValues(i)

        Set objExcelWorkbook = Excel.Application.Workbooks.Add
        Set objSheet = objExcelWorkbook.Sheets(1)
        objSheet.Name = monthNames(i Mod 12) & ".xls"

        objWorksheet.Rows(1).EntireRow.Copy
        objSheet.Activate
        objSheet.Range("A1").Select
        objSheet.Paste

        For nRow = 2 To nLastRow
            If CStr(objWorksheet.Range("C" & nRow).Value) = CStr(varColumnValue) Then

                objWorksheet.Rows(nRow).EntireRow.Copy

                nNextRow = objSheet.Range("A" & objSheet.Rows.Count).End(xlUp).Row + 1
                objSheet.Range("A" & nNextRow).Select
                objSheet.Paste
                objSheet.Columns("A:D").AutoFit
            End If
        Next

        ' Save the workbook with the appropriate name
        objExcelWorkbook.SaveAs ThisWorkbook.Path & "\" & monthNames(i Mod 12) & ".xls"
        objExcelWorkbook.Close SaveChanges:=False
    Next
End Sub

Steps:
1. Enter the code in a module >> close the Visual Basic window.
2. Go to the sheet you want to split >> Click on Developer >> Macros.
3. In Macro dialog, select SplitSheet_IntoMultipleWorkbooks_BasedOnColumn_withnames >> Run.

4. Consequently, the new files appear with declared names.

Note: The new files are created in .xls format as you requested. You can change the format in the code if you want.

I hope this was helpful to you. Let me know if you have any further queries.

Best Regards,
Yousuf Shovon

Hello PENELOPE JORDAN,

We are glad these methods were helpful to you. Though it seems, you are facing issues with the INDEX & MATCH method for multiple columns. If you are talking about the #NA! error using the given formula, it can be solved with an easy step. Just fill up the Product column with the proper value first and enter the given formula afterward. Thus, you will obtain the desired result.

Try this way and let us know if it works.

Regards,
Yousuf Khan Shovon

Thank you for your very useful suggestion, VAIBHAV SRIVASTAVA. I understand you suggested summing a text string which is much more relatable to this article. And to do so, you can merge the SUM, VALUE, and TEXTSPLIT functions. Here is the combined formula:

=SUM(VALUE(TEXTSPLIT(A1,"+")))

Don’t hesitate if you have further suggestions for us. Thanks again.

Regards,
Yousuf Khan Shovon

Hello NISHANTH VJ,

Thank you for your concern. Though I am afraid I am having trouble understanding the query clearly. Assuming you wish to modify the formula to account for the cost even in different forms every time you check in, you can use the weighted average cost of goods sold (COGS) method. This method calculates the average cost of all goods sold during a period, taking into account the varying costs of different ingredients.

The formula:

Food Cost Percentage = (Total Cost of Goods Sold ÷ Total Revenue) x 100

To calculate the total cost of goods sold, you can use the following formula:

Total Cost of Goods Sold = (Beginning Inventory + Purchases - Ending Inventory) x Weighted Average Cost per Unit

To calculate the weighted average cost per unit, you can use the following formula:

Weighted Average Cost per Unit = Total Cost of Goods Available for Sale ÷ Total Units Available for Sale

By using this modified formula, you can account for the fluctuations in the cost of food and beverage and calculate your food cost percentage accurately, even if the cost of ingredients varies over time.

Regards,
Yousuf Khan Shovon

Hello FRANK,

Glad to know the basic methods were useful to you. We further appreciate your valuable insights in addition to these methods. We will look into the advanced techniques and add them in the future. Thank you.

Regards,
Yousuf Khan

Hello MONTY,

Thank you so much for your useful suggestion. Our Exceldemy team will include the note in the article as soon as possible. We always appreciate any further concerns or suggestions you may have.

Hi MICHAEL,

The errors you’ve mentioned could indicate that the workbook is either very hidden or corrupted. There are two levels of worksheet hiding: hidden and very hidden. From a user’s perspective, the difference is that a very hidden sheet cannot be made visible through the Excel user interface, and the only way to unhide it is with VBA.

Since you’ve used VBA to work with the worksheets and they still appear hidden, you can try a different VBA code to check if they’re very hidden and unhide them. Here’s the VBA code to unhide all very hidden sheets:

Sub UnhideVeryHiddenSheets()
  Dim wks As Worksheet
  For Each wks In Worksheets
      If wks.Visible = xlSheetVeryHidden Then wks.Visible = xlSheetVisible
  Next
End Sub

Note: This code only works for very hidden sheets, not worksheets that are hidden normally. If you want to display all hidden sheets, use the code below.

Sub UnhideAllSheets()
  Dim wks As Worksheet
  For Each wks In ActiveWorkbook.Worksheets
    wks.Visible = xlSheetVisible
  Next wks
End Sub

Give it a try and let me know if it works.

Best Regards,
Yousuf Khan