Lutfor Rahman Shimanto

About author

Lutfor Rahman Shimanto, BSc, Information Technology, Jahangirnagar University, Bangladesh, has worked with the ExcelDemy project for over a year. He has written 50+ articles and provided solutions of 100+ comments for ExcelDemy. Currently, he works as an Excel & VBA Developer and provides support and solutions in the ExcelDemy Forum. He has solved 100+ ExcelDemy Forum problems. His work and learning interests are in developing various Excel & VBA and Desktop applications. Outside of work, he enjoys Chess a lot.

Designation

Excel & VBA Developer at ExcelDemy in SOFTEKO.

Lives in

Savar, Dhaka, Bangladesh.

Education

B.sc in Institute of Information Technology, Jahangirnagar University, Bangladesh.

Expertise

VBA, C#, .NET, Python, JavaScript, HTML, SQL, and technical content writing.

Experience

  • Technical Content Writing
  • Software Development
    • Accounting System (Using Excel and VBA)
  • Undergraduate Projects
    • Cafeteria Management System (JavaScript, Java, MySQL)
    • A StegoCrypt System (Using the ideas of Cryptography and Steganography)

Summary

  • Currently working as Excel & VBA Developer of ExcelDemy
  • Started technical content writing of Excel & VBA in November 2022

Latest Posts From Lutfor Rahman Shimanto

0
How to Add Prefix to Entire Column in Excel (4 Simple Methods)

Microsoft Excel is undoubtedly one of the most essential and valuable computer programs today. Using Excel's tools and features, we can do any number of ...

0
How to Add Suffix in Excel (4 Easy Ways)

How to Add Suffix in Excel: 4 Easy Ways Our sample dataset has three columns labeled Name, Suffix, and Email. Method 1 - Utilize CONCATENATE Function ...

0
How to Change Font Style in Excel (5 Easy Ways)

Changing font style in Excel is a process that enhances the visual appeal of the data. It includes changing the font name, size, and color as well as making ...

0
How to Delete Hidden Columns in Excel (2 Simple Methods)

Microsoft Excel is undoubtedly one of the most essential and potent computer programs currently available. Using Excel's capabilities and resources, we can do ...

0
How to Convert ZIP Code to Latitude and Longitude in Excel

A ZIP (Zone Improvement Program) Code is a term used in the United States postal code system to define a specific location. Latitudes are horizontal lines that ...

0
How to Subtract Dates to Get Years in Excel – 7 Methods

This is the sample dataset. Method 1 - Using the DATEDIF Function to Subtract Dates to Get Years The DATEDIF Function calculates the number of days ...

0
How to Convert Lat Long to UTM in Excel (with Easy Steps)

We'll use the following dataset, which contains several coordinates with their latitude and longitude values, to convert to UTM values. What Is UTM? ...

0
Automatically Create PowerPoint Slides from Excel (3 Easy Ways)

The following dataset has Names and Areas of sales representatives. We are going to show the Excel data in PowerPoint slides. Method 1 - ...

0
Using an Excel Formula to Work with Positive and Negative Numbers – 6 Examples

This is an overview. Overview of Excel Formulas for Working with Positive and Negative Numbers Example 1 - Converting Negative Numbers to Positive ...

0
How to Set the Same Height and Width in Excel Cells  – 3 Easy Methods

  Method 1. Making Excel Cells Equal by Pixels to Get the Same Height and Width This is the sample dataset. Click on the image for better quality ...

0
How to Use the Excel VLOOKUP Function to Return Multiple Values Vertically – 5 Methods

Problems with VLOOKUP When Returning Multiple Matches: In the dataset below there are three columns: Book Type, Book Name, and Author. To get the names ...

Browsing All Comments By: Lutfor Rahman Shimanto
  1. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Jan 26, 2023 at 2:03 PM

    Hello Raj
    Thank you for reaching out with your comment. You encountered a different result than what was described in the post. I assume that you missed inserting positive numbers in the range C16:C17. However, once those values were included, I discovered a result identical to what is described in the post. Therefore, It’s essential to ensure all intended data is inputted correctly.
    Regards
    Lutfor Rahman Shimanto

  2. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Jan 26, 2023 at 3:31 PM

    Hello Charlotte Fahey
    Thank you for reporting on this fascinating issue. I experience the same problems when a postal code begins with 0, and it is essential to preserve the leading zero when entering data into the system or application.
    The Postal Code column must be formatted as text. Following that, insert the desired data.
    converting into text format
    Now, adhere to the methods mentioned in this article. Ideally, you will observe the desired results.
    The desired output with leading-zero
    Regards
    Lutfor Rahman Shimanto

  3. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Jan 29, 2023 at 11:02 AM

    Thank you for bringing this issue to my attention, William Wyatt. I understand that you have been experiencing difficulties using formulas on cells formatted as fractions in your workbook. I apologize for any confusion or frustration this may have caused you.
    I have gone through this article and did not experience any of your issues. I am using Microsoft 365 to investigate this case. Could you share your workbook with us via email to better understand your situation? I would appreciate it if you could assist me more effectively.
    Regards
    Lutfor Rahman Shimanto

  4. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Jan 31, 2023 at 9:59 AM

    Hello DEBB WOLFE
    We appreciate your comment. I understand your difficulty, and you can avoid the issue by importing the CVS file into the existing worksheet.
    Change the column type in the Power Query window to text, as this article mentions. Next, select the Home tab. Select Close & Load and then Close & Load To at a later time.
    Loading the cvs data
    As a result, the Import Data window will display. Check the Existing Worksheet and then press OK.
    Choosing the Existing worksheet from the Import Data window
    Thus, you will be able to solve the problem.
    Regards
    Lutfor Rahman Shimanto

  5. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Jan 31, 2023 at 4:01 PM

    Hello ARON HOLMBERG
    Thank you for reporting your issues. To count the number of components for each station, when the Station ID may be in one of three different columns, you can use the SUMPRODUCT function.
    =SUMPRODUCT((InputSheet!$B:$B=B5)+(InputSheet!$C:$C=B5)+(InputSheet!$D:$D=B5))
    This formula will test each of the three columns for the station ID and return 1 if it’s present in any of the columns and 0 if it’s not. Then, SUMPRODUCT will sum up the results, giving you the components for the station.
    This solution is more elegant than creating a new column that combines the three columns, as it avoids the need to manipulate the data. If you would like a copy of the illustrated workbook, please click the link provided below this section.
    https://www.exceldemy.com/wp-content/uploads/2023/01/To-Count-the-Number-of-Components-for-Each-Station.xlsx
    Best regards,
    Lutfor Rahman Shimanto

  6. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 23, 2023 at 11:29 AM

    Hello ABIGAYLE PAULSON
    Thank you for reporting on this fascinating issue. I have reviewed this article and found an interesting idea to solve your problem. For illustration, let’s walk through 2nd Example. Filtering the dataset (Sheet3) based on cell values on another sheet (Sheet4). The context filters the dataset for Apple or Tomato products.
    VBA Code:

    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim product1, product2 As Range
            
        If Target.Worksheet.Name = "Sheet4" And (Target.Address = "$C$2" Or Target.Address = "$E$2") Then
            With Worksheets("Sheet4")
                Set product1 = .Range("C2")
                Set product2 = .Range("E2")
            End With
            With Worksheets("Sheet3")
                With .Range("B4:G13")
                    .AutoFilter Field:=3, Criteria1:=product1, Operator:=xlOr, Criteria2:=product2
                End With
            End With
        End If
    End Sub

    If the changed cell is either C2 or E2, the macro will execute and filter the data in “Sheet3” based on the new values in these cells. Note that the Worksheet_Change event must be placed in the code module for the “Sheet4” worksheet.
    The changes we must make to the original code to create the auto-filtering behavior:

      1) We have to add a Private Sub Worksheet_Change(ByVal Target As Range) procedure to the code module for the “Sheet4” worksheet. This procedure runs automatically whenever a cell value is changed on this worksheet.
      2) We can add an If statement to check whether the changed cell is in Sheet4 and C2 or E2. If so, the macro continues executing; if not, it exits without doing anything.
      3) We need to move the Dim statements for product1 and product2 inside the If statement, so they are only declared if the macro is going to run.
      4) The rest of the macro code is the same as the original code, so it will apply the same filter to “Sheet3” based on the new values in “Sheet4” whenever the cell value changes.

    By adding this Worksheet_Change procedure to the code module, the macro will run automatically whenever a change is made to the specified cells in “Sheet4” without manually opening and running the macro.
    Regards
    Lutfor Rahman Shimanto

  7. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Jun 23, 2024 at 4:53 PM

    Hello Chris

    Thanks for your comment! You want to insert multiple leave records at a time.

    I have reviewed your requirements. I am delighted to inform you that I have found an idea that uses an Excel UserForm to fulfil your goal. Please check the following:

    To improve the Excel file, I had to write many lines of code and adjust many features, so I am not explaining how I did this. You can down the improved file from the following link: https://www.exceldemy.com/wp-content/uploads/2024/06/Chris-SOLVED.xlsm

    Hopefully, you will like the idea. Good luck.

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  8. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Jun 20, 2024 at 11:59 AM

    Hello Sherry

    Thanks for reaching out! Providing an ultimate solution without glancing at your Excel file is difficult. However, I suggest several things you may check to ensure the multi-selection drop-down works properly.

    Ensure you have created named ranges for your dataset as described. Make sure that the strDVList variable correctly references your named range. To ensure that the named range CityNames is correctly referenced by strDVList, you can call the InitializeDVList subroutine in the Worksheet_SelectionChange event before it tries to use strDVList.

    So, double-click on the user form and replace the existing code with the following:

    Sub InitializeDVList()
        strDVList = "CityNames"
    End Sub
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Dim rngDV As Range
        Dim oldVal As String
        Dim newVal As String
        Dim strList As String
        On Error Resume Next
        Application.EnableEvents = False
        Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
        On Error GoTo exitHandler
        If rngDV Is Nothing Then GoTo exitHandler
        If Not Intersect(Target, rngDV) Is Nothing Then
            If Target.Validation.Type = 3 Then
                Call InitializeDVList
                frmDVList.Show
            End If
        End If
    exitHandler:
        Application.EnableEvents = True
    End Sub
    
    Private Sub UserForm_Initialize()
        Me.lstDV.RowSource = strDVList
    End Sub
    
    Private Sub cmdOK_Click()
        Dim strSelItems As String
        Dim lCountList As Long
        Dim strSep As String
        Dim strAdd As String
        Dim bDup As Boolean
        On Error Resume Next
        strSep = ", "
        With Me.lstDV
            For lCountList = 0 To .ListCount - 1
                If .Selected(lCountList) Then
                    strAdd = .List(lCountList)
                Else
                    strAdd = ""
                End If
                If strSelItems = "" Then
                    strSelItems = strAdd
                Else
                    If strAdd <> "" Then
                        strSelItems = strSelItems & strSep & strAdd
                    End If
                End If
            Next lCountList
        End With
        With ActiveCell
            If .Value <> "" Then
                .Value = ActiveCell.Value & strSep & strSelItems
            Else
                .Value = strSelItems
            End If
        End With
        Unload Me
    End Sub
    
    Private Sub cmdClose_Click()
        Unload Me
    End Sub

    Hopefully, these ideas will help you overcome your situation. Good luck.

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  9. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Jun 20, 2024 at 10:49 AM

    Hello Cathy Thompson

    Thanks for visiting our blog! We cannot provide an ultimate solution without reviewing your Excel file and being remote. However, you apply several adjustments to the dataset, like checking for non-numeric characters, converting text to numbers, ensuring correct formatting, and checking calculation mode. To clean up your numeric data, you can create a helper numeric column (e.g., real estate tax) and use the formula: =VALUE(TRIM(CLEAN(B1))) Lastly, sum the values in the helper columns using the SUM function.

    Hopefully, these ideas will help. If you still have difficulties, you can share your problem with the ExcelDemy Forum by attaching your Excel file. Good luck.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  10. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Jun 19, 2024 at 4:40 PM

    Hello Onkar

    Thanks for your invaluable feedback!

    When copying data from the Excel files, you wanted a sub-procedure to copy only the header from the first file and skip the header row for the subsequent files. Currently, you are getting the runtime error 1004 with the existing code, which is typically caused by issues with object references or out-of-bound ranges.

    Don’t worry! I have reviewed your problem and improved the existing sub-procedure to fulfil your goal. Please check the following:

    Improved Excel VBA Sub-procedure:

    Sub ExtractMergeDataFromMultipleFiles()
    
        Dim fileDialog As fileDialog
        Dim selectedFiles As FileDialogSelectedItems
        Dim wBook As Workbook
        Dim currentRow As Long
        Dim row As Long
        Dim col As Long
        Dim i As Long
        Dim file As Variant
        Dim firstFile As Boolean
    
        Set fileDialog = Application.fileDialog(msoFileDialogFilePicker)
        fileDialog.AllowMultiSelect = True
        fileDialog.Title = "Select Excel Files"
        fileDialog.Filters.Clear
        fileDialog.Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xlsb; *.xls"
        
        If fileDialog.Show = -1 Then
            Set selectedFiles = fileDialog.SelectedItems
        Else
            Exit Sub
        End If
        
        currentRow = 1
        firstFile = True
        
        For Each file In selectedFiles
            Set wBook = Workbooks.Open(file)
            
            With wBook.Sheets(1)
                If firstFile Then
                    row = 1
                    firstFile = False
                Else
                    row = 2
                End If
                
                Do Until .Cells(row, 1).Value = vbNullString
                    col = .Cells(row, .Columns.Count).End(xlToLeft).Column
                    
                    For i = 1 To col
                        ThisWorkbook.Sheets(1).Cells(currentRow, i).Value = .Cells(row, i).Value
                    Next i
                    
                    row = row + 1
                    currentRow = currentRow + 1
                Loop
            End With
            
            wBook.Close False
        Next file
        
        Set wBook = Nothing
        Set fileDialog = Nothing
        Set selectedFiles = Nothing
    
    End Sub

    Hopefully, with the code, you will not get any runtime error, and you will be able to copy the header only from the first filter, skipping the header row for the other files. I have attached the solution workbook used to solve your problem. You can download it for better understanding. Good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  11. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Jun 19, 2024 at 1:10 PM

    Hello John

    Thanks for your compliments! Your appreciation means a lot to us.

    I have reviewed your requirements. To do so, first, you must create helper cells to store the dates associated with each checkbox when marked TRUE. Next, use the MAX function to find the latest date from these helper cells. Finally, the latest date calculates the new expiration date in A27. Please check the following:

    Follow these steps:

    1. Create helper cells to store the dates when checkboxes are TRUE.
    2. Use a formula to capture these dates:
      B38: =IF($B$38=TRUE, DATE(2024, 2, 2), "")
      B42: =IF($B$42=TRUE, DATE(2024, 2, 10), "")
      //Continue for other checkboxes.
    3. Use the MAX function to find the latest date from these helper cells. In cell B26, apply: =MAX($C$38,$C$42)
    4. Calculate the new expiry date in A27: =IF($B$26<>0, $B$26 + 180, DATE(2024, 7, 13))

    Hopefully, these ideas will help you reach your goal. I have attached the solution workbook as well. Good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  12. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Jun 19, 2024 at 11:10 AM

    Hello Shaz

    Thanks for your question! You want to link checkboxes directly to the same cells where these are presented.

    To do so, insert a check box and edit the text like described here. Next, select the checkbox by holding the Ctrl key. Now, type the cell reference in the formula bar where the checkbox is located. Please check the following:

    You can download the workbook used to solve your problem: https://www.exceldemy.com/wp-content/uploads/2024/06/Shaz-SOLVED.xlsx

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  13. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Jun 13, 2024 at 5:05 PM

    Hello David Wang

    Thanks for your kind words! You are very welcome.

    I have reviewed both of your requirements. These requirements can quickly be developed, and I think they will overcome all your hassles. I have made the necessary changes. Please check the following:

    To fulfil your goal, I had to make many changes to the codes and design, develop several sub-procedures and event procedures, and add the necessary validation. As there are many more things, I am not describing everything here. If you are interested in how I developed such a customized date picker, you can post your queries in the ExcelDemy Forum.

    Hopefully, you have found the solution you were looking for. I have attached the Date Picker file. Good luck.

    DOWNLOAD CUSTOMIZED DATE PICKER

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  14. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Jun 13, 2024 at 12:09 PM

    Hello Lisa Hoffer

    Thanks for visiting our blog and sharing such an interesting question. You wanted an Excel VBA sub-procedure to clear the contents of non-contiguous rows. I have developed such a sub-procedure to fulfil your goal. Please check the following:

    Excel VBA Sub-procedure:

    Sub ClearSpecificRows()
    
        Dim ws As Worksheet
        Set ws = ThisWorkbook.Sheets("Sunday Report")
        
        Dim rowsToClear As Variant
        rowsToClear = Array(4, 7, 10, 14)
        
        Dim i As Integer
        For i = LBound(rowsToClear) To UBound(rowsToClear)
            ws.Rows(rowsToClear(i)).ClearContents
        Next i
        
        MsgBox "Contents cleared for rows 4, 7, 10, and 14 in 'Sunday Report'"
    
    End Sub

    Hopefully, you have found the VBA macro you were looking for. I have attached the workbook used to solve your problem. Good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  15. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Jun 13, 2024 at 11:35 AM

    Hello Robert

    Thanks for your question! You want to calculate the number of weeks between two dates. To do so, you can apply several formulas:

    1. Using NETWORKDAYS: =INT(NETWORKDAYS(B6,C6)/5)
    2. Using DATEDIF: =INT(DATEDIF(B6, C6, "d")/7)
    3. Using Arithmetic: =INT((C6-B6)/7)
    4. Using WEEKNUM: =WEEKNUM(C6)-WEEKNUM(B6)

    Hopefully, you have found the solutions you were looking for. I have attached the solution workbook as well. Good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  16. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Jun 13, 2024 at 10:33 AM

    Dear Jess

    Thanks for your comment! Only leading spaces typically don’t affect the SUM function in most Excel versions. However, leading spaces can indeed cause numbers to be treated as text in some Excel versions like yours. It is great to hear that removing leading spaces resolved the issue for you.

    I am sharing another exciting solution for summing numbers with inconsistent spaces using an Excel formula. Please check the following:

    Excel Formulas (using SUM, VALUE, and SUBSTITUTE functions): =SUM(VALUE(SUBSTITUTE($B$2:$B$7, " ", "")))

    Hopefully, you will like the solution. Good luck.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  17. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Jun 12, 2024 at 4:09 PM

    Hello Michael A. Dunn

    Thanks for visiting our blog and noticing a critical fact. You were right. Sorry for the inconvenience. We have improved all the formulas and modified the article.

    Using the arithmetic formula would be best if you worked with a fixed rate. So, it may provide different results from other procedures. However, all the procedures except for using the arithmetic formula will calculate almost the same result. So, if you do not have a fixed rate, consider applying the formulas Using Nested IF, IFS and SUMPRODUCT functions.

    Regards
    ExcelDemy

  18. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Jun 11, 2024 at 2:31 PM

    Dear Doug

    Thanks for visiting our blog and sharing an exciting problem. You needed help with some Excel VBA sub-procedures to move rows from the Account sheet to the Archive sheet under specific conditions. You want this to happen only when you click a button on the Metrics sheet and turn it on. The conditions are as follows: if a row in the Account sheet has Closed or Archive in its Status column. You also wanted a pop-up to confirm the action before moving a row. Additionally, the row should be deleted from the Account sheet after moving.

    Don’t worry! I have reviewed your requirements and demonstrated the situation within an Excel file with a suitable dataset. I have solved the problem with the help of some Excel VBA sub-procedures. Please check the following:

    Conditional Row Movement and Deletion with User Prompt using Excel VBA

    Excel VBA Sub-procedures:

    Dim isMacroEnabled As Boolean
    
    Sub MoveRowsBasedOnConditions()
        
        If Not isMacroEnabled Then Exit Sub
        
        Dim wsAccount As Worksheet
        Dim wsArchive As Worksheet
        Dim rg As Range
        Dim cell As Range
        Dim lastRowAccount As Long
        Dim lastRowArchive As Long
        Dim userResponse As VbMsgBoxResult
    
        Set wsAccount = Worksheets("Account")
        Set wsArchive = Worksheets("Archive")
    
        userResponse = MsgBox("Are you sure?", vbOKCancel, "Confirm Action")
        
        If userResponse = vbCancel Then Exit Sub
    
        lastRowAccount = wsAccount.Cells(wsAccount.Rows.Count, "A").End(xlUp).Row
        lastRowArchive = wsArchive.Cells(wsArchive.Rows.Count, "A").End(xlUp).Row + 1
    
        Set rg = wsAccount.Range("A2:A" & lastRowAccount)
        
        For Each cell In rg
        
            If cell.Offset(0, 2).Value = "Closed" Or cell.Offset(0, 2).Value = "Archive" Then
                
                cell.EntireRow.Copy Destination:=wsArchive.Range("A" & lastRowArchive)
                lastRowArchive = lastRowArchive + 1
            
            End If
            
        Next cell
        
        Call DeleteRowsBasedOnCellValue
    
    End Sub
    
    Sub DeleteRowsBasedOnCellValue()
    
        Dim ws As Worksheet
        Dim lastRow As Long
        Dim i As Long
        
        Set ws = Worksheets("Account")
        
        lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
        
        For i = lastRow To 2 Step -1
            If ws.Cells(i, 3).Value = "Closed" Or ws.Cells(i, 3).Value = "Archive" Then
                ws.Rows(i).Delete
                
            End If
        Next i
    
    End Sub
    
    
    Sub ToggleMacro()
        
        Dim wsMetrics As Worksheet
        Set wsMetrics = Worksheets("Metrics")
        
        isMacroEnabled = Not isMacroEnabled
        
        If isMacroEnabled Then
            wsMetrics.Buttons("btnToggle").Caption = "Macro is ON"
        Else
            wsMetrics.Buttons("btnToggle").Caption = "Macro is OFF"
        End If
    
    End Sub
    
    Sub CreateButton()
        
        Dim wsMetrics As Worksheet
        Dim btn As Button
    
        Set wsMetrics = Worksheets("Metrics")
    
        Set btn = wsMetrics.Buttons.Add(10, 10, 100, 30)
        btn.Name = "btnToggle"
        btn.Caption = "Macro is OFF"
        btn.OnAction = "ToggleMacro"
        
        isMacroEnabled = False
    
    End Sub

    Hopefully, you have found the solution you were looking for. I have attached the solution workbook as well. Good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  19. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Jun 11, 2024 at 12:23 PM

    Hello there! Thanks for sharing an exciting problem. Since using the conditional formatting option for text alignment is impossible, you can only use an Excel VBA procedure.

    An Excel VBA event procedure was used to apply conditional alignment based on cell value

    Note: The event procedure will trigger when range B2:B7 is changed. If the cell value is Red, it will apply left alignment. For Blue, it will be center alignment; for the other values, it will apply left alignment. You can modify the code based on your needs.

    Excel VBA Event Procedure:

    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Dim cell As Range
    
        Dim targetRange As Range
        Set targetRange = Me.Range("B2:B7")
    
        If Not Intersect(Target, targetRange) Is Nothing Then
            For Each cell In Intersect(Target, targetRange)
    
                If cell.Value = "Red" Then
                    cell.HorizontalAlignment = xlLeft
                ElseIf cell.Value = "Blue" Then
                    cell.HorizontalAlignment = xlCenter
                Else
                    cell.HorizontalAlignment = xlRight
                End If
            Next cell
        End If
    
    End Sub

    Hopefully, the solution will fulfil your goal. Download the attached solution workbook for a better understanding.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  20. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Jun 11, 2024 at 12:05 PM

    Dear, Thanks for pointing out the fact! You are right. The Alignment tab in the Format Cells dialog box is unavailable when setting up conditional formatting rules. So, using the conditional formatting option for text alignment is impossible.

    Don’t worry! There is an idea of using an Excel VBA event procedure. Please check the following:

    An Excel VBA event procedure was used to apply conditional alignment based on cell value

    The event procedure will trigger when range B2:B7 is changed. If the cell value is Red, it will apply left alignment. For Blue, it will be center alignment; for the other values, it will apply left alignment.

    Excel VBA Event Procedure:

    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Dim cell As Range
    
        Dim targetRange As Range
        Set targetRange = Me.Range("B2:B7")
    
        If Not Intersect(Target, targetRange) Is Nothing Then
            For Each cell In Intersect(Target, targetRange)
    
                If cell.Value = "Red" Then
                    cell.HorizontalAlignment = xlLeft
                ElseIf cell.Value = "Blue" Then
                    cell.HorizontalAlignment = xlCenter
                Else
                    cell.HorizontalAlignment = xlRight
                End If
            Next cell
        End If
    
    End Sub

    Hopefully, the solution will fulfil your goal. I have attached the solution workbook as well. Good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  21. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Jun 11, 2024 at 10:44 AM

    Dear, Thanks for your compliment! That’s fantastic to hear! We are glad the template was helpful. Keeping track of sales data can be overwhelming, but a well-designed sales tracker can make a difference.

    Regards
    ExcelDemy

  22. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Jun 10, 2024 at 4:37 PM

    Dear Naqavi

    Thanks for your compliment! Your appreciation means a lot to us.

    Regards
    ExcelDemy

  23. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Jun 9, 2024 at 1:02 PM

    Hello Umar

    Thanks for visiting our blog and sharing an exciting problem. You want to apply Data validation in two columns, with options in the second column dependent on the first column selection.

    Don’t worry! I have demonstrated your situation within an Excel file and solved it. Please check the following:

    You can download the solution workbook for a better understanding: https://www.exceldemy.com/wp-content/uploads/2024/06/Umar-SOLVED.xlsx

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  24. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Jun 9, 2024 at 10:30 AM

    Dear Annie

    Thanks for visiting our blog and sharing an important fact! You are right that the holiday range reference shifts when you copy the formula. You need to use absolute references for the holiday range to fix the issue. Another improvement you can consider is not keeping the holiday list and start date or next working dates in the same column. Assume the start date and next working date data are in columns B and C; the holiday list is in column E.

    Don’t worry! I have demonstrated an improved Excel file to overcome the situation. Please check the following:

    Follow these steps:

    1. Select cell C5.
    2. Insert the following formula: =WORKDAY(B5,7,$E$5:$E$7)
    3. Select the C5:C9 range and press Ctrl+D.
      // After pressing Ctrl+D, you will get the output like the following:

    Hopefully, you have found the solution you were looking for. I have attached the solution workbook as well. Good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  25. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Jun 6, 2024 at 11:45 AM

    Hello Brandy

    Thanks for your wonderful compliment! Your appreciation means a lot to us.

    The Error 13 Type Mismatch in VBA typically occurs when you try to perform an operation on incompatible data types. I have reviewed the code and found that Value2 is used to contain cell values when looping through and comparing with Value1. It seems like some of your values contain errors, which is why it is not possible to use the Len function with this value. So, to avoid this type of situation, you can use IsError to check whether the cells contain any errors or not. If not, perform an operation; otherwise, do nothing.

    You can use the following structure:

    If Not IsError(Rng.Cells(i, j).Value) Then
        'Perform operations
    End If

    Hopefully, you have found the ideas you were looking for. Good luck.

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  26. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Jun 5, 2024 at 9:39 AM

    Hello Mary

    Thanks for visiting our blog and sharing your problem. The issue you are facing is due to the RAND function, which generates a new random number every time the worksheet recalculates.

    To prevent the problem, copy the random values, then use Paste Special and paste them back as Values. The idea should keep your random names stable.

    Regards
    ExcelDemy

  27. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Jun 3, 2024 at 8:37 AM

    Dear, Thanks for your question! Unfortunately, it isn’t possible to disable copying directly within Excel Online. Excel Online offers some control over how users interact with a shared workbook, but it doesn’t provide a direct way to turn off copying explicitly. You can set the workbook to Read Only for users who cannot copy data.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  28. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Jun 2, 2024 at 4:41 PM

    Hello Luke

    Thanks for sharing your problem with such clarity. Based on your requirements, you can use the following Excel VBA Sub-procedure to fulfil your goal.

    Excel VBA Sub-procedure:

    Sub ExcelToCSV()
    
        Dim rng As Range, row As Range, cell As Range
        Dim dqt As String, dlim As String, text As String
        Dim file As Variant
        On Error Resume Next
    
        Set rng = ActiveSheet.Range("A3:B319, D3:F319, I3:I319")
    
        If rng Is Nothing Then Exit Sub
    
        file = Application.GetSaveAsFilename("", "CSV File (*.csv), *.csv")
        dlim = Application.International(xlListSeparator)
        Open file For Output As #1
        For Each row In rng.Rows
            dqt = ""
            For Each cell In row.Cells
    
                If cell.Column = 1 Or cell.Column = 2 Or cell.Column = 4 Or cell.Column = 5 Or cell.Column = 6 Or cell.Column = 9 Then
                    dqt = dqt & """" & cell.Value & """" & dlim
                Else
                    dqt = dqt & cell.Value & dlim
                End If
            Next
            While Right(dqt, 1) = dlim
                dqt = Left(dqt, Len(dqt) - 1)
            Wend
            Print #1, dqt
        Next
        Close #1
        If Err = 0 Then MsgBox "The CSV file has been saved to: " & file, vbInformation
    
    End Sub

    Hopefully, the sub-procedure will be helpful. Stay blessed.

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  29. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Jun 2, 2024 at 10:55 AM

    Hello Luke

    Thanks for reaching out and sharing an exciting problem.

    You want to generate a comma-delimited (.txt) file from an Excel sheet. When saving as CSV, you want to remove the double quotes that Excel automatically adds. The text file should have each data point separated by commas, without any quotes surrounding the text.

    Don’t worry! I have developed an Excel VBA code to help you overcome your situation. Please check the following:

    Excel VBA Sub-procedure:

    Sub ExcelToTXT()
    
      Dim rng, row, cell As Range
      Dim dlim, text As String
      Dim file As Variant
      On Error Resume Next
    
      If ActiveWindow.RangeSelection.Count > 1 Then
        text = ActiveWindow.RangeSelection.AddressLocal
      Else
        text = ActiveSheet.UsedRange.AddressLocal
      End If
      Set rng = Application.InputBox("Please select the data range:", "ExcelDemy.Com", text, , , , , 8)
      If rng Is Nothing Then Exit Sub
    
      file = Application.GetSaveAsFilename(InitialFileName:="", FileFilter:="Text Files (*.txt), *.txt")
    
      dlim = ","
    
      Open file For Output As #1
    
      For Each row In rng.Rows
    
        text = ""
        For Each cell In row.Cells
          text = text & cell.Value & dlim
        Next cell
    
        text = Left(text, Len(text) - Len(dlim))
    
        Print #1, text
      
      Next row
    
      Close #1
    
      If Err = 0 Then MsgBox "The text file has been saved to: " & file, vbInformation, "ExcelDemy.Com"
    
    End Sub

    I hope you have found the solution you were looking for. I have attached the solution workbook. Good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  30. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Jun 2, 2024 at 9:55 AM

    Hello Nancy Cruz

    Thanks for visiting our blog! And also sharing your problem with such clarity. I have reviewed your issue with date formatting and come up with a solution. Please check the following:

    Follow these steps:

    1. Select column A and go to Home.
    2. Under the Cells group, expand Format and click on Format Cells.
      // As a result, the Format Cells window will open up.
    3. In the Format Cells window,
      • Go to Number and click on Custom.
      • Insert the following formatting code in the Type section: mm/dd
      • Hit OK.
        // As a result, the date will be formatted as expected like the following:
    4. Now, select the intended date range and apply the desired sorting option.

    Hopefully, you have found the solution you were looking for. I have attached the solution workbook as well. Good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  31. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Jun 2, 2024 at 9:09 AM

    Hello Pablo

    Thanks for visiting our blog and sharing your queries! You can modify the existing code slightly to update specific columns or rows in Google Sheets from Excel. Follow the previously mentioned steps, ensure you have the necessary permissions for the Google Sheet and adjust these with the VBA code.

    Excel VBA Code:

    Sub UpdateGoogleSheet()
    
        Dim spreadsheetId As String
        Dim rangeName As String
        Dim oAuthApp As Object
        Dim dataToUpdate As Variant
        
        spreadsheetId = "YOUR_SPREADSHEET_ID"
        rangeName = "Sheet1!A1:C10"
        dataToUpdate = Array(Array("Updated Value 1", "Updated Value 2", "Updated Value 3"))
        
        Set oAuthApp = CreateObject("Scripting.Dictionary")
        
        oAuthApp("client_id") = "YOUR_CLIENT_ID"
        oAuthApp("client_secret") = "YOUR_CLIENT_SECRET"
        oAuthApp("auth_uri") = "https://accounts.google.com/o/oauth2/auth"
        oAuthApp("token_uri") = "https://oauth2.googleapis.com/token"
        oAuthApp("scope") = "https://www.googleapis.com/auth/spreadsheets"
        
        Dim accessToken As String
        accessToken = GetAccessToken(oAuthApp)
        
        If accessToken <> "" Then
            Call UpdateGoogleSheetData(accessToken, spreadsheetId, rangeName, dataToUpdate)
        Else
            MsgBox "Authentication failed. Please try again."
        End If
    
    End Sub
    
    Function GetAccessToken(oAuthApp As Object) As String
        
        Dim authUrl As String
        authUrl = oAuthApp("auth_uri") & "?client_id=" & oAuthApp("client_id") & "&redirect_uri=urn:ietf:wg:oauth:2.0:oob&scope=" & oAuthApp("scope") & "&response_type=code"
        
        Call OpenDefaultBrowser(authUrl)
        
        Application.Wait Now + TimeValue("00:00:10")
        
        Dim authCode As String
        authCode = InputBox("Enter the authorization code: ")
        
        Dim postData As String
        postData = "code=" & authCode & "&client_id=" & oAuthApp("client_id") & "&client_secret=" & oAuthApp("client_secret") & "&redirect_uri=urn:ietf:wg:oauth:2.0:oob&grant_type=authorization_code"
        
        Dim oAuthClient As Object
        Set oAuthClient = CreateObject("MSXML2.ServerXMLHTTP.6.0")
        oAuthClient.Open "POST", oAuthApp("token_uri"), False
        oAuthClient.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        oAuthClient.send postData
        
        Dim responseText As String
        responseText = oAuthClient.responseText
        GetAccessToken = GetJsonValue(responseText, "access_token")
    
    End Function
    
    Function GetJsonValue(jsonString As String, key As String) As String
        
        Dim regex As Object
        Set regex = CreateObject("VBScript.RegExp")
        
        regex.Global = True
        regex.MultiLine = False
        regex.IgnoreCase = True
        regex.Pattern = """" & key & """:\s*""(.*?)"""
        
        If regex.Test(jsonString) Then
            GetJsonValue = regex.Execute(jsonString)(0).SubMatches(0)
        Else
            GetJsonValue = ""
        End If
    
    End Function
    
    Sub OpenDefaultBrowser(url As String)
        
        Dim shell As Object
        Set shell = CreateObject("WScript.Shell")
        shell.Run url
    
    End Sub
    
    Sub UpdateGoogleSheetData(accessToken As String, spreadsheetId As String, rangeName As String, dataToUpdate As Variant)
        
        Dim updateUrl As String
        updateUrl = "https://sheets.googleapis.com/v4/spreadsheets/" & spreadsheetId & "/values/" & rangeName & "?valueInputOption=RAW"
        
        Dim jsonBody As String
        jsonBody = "{""range"":""" & rangeName & """,""majorDimension"":""ROWS"",""values"":" & ConvertToJson(dataToUpdate) & "}"
        
        Dim http As Object
        Set http = CreateObject("MSXML2.ServerXMLHTTP.6.0")
        http.Open "PUT", updateUrl, False
        http.setRequestHeader "Authorization", "Bearer " & accessToken
        http.setRequestHeader "Content-Type", "application/json"
        http.send jsonBody
        
        If http.Status = 200 Then
            MsgBox "Data updated successfully!"
        Else
            MsgBox "Failed to update data. Error: " & http.Status & " - " & http.statusText
        End If
    
    End Sub
    
    Function ConvertToJson(dataArray As Variant) As String
        
        Dim json As String
        json = "["
        Dim i As Long, j As Long
        For i = LBound(dataArray, 1) To UBound(dataArray, 1)
            json = json & "["
            For j = LBound(dataArray, 2) To UBound(dataArray, 2)
                json = json & """" & dataArray(i, j) & """"
                If j < UBound(dataArray, 2) Then json = json & ","
            Next j
            json = json & "]"
            If i < UBound(dataArray, 1) Then json = json & ","
        Next i
        json = json & "]"
        ConvertToJson = json
    
    End Function

    Things to keep in mind: To adjust the VBA code, replace YOUR_SPREADSHEET_ID with your actual Google Sheet ID, and replace your YOUR_CLIENT_ID and YOUR_CLIENT_SECRET with the values from your OAuth 2.0 credential JSON file. Adjust the rangeName to specify the exact range (column/row) you want to update in the Google Sheet. Next, modify dataToUpdate to include the data you want to update. After running the VBA code, it will open a browser for you to authenticate with Google; enter the authorization code.

    Hopefully, the code will fulfil your goal. Good luck.

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  32. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto May 30, 2024 at 9:48 AM

    Hello Rahul Dixit

    Thanks for visiting our blog and sharing such an exciting problem! I have reviewed your problem and come up with an Excel VBA User-defined function. Please check the following:

    Excel VBA Code:

    Function ConvertNumbersToLetters(inputStr As String) As String
    
        Dim numArray() As String
        Dim resultArray() As String
        Dim i As Integer
        Dim currentNum As String
        Dim convertedNum As String
        
        numArray = Split(inputStr, ",")
        ReDim resultArray(LBound(numArray) To UBound(numArray))
        
        For i = LBound(numArray) To UBound(numArray)
            currentNum = Trim(numArray(i))
            convertedNum = ""
            Dim j As Integer
            Dim currentChar As String
            
            For j = 1 To Len(currentNum)
                currentChar = Mid(currentNum, j, 1)
                Select Case currentChar
                    Case "1"
                        convertedNum = convertedNum & "A"
                    Case "2"
                        convertedNum = convertedNum & "B"
                    Case "3"
                        convertedNum = convertedNum & "C"
                    Case "4"
                        convertedNum = convertedNum & "D"
                    Case "5"
                        convertedNum = convertedNum & "E"
                    Case "6"
                        convertedNum = convertedNum & "F"
                    Case "7"
                        convertedNum = convertedNum & "G"
                    Case "8"
                        convertedNum = convertedNum & "H"
                    Case "9"
                        convertedNum = convertedNum & "I"
                    Case "0"
                        convertedNum = convertedNum & "O"
                    Case Else
                        convertedNum = convertedNum & currentChar
                End Select
            Next j
            
            resultArray(i) = convertedNum
        Next i
        
        ConvertNumbersToLetters = Join(resultArray, ", ")
    
    End Function

    Hopefully, you have found the solution you were looking for. I have attached the solution workbook as well; good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  33. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto May 29, 2024 at 3:51 PM

    Hello, Rekayasa Perangkat Lunak Aplikasi!

    Thank you for your question. The main purpose of the Excel sales template provided in the article is to facilitate the recording and analysis of sales data for any merchandising business. Also, this comprehensive template includes various sections, such as a sales summary, a sales recording table, sales performance analyses, and a sales plan template. It helps businesses track sales transactions, compare planned versus actual sales, and analyze overall sales performance, enabling more informed decision-making and strategic planning.

    If you have any further questions about the article or the templates, feel free to ask!

    Regards
    ExcelDemy

  34. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto May 27, 2024 at 1:01 PM

    Dear Jon Peltier

    Thanks for your invaluable feedback and suggestions! You are correct about the positive percentage error bars being inserted incorrectly. Based on your suggestions, we have updated the article section.

    Regards
    ExcelDemy

  35. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto May 26, 2024 at 9:40 AM

    Hello Parvez Alam

    Thanks for the compliment! You can include the “RefreshAll” command to refresh all Power Query connections. Don’t worry! I have improved the code a bit to fulfil your goal.

    Excel VBA Code:

    Public Sub AutoRefreshFolder()
    
        Dim mrf As Object
        Dim mfolder As Object
        Dim mfile As Object
        
        mPath = "C:\Users\Alok Paul\Desktop\Softeko\Auto Refresh Excel File Without Opening\"
        
        Set mrf = CreateObject("Scripting.FileSystemObject")
        Set mfolder = mrf.GetFolder(mPath)
        
        With Application
            .DisplayAlerts = False
            .ScreenUpdating = False
            .EnableEvents = False
            .AskToUpdateLinks = False
        End With
        
        For Each mfile In mfolder.Files
            If Right(mfile.Name, 4) = "xlsx" Or Right(mfile.Name, 3) = "xls" Then
                Workbooks.Open mPath & mfile.Name
                ActiveWorkbook.RefreshAll
                ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
                ActiveWorkbook.Close True
            End If
        Next
        
        With Application
            .DisplayAlerts = True
            .ScreenUpdating = True
            .EnableEvents = True
            .AskToUpdateLinks = True
        End With
    
    End Sub

    I hope you have found the code you were looking for. Good luck.

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  36. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto May 26, 2024 at 9:12 AM

    Hello Vickie

    Thanks for visiting our blog and sharing an exciting problem with such clarity. I have reviewed your situation and developed two sub-procedures to fulfil your goal. Your problem is about automating email alerts in Excel based on conditional formatting for expiry dates of lifting equipment test certificates.

    Don’t worry! I have demonstrated it in an Excel workbook. Please check the following:

    Automate email alerts in Excel based on conditional formatting for expiry dates of lifting equipment test certificates

    Excel VBA Sub-procedure:

    Sub SendEmailAlerts()
    
        Dim ws As Worksheet
        Dim cell As Range
        Dim expiryDate As Date
        Dim today As Date
        Dim emailBody As String
        Dim OutApp As Object
        Dim OutMail As Object
    
        Set ws = ThisWorkbook.Sheets("Sheet1")
        today = Date
    
        Set OutApp = CreateObject("Outlook.Application")
        OutApp.Session.Logon
    
        For Each cell In ws.Range("B2:B" & ws.Cells(ws.Rows.Count, "B").End(xlUp).Row)
            If IsDate(cell.Value) Then
                expiryDate = cell.Value
                If expiryDate <= today Then
                    emailBody = "The test certificate for " & cell.Offset(0, -1).Value & " has expired on " & expiryDate & "."
                    Call SendEmail(OutApp, emailBody, "Expiry Alert: Red")
                ElseIf expiryDate <= today + 30 Then
                    emailBody = "The test certificate for " & cell.Offset(0, -1).Value & " will expire on " & expiryDate & "."
                    Call SendEmail(OutApp, emailBody, "Expiry Alert: Yellow")
                End If
            End If
        Next cell
    
        Set OutMail = Nothing
        Set OutApp = Nothing
    
    End Sub
    
    Sub SendEmail(OutApp As Object, emailBody As String, subject As String)
        Dim OutMail As Object
        Set OutMail = OutApp.CreateItem(0)
        
        With OutMail
            .To = "[email protected]"
            .subject = subject
            .HTMLBody = "<p>" & emailBody & "</p>"
            .display
            '.Send 'To send emails, uncomment the ".Send" command.
        End With
    End Sub

    I hope you have found the sub-procedures you were looking for. I have attached the solution workbook as well. Good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  37. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto May 20, 2024 at 1:02 PM

    Hello Mohammed

    Thanks for sharing your problem! However, we can not accurately get the corresponding latitude and longitude values using only the UTM Easting and UTM Northing. We also need UTM Zone, Easting, and Northing values to get Lat and Long values.

    For your address, the UTM Zone would be 39Z. Let’s say your UTM Easting, UTM Northing, and UTM Zone are 585302.3, 27464684.41, and 39Z. Using this UTM information, you can get the corresponding Latitude and Longitude. To achieve your goal, I have developed some Excel VBA User-defined functions.

    Follow these steps:

    1. Press Alt+F11 to open the VBA Editor.
    2. Click on Insert followed by Module.
    3. Paste the following code in the module and save it:
      Option Explicit
      
      Private Const WGS84_A As Double = 6378137#
      Private Const WGS84_E As Double = 0.081819190842622
      
      Function UTMToLatLong(Easting As Double, Northing As Double, Zone As String) As Variant
          
          Dim zoneNumber As Integer
          Dim zoneLetter As String
          Dim latitude As Double
          Dim longitude As Double
          Dim result(1 To 2) As Double
          
          zoneNumber = Val(Left(Zone, Len(Zone) - 1))
          zoneLetter = Right(Zone, 1)
          
          Call ConvertUTMToLatLon(Easting, Northing, zoneNumber, zoneLetter, latitude, longitude)
          
          result(1) = latitude
          result(2) = longitude
          
          UTMToLatLong = result
      
      End Function
      
      Sub ConvertUTMToLatLon(Easting As Double, Northing As Double, zoneNumber As Integer, zoneLetter As String, ByRef latitude As Double, ByRef longitude As Double)
          
          Dim k0 As Double
          k0 = 0.9996
          
          Dim E As Double, N As Double
          Dim A As Double, eccSquared As Double, eccPrimeSquared As Double
          Dim M As Double, mu As Double
          Dim e1 As Double, J1 As Double, J2 As Double, J3 As Double, J4 As Double, J5 As Double
          Dim FPhi1 As Double, C1 As Double, T1 As Double, R1 As Double, N1 As Double, D As Double
          
          E = Easting - 500000#
          If UCase(zoneLetter) < "N" Then
              N = Northing - 10000000#
          Else
              N = Northing
          End If
          
          A = WGS84_A
          eccSquared = WGS84_E ^ 2
          eccPrimeSquared = eccSquared / (1 - eccSquared)
          
          M = N / k0
          mu = M / (A * (1 - eccSquared / 4 - 3 * eccSquared ^ 2 / 64 - 5 * eccSquared ^ 3 / 256))
          
          e1 = (1 - Sqr(1 - eccSquared)) / (1 + Sqr(1 - eccSquared))
          
          J1 = 3 * e1 / 2 - 27 * e1 ^ 3 / 32
          J2 = 21 * e1 ^ 2 / 16 - 55 * e1 ^ 4 / 32
          J3 = 151 * e1 ^ 3 / 96
          J4 = 1097 * e1 ^ 4 / 512
          
          FPhi1 = mu + J1 * Sin(2 * mu) + J2 * Sin(4 * mu) + J3 * Sin(6 * mu) + J4 * Sin(8 * mu)
          
          C1 = eccPrimeSquared * Cos(FPhi1) ^ 2
          T1 = Tan(FPhi1) ^ 2
          R1 = A * (1 - eccSquared) / (1 - eccSquared * Sin(FPhi1) ^ 2) ^ 1.5
          N1 = A / Sqr(1 - eccSquared * Sin(FPhi1) ^ 2)
          D = E / (N1 * k0)
          
          latitude = FPhi1 - (N1 * Tan(FPhi1) / R1) * (D ^ 2 / 2 - (5 + 3 * T1 + 10 * C1 - 4 * C1 ^ 2 - 9 * eccPrimeSquared) * D ^ 4 / 24 + (61 + 90 * T1 + 298 * C1 + 45 * T1 ^ 2 - 252 * eccPrimeSquared - 3 * C1 ^ 2) * D ^ 6 / 720)
          latitude = latitude * 180 / Application.WorksheetFunction.Pi()
          
          longitude = (D - (1 + 2 * T1 + C1) * D ^ 3 / 6 + (5 - 2 * C1 + 28 * T1 - 3 * C1 ^ 2 + 8 * eccPrimeSquared + 24 * T1 ^ 2) * D ^ 5 / 120) / Cos(FPhi1)
          longitude = zoneNumber * 6 - 183 + longitude * 180 / Application.WorksheetFunction.Pi()
      
      End Sub

    4. Return to the sheet and choose an empty cell.
    5. Apply the following formula: =UTMToLatLong(B6, C6, D6)
    6. Hit Enter.

    Hopefully, these user-defined functions will help. I have attached the solution workbook as well.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  38. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto May 20, 2024 at 12:46 PM

    Hello David

    Thanks for visiting our blog! For your address, the UTM Zone would be 32U. Let’s say your UTM Easting, UTM Northing, and UTM Zone are 691609.5, 5334764.67, and 32U. Using this UTM information, you can get the corresponding Latitude and Longitude. To achieve your goal, I have developed some Excel VBA User-defined functions.

    Follow these steps:

    1. Press Alt+F11 to open the VBA Editor.
    2. Click on Insert followed by Module.
    3. Paste the following code in the module and save it:
      Option Explicit
      
      Private Const WGS84_A As Double = 6378137#
      Private Const WGS84_E As Double = 0.081819190842622
      
      Function UTMToLatLong(Easting As Double, Northing As Double, Zone As String) As Variant
          
          Dim zoneNumber As Integer
          Dim zoneLetter As String
          Dim latitude As Double
          Dim longitude As Double
          Dim result(1 To 2) As Double
          
          zoneNumber = Val(Left(Zone, Len(Zone) - 1))
          zoneLetter = Right(Zone, 1)
          
          Call ConvertUTMToLatLon(Easting, Northing, zoneNumber, zoneLetter, latitude, longitude)
          
          result(1) = latitude
          result(2) = longitude
          
          UTMToLatLong = result
      
      End Function
      
      Sub ConvertUTMToLatLon(Easting As Double, Northing As Double, zoneNumber As Integer, zoneLetter As String, ByRef latitude As Double, ByRef longitude As Double)
          
          Dim k0 As Double
          k0 = 0.9996
          
          Dim E As Double, N As Double
          Dim A As Double, eccSquared As Double, eccPrimeSquared As Double
          Dim M As Double, mu As Double
          Dim e1 As Double, J1 As Double, J2 As Double, J3 As Double, J4 As Double, J5 As Double
          Dim FPhi1 As Double, C1 As Double, T1 As Double, R1 As Double, N1 As Double, D As Double
          
          E = Easting - 500000#
          If UCase(zoneLetter) < "N" Then
              N = Northing - 10000000#
          Else
              N = Northing
          End If
          
          A = WGS84_A
          eccSquared = WGS84_E ^ 2
          eccPrimeSquared = eccSquared / (1 - eccSquared)
          
          M = N / k0
          mu = M / (A * (1 - eccSquared / 4 - 3 * eccSquared ^ 2 / 64 - 5 * eccSquared ^ 3 / 256))
          
          e1 = (1 - Sqr(1 - eccSquared)) / (1 + Sqr(1 - eccSquared))
          
          J1 = 3 * e1 / 2 - 27 * e1 ^ 3 / 32
          J2 = 21 * e1 ^ 2 / 16 - 55 * e1 ^ 4 / 32
          J3 = 151 * e1 ^ 3 / 96
          J4 = 1097 * e1 ^ 4 / 512
          
          FPhi1 = mu + J1 * Sin(2 * mu) + J2 * Sin(4 * mu) + J3 * Sin(6 * mu) + J4 * Sin(8 * mu)
          
          C1 = eccPrimeSquared * Cos(FPhi1) ^ 2
          T1 = Tan(FPhi1) ^ 2
          R1 = A * (1 - eccSquared) / (1 - eccSquared * Sin(FPhi1) ^ 2) ^ 1.5
          N1 = A / Sqr(1 - eccSquared * Sin(FPhi1) ^ 2)
          D = E / (N1 * k0)
          
          latitude = FPhi1 - (N1 * Tan(FPhi1) / R1) * (D ^ 2 / 2 - (5 + 3 * T1 + 10 * C1 - 4 * C1 ^ 2 - 9 * eccPrimeSquared) * D ^ 4 / 24 + (61 + 90 * T1 + 298 * C1 + 45 * T1 ^ 2 - 252 * eccPrimeSquared - 3 * C1 ^ 2) * D ^ 6 / 720)
          latitude = latitude * 180 / Application.WorksheetFunction.Pi()
          
          longitude = (D - (1 + 2 * T1 + C1) * D ^ 3 / 6 + (5 - 2 * C1 + 28 * T1 - 3 * C1 ^ 2 + 8 * eccPrimeSquared + 24 * T1 ^ 2) * D ^ 5 / 120) / Cos(FPhi1)
          longitude = zoneNumber * 6 - 183 + longitude * 180 / Application.WorksheetFunction.Pi()
      
      End Sub

    4. Return to the sheet and choose an empty cell.
    5. Apply the following formula: =UTMToLatLong(B6, C6, D6)
    6. Hit Enter.

    Hopefully, these user-defined functions will help you reach your goal. I have attached the solution workbook as well. Good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  39. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto May 19, 2024 at 12:20 PM

    Dear Sanjeev

    Thanks for visiting our blog and sharing your problem. After adding the desired rows, you must drag the Fill Handle icon to copy the existing formulas for new employees. We have improved the file and made the necessary formula adjustments based on your goal.

    SOLUTION Overview:

    You can download the solution file: https://www.exceldemy.com/wp-content/uploads/2024/05/Sanjeev-Fernandes-SOLVED.xlsx

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  40. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto May 16, 2024 at 1:23 PM

    Dear Jen

    Thanks for explaining your requirements clearly. I have reviewed the existing user-defined function and tried to improve it to achieve your goal. The improved user-defined function will try to populate house numbers, roads, cities, states and zip codes by taking Lat and Long values; if any item is missing, it will display an extension like Incomplete!

    Follow these steps:

    1. Click on the Developer tab, followed by Visual Basic.
    2. Next, click on Insert, followed by Module.
    3. Paste the following code in the module and save it:
      Option Explicit
      
      Function ReverseGeocoder(lati As Double, longi As Double) As String
      
          On Error GoTo ErrorHandler
          Dim xD As New MSXML2.DOMDocument
          Dim URL As String
          Dim house As String
          Dim road As String
          Dim city As String
          Dim state As String
          Dim postcode As String
          
          xD.async = False
          URL = "https://nominatim.openstreetmap.org/reverse?lat=" + CStr(lati) + "&lon=" + CStr(longi)
          xD.Load (URL)
          
          If xD.parseError.ErrorCode <> 0 Then
              ReverseGeocoder = "Error: " & xD.parseError.reason
          Else
              xD.SetProperty "SelectionLanguage", "XPath"
              
              Dim houseNode As MSXML2.IXMLDOMElement
              Dim roadNode As MSXML2.IXMLDOMElement
              Dim cityNode As MSXML2.IXMLDOMElement
              Dim stateNode As MSXML2.IXMLDOMElement
              Dim postcodeNode As MSXML2.IXMLDOMElement
              
              Set houseNode = xD.SelectSingleNode("//addressparts/house_number")
              Set roadNode = xD.SelectSingleNode("//addressparts/road")
              Set cityNode = xD.SelectSingleNode("//addressparts/city")
              Set stateNode = xD.SelectSingleNode("//addressparts/state")
              Set postcodeNode = xD.SelectSingleNode("//addressparts/postcode")
              
              If Not houseNode Is Nothing Then
                  house = houseNode.Text
              End If
              
              If Not roadNode Is Nothing Then
                  road = roadNode.Text
              End If
              
              If Not cityNode Is Nothing Then
                  city = cityNode.Text
              End If
              
              If Not stateNode Is Nothing Then
                  state = stateNode.Text
              End If
              
              If Not postcodeNode Is Nothing Then
                  postcode = postcodeNode.Text
              End If
              
              If house = "" Or road = "" Or city = "" Or state = "" Or postcode = "" Then
                  ReverseGeocoder = house & " " & road & ", " & city & ", " & state & ", " & postcode & " [Incomplete!]"
              Else
                  ReverseGeocoder = house & " " & road & ", " & city & ", " & state & ", " & postcode
              End If
          End If
          
          Exit Function
      
      ErrorHandler:
          ReverseGeocoder = "Error: " & Err.Description
          
      End Function

    4. Return to the sheet and choose an empty cell.
    5. Apply the following formula: =ReverseGeocoder(B5,C5)
    6. Drag the Fill Handle icon to copy the formula down.

    I hope you have found the user-defined function of reverse geocoding you were looking for. I have attached the solution workbook as well. Good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  41. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto May 16, 2024 at 10:12 AM

    Hello Watt

    Thanks for visiting our blog and sharing your queries. I have reviewed your requirements. Unfortunately, you cannot directly use any formulas to add a hidden apostrophe. Formulas can only output visible characters; not even a User-defined function can do that. So, you can type the apostrophe before the number or text to do so, though it is very exhausting when it comes to lots of cells.

    Don’t worry! I have developed a sub-procedure that will add a hidden apostrophe to cells in a selected range without displaying it with one click.

    SOLUTION Overview:

    Adding a hidden apostrophe to cells in a selected range without displaying it

    Excel VBA Sub-procedure:

    Sub AddHiddenApostrophe()
    
        Dim cell As Range
        For Each cell In Selection
            If Not IsEmpty(cell) Then
                cell.value = "'" & cell.value
            End If
        Next cell
    
    End Sub

    Hopefully, you will find the solution helpful. I have attached the solution workbook as well; good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  42. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto May 15, 2024 at 12:52 PM

    Dear Iona

    Thanks for visiting our blog and sharing your requirements! I have reviewed your goal and created an Excel VBA Event procedure (assuming your dates are in column A).

    Excel VBA Event Procedure:

    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Dim cell As Range
    
        If Not Intersect(Target, Range("A:A")) Is Nothing Then
            For Each cell In Target
                If cell.Value < (Date - 7) And cell.Interior.Color <> RGB(0, 255, 0) Then
                    cell.Font.Color = RGB(255, 0, 0)
                ElseIf cell.Interior.Color = RGB(0, 255, 0) Then
                    cell.Font.Color = RGB(0, 0, 0)
                End If
            Next cell
        End If
        
    End Sub

    Right-click on the sheet name tab, paste the given code into the sheet module and save it. Hopefully, the idea will fulfil your goal; good luck.

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  43. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto May 12, 2024 at 11:32 AM

    Hello James

    Thanks for your compliment! We are glad these ideas helped you a lot.

    You are facing difficulties in excluding blank cells that hold a formula from the print area. The following code can give you ideas; here, the HasFormula property is used, along with checking whether it is empty or not.

    Excel VBA Code:

    Sub ExcludingBlankCellsHoldingAFormula()
    
        Dim sht As Worksheet
        Set sht = Worksheets("Sheet1")
        
        Dim lastRow As Long
        lastRow = sht.Cells(sht.Rows.Count, "B").End(xlUp).Row
        
        Dim loopRange As Range
        Set loopRange = sht.Range("B3:B" & lastRow)
        
        Dim printRange As Range
        
        Dim cell As Range
        For Each cell In loopRange
            If cell.Value <> "" And cell.HasFormula Then
                If printRange Is Nothing Then
                    Set printRange = cell
                Else
                    Set printRange = Union(printRange, cell)
                End If
            End If
        Next cell
        
        If Not printRange Is Nothing Then
            sht.PageSetup.PrintArea = printRange.Address
        Else
            MsgBox "No printable area found.", vbExclamation
            Exit Sub
        End If
        
        With sht.PageSetup
            .LeftMargin = Application.InchesToPoints(1)
            .RightMargin = Application.InchesToPoints(1)
            .TopMargin = Application.InchesToPoints(1)
            .BottomMargin = Application.InchesToPoints(1)
            .Orientation = xlPortrait
            .CenterHorizontally = True
            .FitToPagesWide = 1
            .FitToPagesTall = 1
            .Zoom = False
        End With
        
        ActiveSheet.PrintPreview
    
    End Sub

    I hope the ideas will help you exclude blank cells holding a formula. Good luck.

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  44. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto May 9, 2024 at 11:32 AM

    Dear Abinsh

    Thanks for sharing your requirements. You want to convert numbers to words in Qatar Riyal. Don’t worry! I have modified the previously given code to fulfil your goal.

    Excel VBA User-Defined Function:

    Function AdvancedWord(SNum As String)
    
        Dim zDPInt As Integer
        Dim zArrPlace As Variant
        Dim zRStr_Paisas As String
        Dim zNumStr As String
        Dim zP As Integer
        Dim zTemp As String
        Dim zStrTemp As String
        Dim zRStr As String
        Dim zBp As Integer
    
        zArrPlace = Array("", "", " Thousand ", " Lacs ", " Crores ", " Trillion ", "", "", "", "")
    
        On Error Resume Next
    
        If SNum = "" Then
            word = ""
            Exit Function
        End If
    
        zNumStr = Trim(Str(SNum))
    
        If zNumStr = "" Then
            word = ""
            Exit Function
        End If
    
        zRStr = ""
        zBp = 0
    
        If (zNumStr > 999999999.99) Then
            word = "Digit exceeds Maximum limit"
            Exit Function
        End If
    
        zDPInt = InStr(zNumStr, ".")
    
        If zDPInt > 0 Then
            If (Len(zNumStr) - zDPInt) = 1 Then
                zRStr_Paisas = word_GetT(Left(Mid(zNumStr, zDPInt + 1) & "0", 2))
            ElseIf (Len(zNumStr) - zDPInt) > 1 Then
                zRStr_Paisas = word_GetT(Left(Mid(zNumStr, zDPInt + 1), 2))
            End If
            zNumStr = Trim(Left(zNumStr, zDPInt - 1))
        End If
    
        zP = 1
    
        Do While zNumStr <> ""
            If (zP >= 2) Then
                zTemp = Right(zNumStr, 2)
            Else
                If (Len(zNumStr) = 2) Then
                    zTemp = Right(zNumStr, 2)
                ElseIf (Len(zNumStr) = 1) Then
                    zTemp = Right(zNumStr, 1)
                Else
                    zTemp = Right(zNumStr, 3)
                End If
            End If
    
            zStrTemp = ""
    
            If Val(zTemp) > 99 Then
                zStrTemp = word_GetH(Right(zTemp, 3), zBp)
                If Right(Trim(zStrTemp), 3) <> "Lac" Then
                    zBp = zBp + 1
                End If
            ElseIf Val(zTemp) <= 99 And Val(zTemp) > 9 Then
                zStrTemp = word_GetT(Right(zTemp, 2))
            ElseIf Val(zTemp) < 10 Then
                zStrTemp = word_GetD(Right(zTemp, 2))
            End If
    
            If zStrTemp <> "" Then
                zRStr = zStrTemp & zArrPlace(zP) & zRStr
            End If
    
            If zP = 2 Then
                If Len(zNumStr) = 1 Then
                    zNumStr = ""
                Else
                    zNumStr = Left(zNumStr, Len(zNumStr) - 2)
                End If
            ElseIf zP = 3 Then
                If Len(zNumStr) >= 3 Then
                    zNumStr = Left(zNumStr, Len(zNumStr) - 2)
                Else
                    zNumStr = ""
                End If
            ElseIf zP = 4 Then
                zNumStr = ""
            Else
                If Len(zNumStr) <= 2 Then
                    zNumStr = ""
                Else
                    zNumStr = Left(zNumStr, Len(zNumStr) - 3)
                End If
            End If
    
            zP = zP + 1
        Loop
    
        If zRStr = "" Then
            zRStr = "No Qatar Riyals"
        Else
            zRStr = " Qatar Riyals " & zRStr
        End If
    
        If zRStr_Paisas <> "" Then
            zRStr_Paisas = " and " & zRStr_Paisas & " Dirhams"
        End If
    
        AdvancedWord = zRStr & zRStr_Paisas & " Only"
    End Function
    
    Function word_GetH(zStrH As String, zBp As Integer)
        Dim zRStr As String
    
        If Val(zStrH) < 1 Then
            word_GetH = ""
            Exit Function
        Else
            zStrH = Right("000" & zStrH, 3)
            If Mid(zStrH, 1, 1) <> "0" Then
                If (zBp > 0) Then
                    zRStr = word_GetD(Mid(zStrH, 1, 1)) & " Lac "
                Else
                    zRStr = word_GetD(Mid(zStrH, 1, 1)) & " Hundred "
                End If
            End If
    
            If Mid(zStrH, 2, 1) <> "0" Then
                zRStr = zRStr & word_GetT(Mid(zStrH, 2))
            Else
                zRStr = zRStr & word_GetD(Mid(zStrH, 3))
            End If
        End If
    
        word_GetH = zRStr
    End Function
    
    Function word_GetT(zTStr As String)
        Dim zTArr1 As Variant
        Dim zTArr2 As Variant
        Dim zRStr As String
    
        zTArr1 = Array("Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
        zTArr2 = Array("", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
        Result = ""
    
        If Val(Left(zTStr, 1)) = 1 Then
            zRStr = zTArr1(Val(Mid(zTStr, 2, 1)))
        Else
            If Val(Left(zTStr, 1)) > 0 Then
                zRStr = zTArr2(Val(Left(zTStr, 1)) - 1)
            End If
            zRStr = zRStr & word_GetD(Right(zTStr, 1))
        End If
    
        word_GetT = zRStr
    End Function
    
    Function word_GetD(zDStr As String)
        Dim zArr_1() As Variant
    
        zArr_1 = Array(" One", " Two", " Three", " Four", " Five", " Six", " Seven", " Eight", " Nine", "")
    
        If Val(zDStr) > 0 Then
            word_GetD = zArr_1(Val(zDStr) - 1)
        Else
            word_GetD = ""
        End If
    End Function
    

    I hope you have found the solution you were looking for. I have attached the solution workbook. Good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards

    Lutfor Rahman Shimanto

    Excel & VBA Developer

    ExcelDemy

  45. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto May 8, 2024 at 4:41 PM

    Hi Diw

    Thanks for thanking me! All three ideas clear the Excel memory cache. However, they differ in the approaches and memory they target.

    1. Clearing PivotCache Memory: It clears the cache specifically associated with PivotTables.
    2. Assigning Nothing Literal to Objects: You can apply it to any objects (such as ranges, shapes, arrays, variables, etc.) you’re done working with.
    3. Assign Zero to RecentFiles Properties: This clears the list of recently opened files.

    So, it would be better to clear PivotCache Memory for heavy workbooks. You can apply Nothing Literal to any unwanted object for general memory cleanup. For a slight memory boost, you can assign zero to RecentFile Properties.

    I hope these ideas will help you. Thanks once again for visiting our blog. And good luck.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  46. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto May 8, 2024 at 11:46 AM

    Hello Fazlay Rabby

    Thanks for your compliment! You are very welcome. We are glad that you found the article helpful.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  47. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto May 8, 2024 at 11:35 AM

    Hello Barry

    Thanks for your compliment. Your appreciation means a lot to us. Thanks once again for sharing an exciting problem.

    I have reviewed your requirements. You wanted to place small rectangles at each of the 12 positions on a clock face. Besides, each shape will be placed at an angle of 30 degrees; more than its neighbor. Don’t worry! I have come up with a sub-procedure and a user-defined function to fulfill your goal.

    SOLUTION Overview:

    Excel VBA Sub-procedure:

    Sub PlaceRectanglesOnClockFace()
    
        Dim centerX As Double
        Dim centerY As Double
        Dim radius As Double
        Dim angleIncrement As Double
        Dim angle As Double
        Dim rectWidth As Double
        Dim rectHeight As Double
        Dim rect As Shape
            
        centerX = 410
        centerY = 110
        
        radius = 100
        
        rectWidth = 20
        rectHeight = 10
        
        angleIncrement = 30
    
        angle = 0
    
        For i = 1 To 12
    
            Dim rectLeft As Double
            Dim rectTop As Double
            rectLeft = centerX + radius * Cos(DegreesToRadians(angle)) - rectWidth / 2
            rectTop = centerY - radius * Sin(DegreesToRadians(angle)) - rectHeight / 2
            
            Set rect = ActiveSheet.Shapes.AddShape(msoShapeRectangle, rectLeft, rectTop, rectWidth, rectHeight)
            rect.Name = "Rect" & i
            
            angle = angle + angleIncrement
        Next i
    
    End Sub
    
    Function DegreesToRadians(degrees As Double) As Double
        DegreesToRadians = degrees * WorksheetFunction.Pi / 180
    End Function

    I hope you have found the solution, you were looking for. I have attached the solution workbook as well; good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  48. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto May 7, 2024 at 12:43 PM

    Hi Nat

    Thanks for your patience and feedback. We apologize for any confusion. While the article covers two common reasons for date filter issues, other factors might be at play in your case.

    You mentioned trying the two solutions and not getting the desired results. We recommend joining our ExcelDemy Forum. You can post your question there and attach your Excel file for a more detailed look.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  49. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto May 6, 2024 at 12:32 PM

    Dear Harman
    Thanks for thanking me. You are most welcome. We are glad the solution worked perfectly.

    Regards
    ExcelDemy

  50. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto May 5, 2024 at 9:19 AM

    Hello Fulad

    Thanks for your compliment! As you requested, you can use the following dataset, which contains data with your mentioned fields:

    You can download the workbook from the following link:

    DOWNLOAD WORKBOOK

    I hope you have found the dataset you were looking for; good luck.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  51. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto May 2, 2024 at 12:28 PM

    Hello Olaide

    Thanks for thanks! You are very welcome. Your appreciation means a lot to us.

    You are right! The user-defined function works perfectly fine for a few sets of coordinates. I have reviewed the code and estimated the time required to perform it on a pair of coordinates; it takes 5.7 seconds on my machine (the time can vary from device). It will take almost 8 hours to perform 5000 sets of coordinates with a spontaneous internet connection.

    The user-defined function mentioned in this article uses the OpenStreetMap Nominatim API to perform reverse geocoding. It retrieves location information based on the lat and long values given to it. It must take the required time to perform perfectly.

    So, it is impossible to reverse geocode 5000 sets of coordinates within minutes. If you ever feel like getting location information without applying the user-defined function like the regular Excel function, you can use the following sub-procedure. You need to run the code, and it will consider the lat and long values as columns B and C starting from row 3 and display the result in column D.

    Sub PopulateReverseGeocoding()
    
        Dim ws As Worksheet
        Dim lastRow As Long
        Dim i As Long
        
        Set ws = ThisWorkbook.Sheets("Sheet1")
        
        lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
        
        For i = 3 To lastRow
        
            Dim lat As Double
            Dim lon As Double
            lat = ws.Cells(i, "B").Value
            lon = ws.Cells(i, "C").Value
            
            ws.Cells(i, "D").Value = ReverseGeocoder(lat, lon)
        
        Next i
    
    End Sub
    
    Function ReverseGeocoder(lati As Double, longi As Double) As String
        On Error GoTo ErrorHandler
        
        Dim xD As New MSXML2.DOMDocument
        Dim URL As String
        
        xD.async = False
        URL = "https://nominatim.openstreetmap.org/reverse?lat=" + CStr(lati) + _
              "&lon=" + CStr(longi)
        
        xD.Load (URL)
        
        If xD.parseError.ErrorCode <> 0 Then
            ReverseGeocoder = xD.parseError.reason
        Else
            xD.SetProperty "SelectionLanguage", "XPath"
            Dim loca As MSXML2.IXMLDOMElement
            Set loca = xD.SelectSingleNode("/reversegeocode/result")
            
            If loca Is Nothing Then
                ReverseGeocoder = "No location found."
            Else
                ReverseGeocoder = loca.Text
            End If
        End If
        
        Exit Function
        
    ErrorHandler:
        ReverseGeocoder = "Error occurred: " & Err.Description
        
    End Function

    I hope you understand the situation. Stay blessed.

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  52. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto May 2, 2024 at 11:14 AM

    Hello Alan Ang

    Thanks for visiting our blog and asking an interesting question. You seek the language codes for Simplified Chinese, Traditional Chinese, and Central Khmer. Please check the following table:

    Language Language Code
    English en
    Simplified Chinese zh-CN
    Traditional Chinese zh-TW
    Central Khmer km

    You can easily translate and fulfil your goal using the above language codes and the user-defined function mentioned in this article.

    I hope you have found the language code you were looking for. I have attached the workbook used to investigate your question; good luck.

    DOWNLOAD WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  53. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Apr 29, 2024 at 3:56 PM

    Dear, Thanks for visiting our blog and sharing your questions. You want to modify the existing formula to deal with the southerly direction of the bearings. The provided formulas in the article work for any bearing, regardless of direction. This is because the SIN and COS functions account for the direction within their calculations.

    Southerly bearings typically range from 180 to 270 degrees. The formulas will handle these values fine and automatically calculate the appropriate change in Northing and Easting based on the southerly direction. The cosine value for bearings in this range will be negative, meaning there will be a decrease in Northing because the reference point is likely north of the new point. Likewise, the sine value will be positive, meaning an increase in Easting because a southerly direction moves the point eastward.

    So, you don’t need to modify the formulas for southerly directions. They will work as intended.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  54. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Apr 29, 2024 at 10:31 AM

    Dear Adam

    Thanks for your feedback. I have reviewed your requirements. It seems like you want to perform a lookup of multiple entries (country names) in a single cell separated by a delimiter, such as commas. There will be a lookup table to keep the corresponding short names for each country. You are looking for a complex formula that takes multiple country names separated by commas, returns corresponding values (country short names), and displays them in a single cell.

    Don’t worry! I have demonstrated your situation and developed a complex formula to fulfil your goal. I used the TEXTJOIN, INDEX, MATCH, TRIM, and TEXTSPLIT functions to build the formula.

    SOLUTION Overview:

    Suppose you want to enter the comma-separated country names in cell E1. In cell E2, you want to display country short names. To do so,

    1. Select cell E2.
    2. Insert the following formula: =TEXTJOIN(", ", TRUE, INDEX($A$2:$B$46, MATCH(TRIM(TEXTSPLIT($E$1, ",")), $A$2:$A$46, 0), 2 ))

    I hope you have found the formula you were looking for. I have attached the solution workbook as well; good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  55. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Apr 25, 2024 at 5:18 PM

    Hello Harman

    Thanks for visiting our blog and sharing your query. You want 0 as leading when returning a number against a character. Don’t worry! I have come up with two solutions:

    Use of TEXTJOIN, TEXT & VLOOKUP Functions

    1. Select an empty cell.
    2. Insert the following formula: =TEXTJOIN("", 1, TEXT(VLOOKUP((IF(1, MID(B5, ROW(INDIRECT("1:" & LEN(B5))), 1))), $E$5:$F$30, 2, 0), "00"))
    3. Drag the Fill Handle icon to copy the formula down.

    Use of VBA User-Defined Function

    1. Go to Developer, followed by Visual Basic.
    2. Click on Insert followed by Module.
    3. Insert the following code in the module and save it:
      Option Explicit
      
      Function AlphabetToNumber(ByVal sSource As String) As String
      
          Dim x As Integer
          Dim sResult As String
          
          For x = 1 To Len(sSource)
              Select Case Asc(Mid(sSource, x, 1))
                  Case 65 To 90:
                      sResult = sResult & Format(Asc(Mid(sSource, x, 1)) - 64, "00")
                  Case Else
                      sResult = sResult & Mid(sSource, x, 1)
              End Select
          Next
          
          AlphabetToNumber = sResult
      
      End Function
    4. Return to the sheet and select an empty cell.
    5. Insert the following formula: =AlphabetToNumber(UPPER(B5))
    6. Hit Enter to see an output like the following GIF:

    I hope the formulas and VBA code will fulfil your goal. I have attached the solution workbook; good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  56. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Apr 25, 2024 at 11:57 AM

    Hello Anon

    Thanks for your question! You wanted formulas to check for changes in multiple columns. Assuming you have three columns: First Name, Middle Name, and Last Name, you want to check for changes in the Timestamp column.

    Solution Overview:

    Follow these steps:

    1. Select cell F5 and insert the following formula: =IF(B5="","",IF(OR(E5="",AND(ISNUMBER(F5),B5=F5)),F5,B5))
    2. Press Enter and use the Fill Handle icon to copy the formula down.
    3. Select cell G5 and apply the following formula: =IF(C5="","",IF(OR(E5="",AND(ISNUMBER(G5),C5=G5)),G5,C5))
    4. Press Enter and use the Fill Handle icon to copy the formula down.
    5. Select cell H5 and insert the following formula: =IF(D5="","",IF(OR(E5="",AND(ISNUMBER(H5),D5=H5)),H5,D5))
    6. Press Enter and use the Fill Handle icon to copy the formula down.
    7. Select cell E5 and insert the following formula: =IF(AND(B5<>"",F5<>B5, G5 <> C5, H5 <> D5),NOW(),IF(B5="","",E5))
    8. Press Enter and use the Fill Handle icon to copy the formula down.

    Now, input the intended names to see the output, like the GIF above.

    I hope these are the formulas you were looking for. I have attached the solution workbook; good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  57. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Apr 22, 2024 at 10:09 AM

    Hello Charlie V

    Thanks for visiting our blog and sharing your ideas. You are talking about shortcut keys to hide all rows or columns.

    Do worry! We have demonstrated the whole idea. Please check the following GIF:

    Required Shortcut Keys to Hide All Rows or Columns:
    >> Ctrl + Shift + Right Arrow to select all columns to the right.
    >> Ctrl + 0 to hide the selected columns.
    >> Ctrl + Shift + Down Arrow to select all rows to the bottom.
    >> Ctrl + 9 to hide the selected rows.

    Hopefully, these are the shortcut keys you were looking for. Once again, thanks for the comment; good luck.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  58. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Apr 21, 2024 at 12:12 PM

    Hello Steve Zimmermann

    Thanks for visiting our blog and sharing your problem. You mentioned that the existing article methods work fine for regular Excel but not when opened inside SolidWorks (SW). Instead of showing the cell color, you get a #BLOCKED! Error; it shows up when a required resource can not be accessible.

    It seems there are compatibility issues between VBA functionalities and SolidWorks. When Excel is opened by other applications like SolidWorks, some features and functionalities may not be available. As a result, when some resources are unavailable, they show #BLOCKED!

    To overcome your situation, you can check the settings related to external scripting. You can also reach out to the SolidWorks community forum. Providing a solution without glancing at your file and being remote is very tough. I hope these ideas will help you; good luck.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  59. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Apr 21, 2024 at 10:46 AM

    Hello Abdul

    Thanks for visiting our blog and sharing your requirements. You wanted to calculate the number of hours worked by each employee in a month. You also mentioned that you have 4 guards, 2 on day and 2 on night shifts. If each shift is 6 hours long and there are 4 shifts in a day, you can calculate the total hours worked by multiplying 6 by the total shits.

    You have demonstrated your situation within an Excel file. You can download it by clicking the following link.

    DOWNLOAD SOLUTION WORKBOOK

    Note: If you want to display more detailed information like calculating exact amount of work time, you must create columns for start and end times. Then, calculate the hours worked for each shift by subtracting the start time from the end time.

    I hope these ideas will help you overcome your problem; good luck.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  60. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Apr 18, 2024 at 2:51 PM

    Dear Peter Berger

    Thanks for your feedback. Perhaps you are right about using the direct results from Nominatim, which would lead to more accurate outcomes. However, we have to get the result from the response from the Nominatim API.

    In addition to your previous post, you wanted to get all the information in a column but in different cells. Do not worry! I have improved the previously given code by using a 2D array.

    SOLUTION Overview:

    Required Excel VBA User-Defined Functions:

    Option Explicit
    
    Function ReverseGeocoder(lati As Double, longi As Double) As Variant
        On Error GoTo 0
        
        Dim xD As New MSXML2.DOMDocument
        Dim URL As String, vbErr As String
        
        xD.async = False
        URL = "https://nominatim.openstreetmap.org/reverse?lat=" + CStr(lati) + _
                "&lon=" + CStr(longi)
        
        xD.Load ("https://nominatim.openstreetmap.org/reverse?lat=" + CStr(lati) + _
                "&lon=" + CStr(longi))
        
        If xD.parseError.ErrorCode <> 0 Then
            Application.Caller.Font.ColorIndex = vbErr
            ReverseGeocoder = xD.parseError.reason
        Else
            xD.SetProperty "SelectionLanguage", "XPath"
            Dim loca As MSXML2.IXMLDOMElement
            Set loca = xD.SelectSingleNode("/reversegeocode/result")
            
            If loca Is Nothing Then
                Application.Caller.Font.ColorIndex = vbErr
                ReverseGeocoder = xD.XML
            Else
                Application.Caller.Font.ColorIndex = vbOK
                ReverseGeocoder = SplitStringToArray(loca.Text)
            End If
        End If
        
        Exit Function
        
    0:
        Debug.Print Err.Description
        
    End Function
    
    Function SplitStringToArray(strInput As String) As Variant
        
        Dim arrResult As Variant
        Dim i As Integer
        
        arrResult = Split(strInput, ", ")
        
        Dim numRows As Long
        numRows = UBound(arrResult) - LBound(arrResult) + 1
        
        Dim result() As Variant
        ReDim result(1 To numRows, 1 To 1)
        
        For i = LBound(arrResult) To UBound(arrResult)
            result(i + 1, 1) = arrResult(i)
        Next i
        
        SplitStringToArray = result
    
    End Function

    Hopefully, you have found the idea helpful. Download the solution workbook. Stay blessed.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  61. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Apr 18, 2024 at 1:45 PM

    Hello Peter Berger

    Thanks for your nice words. Your appreciation means a lot to us.

    You wanted to get all the address information in different columns. To do so, I have developed another assistive VBA user-defined function and improved the existing user-defined function named ReverseGeocoder. Using these two functions, you can quickly fulfil your goal.

    Follow these steps:

    1. Press Alt+F11.
    2. Click on Insert followed by Module.
    3. Paste the following improved code in the module and save it:
      Option Explicit
      
      Function ReverseGeocoder(lati As Double, longi As Double) As Variant
          On Error GoTo 0
          
          Dim xD As New MSXML2.DOMDocument
          Dim URL As String, vbErr As String
          
          xD.async = False
          URL = "https://nominatim.openstreetmap.org/reverse?lat=" + CStr(lati) + _
                  "&lon=" + CStr(longi)
          
          xD.Load ("https://nominatim.openstreetmap.org/reverse?lat=" + CStr(lati) + _
                  "&lon=" + CStr(longi))
          
          If xD.parseError.ErrorCode <> 0 Then
              Application.Caller.Font.ColorIndex = vbErr
              ReverseGeocoder = xD.parseError.reason
          Else
              xD.SetProperty "SelectionLanguage", "XPath"
              Dim loca As MSXML2.IXMLDOMElement
              Set loca = xD.SelectSingleNode("/reversegeocode/result")
              
              If loca Is Nothing Then
                  Application.Caller.Font.ColorIndex = vbErr
                  ReverseGeocoder = xD.XML
              Else
                  Application.Caller.Font.ColorIndex = vbOK
                  ReverseGeocoder = SplitStringToArray(loca.Text)
                  
              End If
          End If
          
          Exit Function
          
      0:
          Debug.Print Err.Description
          
      End Function
      
      Function SplitStringToArray(strInput As String) As Variant
          
          Dim arrResult As Variant
          Dim i As Integer
          
          arrResult = Split(strInput, ", ")
          
          For i = LBound(arrResult) To (UBound(arrResult) - 1) / 2
              Dim temp As String
              temp = arrResult(i)
              arrResult(i) = arrResult(UBound(arrResult) - i)
              arrResult(UBound(arrResult) - i) = temp
          Next i
          
          If Not IsNumeric(arrResult(1)) Then
      
              ReDim Preserve arrResult(UBound(arrResult) + 1)
              For i = UBound(arrResult) - 1 To 1 Step -1
                  arrResult(i + 1) = arrResult(i)
              Next i
      
              arrResult(1) = ""
          
          End If
          
          SplitStringToArray = arrResult
      
      End Function

    4. Return to the sheet and select the intended cell.
    5. Insert the following formula: =ReverseGeocoder(B5,C5)
    6. Hit Enter to see an output like the following GIF.
    7. Now, drag the Fill Handle icon to copy the formula down.

    I hope you have found the VBA user-defined function helpful. I have attached the solution workbook; good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  62. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Apr 18, 2024 at 11:55 AM

    Hello James Martin

    Thanks for visiting our blog and sharing your problem. When working with this code in another workbook, you are facing “Compile error: User-defined type not defined“. This error typically occurs when the required library or reference is not activated in the VBA Editor.

    Initial Problem:

    To overcome the problem, you must refer to the Microsoft VBScript Regular Expressions 5.5 library in VBA Editor.

    SOLUTION Overview:
    Press Alt+F11 >> Tools >> References >> Microsoft VBScript Regular Expressions 5.5 >> OK.

    I have improved the codes and solved your problem in a workbook. You can find these codes in the modules. Hopefully, you have found the solution helpful. I have attached the solution workbook as well; good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  63. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Apr 18, 2024 at 10:14 AM

    Dear Vyshnav V S

    It is good to see you again. Thanks for sharing further requirements. You want to improve the existing VBA code to add a hyperlink in the email content. The hyperlink should display only the task ID, but when clicked, it will direct to the ALM link along with the task ID.

    I have improved the previously given code to fulfil your goal. When sending an email, ensure that you uncomment the line that contains the “.Send” property. Also, change the existing base AML link with the intended one.

    SOLUTION Overview:

    Improved Excel VBA Sub-procedure:

    Sub SendReminderEmails()
    
        Dim outlookApp As Object
        Dim outlookMail As Object
        Dim ws As Worksheet
        Dim lastRow As Long
        Dim i As Long
        Dim almBaseLink As String
    
        almBaseLink = "http://alm.example.com/task?id="
    
        Set ws = ThisWorkbook.Sheets("DATA")
    
        lastRow = ws.Cells(ws.Rows.Count, "F").End(xlUp).row
    
        For i = 5 To lastRow
            If ws.Cells(i, "C").Value < Date Then
                Set outlookApp = CreateObject("Outlook.Application")
                Set outlookMail = outlookApp.CreateItem(0)
    
                outlookMail.To = ws.Cells(i, "F").Value
                outlookMail.Subject = "Reminder: " & ws.Cells(i, "E").Value
    
                Dim taskID As String
                taskID = ws.Cells(i, "D").Value
                Dim taskALMLink As String
                taskALMLink = almBaseLink & taskID
    
                outlookMail.Body = "Dear " & vbCrLf & vbCrLf & _
                                    "This is a reminder that Task ID " & ws.Cells(i, "D").Value & " is overdue." & vbCrLf & vbCrLf & _
                                    ws.Cells(i, "E").Value & "," & vbCrLf & _
                                    "Start Date: " & ws.Cells(i, "B").Value & vbCrLf & _
                                    "Planned Due Date: " & Format(ws.Cells(i, "C").Value, "DD-MMM-YYYY") & vbCrLf & _
                                    "Mail Content: " & ws.Cells(i, "F").Value & vbCrLf & _
                                    "Reviewer: " & ws.Cells(i, "G").Value & vbCrLf & vbCrLf & _
                                    "Please take necessary action." & vbCrLf & vbCrLf & _
                                    "Regards," & vbCrLf & _
                                    "Vyshnav V S" & vbCrLf & vbCrLf & _
                                    "Click here to view the task details: " & _
                                    "<a href=""" & taskALMLink & """>" & taskID & "</a>"
    
                outlookMail.HTMLBody = outlookMail.Body
                outlookMail.Display
    
                On Error Resume Next
                'outlookMail.Send
    
                Set outlookMail = Nothing
                Set outlookApp = Nothing
            End If
        Next i
    
    End Sub

    I hope the improved sub-procedure will reach your goal. I have attached the solution workbook; good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  64. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Apr 17, 2024 at 2:18 PM

    Hello Adele

    Thanks for your nice words. We are glad you found the article helpful!

    In Excel, we are directly unable to undo a VBA macro action in the same way you can undo regular actions using the Ctrl + Z shortcut. So, you must use the Excel Track Changes feature, or you can also use external version control systems like Git.

    I hope these ideas will overcome your situation; good luck.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  65. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Apr 17, 2024 at 12:31 PM

    Hello Harry Ingram

    Thanks for such an interesting comment. You wanted to extract numbers even if there are decimal points. Additionally, if there are more numbers, you want to put them in different columns. I have come up with a solution using several Excel VBA User-defined functions.

    SOLUTION Overview:

    To do so, follow these steps:

    1. Press Alt+F11 to open the VBA editor.
    2. Click on Insert followed by Module.
    3. Paste the following code into the module and save it:
      Function ExtractNumbers(inputStr As String, Optional separator As String = " ") As Variant
          
          Dim numbers() As String
          Dim num As String
          Dim i As Integer
          Dim outputArr(1 To 3) As Variant
          
          
          inputStr = RemoveSecondSpace(AddSpaceAfterNumbers(inputStr))
          numbers = Split(inputStr, separator)
      
          For i = LBound(numbers) To UBound(numbers)
              num = ""
      
              For j = 1 To Len(numbers(i))
                  If IsNumeric(Mid(numbers(i), j, 1)) Or Mid(numbers(i), j, 1) = "." Then
                      num = num & Mid(numbers(i), j, 1)
                  ElseIf num <> "" Then
                      Exit For
                  End If
              Next j
      
              Select Case i
                  Case 0
                      outputArr(1) = Val(num)
                  Case 1
                      outputArr(2) = Val(num)
                  Case 2
                      outputArr(3) = Val(num)
              End Select
          Next i
      
          ExtractNumbers = outputArr
      
      End Function
      
      Function AddSpaceAfterNumbers(str As String)
      
          Dim result As String
          Dim i As Integer
          
          For i = 1 To Len(str)
      
              If IsNumeric(Mid(str, i, 1)) Or Mid(str, i, 1) = "." Then
      
                  result = result & Mid(str, i, 1)
                  
                  If i < Len(str) And Not (IsNumeric(Mid(str, i + 1, 1)) Or Mid(str, i + 1, 1) = ".") Then
                      result = result & " "
                  End If
              Else
                  result = result & Mid(str, i, 1)
              End If
          Next i
          
          AddSpaceAfterNumbers = result
          
      End Function
      
      Function RemoveSecondSpace(str As String) As String
      
          Dim result As String
          Dim i As Integer
          
          For i = 1 To Len(str)
      
              If Mid(str, i, 1) <> " " Then
                  result = result & Mid(str, i, 1)
              Else
      
                  If i < Len(str) And Mid(str, i + 1, 1) = " " Then
      
                      If Right(result, 1) = " " Then
                          result = Left(result, Len(result) - 1)
                      End If
                  Else
                      result = result & Mid(str, i, 1)
                  End If
              End If
          Next i
          
          RemoveSecondSpace = result
          
      End Function

    4. Return to the sheet and select the intended cell.
    5. Insert the following formula: =ExtractNumbers(B5)
    6. Drag the Fill Handle icon to copy the formula down.

    I have also attached the solution workbook; good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  66. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Apr 17, 2024 at 11:01 AM

    Hello Vyshnav V S

    It is good to see you again. When sending a reminder email, you want to add a user interface instead of running the Excel VBA Sub-procedure. To achieve this goal, you must use an Excel UserForm. I have designed a user interface in an Excel File.

    SOLUTION Overview:

    I hope the solution will help you reach your goal. I have also attached the solution workbook; good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  67. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Apr 17, 2024 at 10:08 AM

    Dear Vito Casalinuovo

    Thanks for further clarifying your problem. Based on the requirement, I have come up with another solution, though the previous solution works perfectly on our end.

    Assuming you have a dataset like the following:

    You want to get all the tasks that fall on the same day. To achieve the goal, you can combine:

    1. IFERROR, TEXTJOIN and IF Functions (New)
      =IFERROR(TEXTJOIN(", ", TRUE, IF(Sheet1!$B$3:$B$11=Sheet2!$B3, Sheet1!$C$3:$C$11, "")), "")
    2. IFERROR, TEXTJOIN and FILTER Functions (Previous)
      =IFERROR(TEXTJOIN(", ", TRUE, FILTER(Sheet1!$C$3:$C$11, Sheet1!$B$3:$B$11=Sheet2!$B3)), "")

    I hope these formulas will help you to reach your goal. I have attached the solution workbook as well; good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  68. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Apr 16, 2024 at 7:36 PM

    Hello Greg

    Thanks for visiting our blog and informing us that the previous solution worked perfectly. You want to filter the data that includes the date falling within the specified date range (0 to 14 days). Also, you want to include the rows where the document submittal date is blank.

    I have come up with an Excel VBA code. You can try it; if needed, make changes to fulfil your goal.

    SOLUTION Overview:

    Excel VBA Code:

    Sub SendEmails()
    
        Dim deadlineRange As Range
        Dim emailRange As Range
        Dim topicRange As Range
        Dim docSubmittalRange As Range
        Dim ws As Worksheet
        Dim outlookApp As Object
        Dim outlookMail As Object
        Dim i As Long
        
        On Error Resume Next
        Set deadlineRange = Application.InputBox("Select the range of deadlines:", Type:=8)
        On Error GoTo 0
        If deadlineRange Is Nothing Then Exit Sub
        
        On Error Resume Next
        Set emailRange = Application.InputBox("Select the range of emails:", Type:=8)
        On Error GoTo 0
        If emailRange Is Nothing Then Exit Sub
        
        On Error Resume Next
        Set topicRange = Application.InputBox("Select the range of topics:", Type:=8)
        On Error GoTo 0
        If topicRange Is Nothing Then Exit Sub
        
        On Error Resume Next
        Set docSubmittalRange = Application.InputBox("Select the range of document submittal dates:", Type:=8)
        On Error GoTo 0
        If docSubmittalRange Is Nothing Then Exit Sub
        
        Set ws = ThisWorkbook.ActiveSheet
        
        Set outlookApp = CreateObject("Outlook.Application")
        
        For i = 1 To emailRange.Rows.Count
    
            If deadlineRange.Cells(i, 1).Value >= Date And deadlineRange.Cells(i, 1).Value <= Date + 14 Then
    
                If Trim(docSubmittalRange.Cells(i, 1).Value) = "" Then
                    Set outlookMail = outlookApp.CreateItem(0)
                    
                    With outlookMail
                        .To = emailRange.Cells(i, 1).Value
                        .Subject = topicRange.Cells(i, 1).Value & " " & Format(deadlineRange.Cells(i, 1).Value, "dd mmmm yyyy")
                        .Body = "Dear," & vbCrLf & vbCrLf & _
                                "TEXT: " & topicRange.Cells(i, 1).Value & vbCrLf & _
                                "DEADLINE: " & Format(deadlineRange.Cells(i, 1).Value, "dd mmmm yyyy") & "." & vbCrLf & vbCrLf & _
                                "Kind regards,"
                        .Display
                        On Error Resume Next
                        .Send
                    End With
                    
                    Set outlookMail = Nothing
                End If
            End If
        Next i
        
        Set outlookApp = Nothing
        Set ws = Nothing
        Set topicRange = Nothing
        Set emailRange = Nothing
        Set deadlineRange = Nothing
        Set docSubmittalRange = Nothing
    
    End Sub

    I have attached the solution workbook; good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  69. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Apr 16, 2024 at 4:43 PM

    Dear Vito Casalinuovo

    It is good to see you again. Yes! You can capture all the tasks that fall on the same day. To do so, use the IFERROR, TEXTJOIN and FILTER function:

    SOLUTION Overview:

    Follow these steps:

    1. Choose cell C3.
    2. Insert the following formula: =IFERROR(TEXTJOIN(", ",TRUE, FILTER(Sheet1!$C$3:$C$7, Sheet1!$B$3:$B$7=Sheet2!$B3)), "")
    3. Drag the Fill Handle icon to copy the formula down.

    I hope you have found the formula helpful. I am also attaching the solution workbook; good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  70. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Apr 16, 2024 at 3:14 PM

    Dear Vyshnav V S

    It is good to see you again. You want to upgrade the previously given VBA code to include a feature that retrieves task details from an ALM system or a similar work item system.

    To do so, connect to the ALM or work item system to fetch task details within the VBA. Later, you can develop a function to find the task details based on the task ID from the Excel sheet and generate a link to the task’s details. The implementation will depend on the system used and its access methods (like APIs).

    Hopefully, the idea will help you; good luck.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  71. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Apr 16, 2024 at 2:40 PM

    Dear Peter Summers

    Thanks for visiting our blog and sharing your problem. You wanted to return a time value with “hh:mm”. To do so, you have to enhance your formula with the help of the TEXT function:

    1. Choose an empty cell.
    2. Insert the following formula: =IF(MOD(B43-B30,1) > MOD(B35-B30,1), TEXT(MOD(B43-B30,1),"hh:mm"), TEXT(MOD(B35-B30,1),"hh:mm"))
    3. Hit Enter.

    Hopefully, you have found the idea helpful; good luck.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  72. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Apr 8, 2024 at 4:16 PM

    Dear Vyshnav V S

    It is great to see you again. Thanks for reaching out and sharing another exciting problem.

    To add a Request for Delivery Receipt and a Read Receipt in the Outlook Application, we must use the ReadReceiptRequested and OriginatorDeliveryReportRequested properties. Do not worry! Based on your new requirements, I have enhanced the existing sub-procedure (I provided earlier).

    SOLUTION Overview:

    Excel VBA SUb-procedure:

    Sub SendReminderEmails()
    
        Dim outlookApp As Object
        Dim outlookMail As Object
        Dim ws As Worksheet
        Dim lastRow As Long
        Dim i As Long
    
        Set ws = ThisWorkbook.Sheets("DATA")
    
        lastRow = ws.Cells(ws.Rows.Count, "F").End(xlUp).row
    
        For i = 5 To lastRow
            If ws.Cells(i, "C").Value < Date Then
                Set outlookApp = CreateObject("Outlook.Application")
                Set outlookMail = outlookApp.CreateItem(0)
    
                outlookMail.To = ws.Cells(i, "F").Value
                outlookMail.Subject = "Reminder: " & ws.Cells(i, "E").Value
    
                outlookMail.Body = "Dear " & vbCrLf & vbCrLf & _
                                   "This is a reminder that Task ID " & ws.Cells(i, "D").Value & " is overdue." & vbCrLf & vbCrLf & _
                                   ws.Cells(i, "E").Value & "," & vbCrLf & _
                                   "Start Date: " & ws.Cells(i, "B").Value & vbCrLf & _
                                   "Planned Due Date: " & Format(ws.Cells(i, "C").Value, "DD-MMM-YYYY") & vbCrLf & _
                                   "Mail Content: " & ws.Cells(i, "F").Value & vbCrLf & _
                                   "Reviewer: " & ws.Cells(i, "G").Value & vbCrLf & vbCrLf & _
                                   "Please take necessary action." & vbCrLf & vbCrLf & _
                                   "Regards," & vbCrLf & _
                                   "Vyshnav V S"
                
                
                outlookMail.ReadReceiptRequested = True
                outlookMail.OriginatorDeliveryReportRequested = True
    
                outlookMail.Display
    
                On Error Resume Next
                outlookMail.Send
    
                Set outlookMail = Nothing
                Set outlookApp = Nothing
            End If
        
        Next i
        
    End Sub

    I hope you have found the solution you were looking for. The solution workbook is attached; good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  73. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Apr 8, 2024 at 3:20 PM

    Hello Denny Hevalahu

    Thanks for visiting our blog and sharing your questions.

    To calculate the number of different individual items sold:

    1. Select an empty cell.
    2. Insert the following formula: =SUM(1/COUNTIF(C3:C22, C3:C22))
    3. Hit Enter to see an output like the following.

    Hopefully, the formula will be helpful; good luck.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  74. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Apr 8, 2024 at 2:37 PM

    Hello MEDO

    Thanks for letting us know that the solution works perfectly on your end. However, you want to handle cultural settings in VBA, which also works on non-UK clients.

    Assuming your requirements are related to the decimal separator and thousand separator settings. Different regions worldwide use different conventions to represent decimals and thousands of separators.

    You can use two user-defined functions to replace these separators with periods and commas to overcome the situation. I have developed two VBA user-defined functions and slightly changed the existing sub-procedures.

    Calculating Distance in Miles:

    Option Explicit
    
    Public Function DistanceInMiles(First_Location As String, _
                                      Final_Location As String, _
                                      Target_Value As String) As Double
    
        Dim Initial_Point As String
        Dim Ending_Point As String
        Dim Distance_Unit As String
        Dim Setup_HTTP As Object
        Dim Output_Url As String
    
        Initial_Point = "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="
        Ending_Point = "&destinations="
        Distance_Unit = "&travelMode=driving&o=xml&key=" & Target_Value & "&distanceUnit=mi"
    
        Set Setup_HTTP = CreateObject("MSXML2.ServerXMLHTTP")
        
        First_Location = AddCommaInBetweenM(ReplaceCharsM(First_Location))
        Final_Location = AddCommaInBetweenM(ReplaceCharsM(Final_Location))
        
        Output_Url = Initial_Point & First_Location & Ending_Point & Final_Location & Distance_Unit
    
        Setup_HTTP.Open "GET", Output_Url, False
        Setup_HTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
        Setup_HTTP.Send ("")
    
        Dim distance As Double
        distance = Round(Round(WorksheetFunction.FilterXML(Setup_HTTP.ResponseText, "//TravelDistance"), 3), 0)
    
        DistanceInMiles = Format(distance, "#,##0.00")
    
    End Function
    
    Function ReplaceCharsM(strInput As String) As String
    
        Dim i As Long
        Dim strOutput As String
        Dim char As String
    
        For i = 1 To Len(strInput)
            char = Mid(strInput, i, 1)
            Select Case char
                Case Application.International(xlDecimalSeparator)
                    strOutput = strOutput & "."
                Case Application.International(xlThousandsSeparator)
                    strOutput = strOutput & ","
                Case Else
                    strOutput = strOutput & char
            End Select
        Next i
    
        ReplaceCharsM = strOutput
    
    End Function
    
    Function AddCommaInBetweenM(inputString As String) As String
        
        Dim resultString As String
        Dim i As Integer
        
        resultString = Left(inputString, 1)
        
        For i = 2 To Len(inputString)
            If Mid(inputString, i, 1) = " " Then
                resultString = Left(resultString, Len(resultString) - 1) & ","
            Else
                resultString = resultString & Mid(inputString, i, 1)
            End If
        Next i
        
        AddCommaInBetweenM = resultString
        
    End Function

    Calculating Distance in KM:

    Option Explicit
    
    Public Function DistanceInKM(First_Location As String, Final_Location As String, _
                                 Target_Value As String) As String
    
        Dim Initial_Point As String
        Dim Ending_Point As String
        Dim Distance_Unit As String
        Dim Setup_HTTP As Object
        Dim Output_Url As String
    
        Initial_Point = "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="
        Ending_Point = "&destinations="
        Distance_Unit = "&travelMode=driving&o=xml&key=" & Target_Value & "&distanceUnit=km"
    
        Set Setup_HTTP = CreateObject("MSXML2.ServerXMLHTTP")
    
        First_Location = AddCommaInBetweenKM(ReplaceCharsKM(First_Location))
        Final_Location = AddCommaInBetweenKM(ReplaceCharsKM(Final_Location))
    
        Output_Url = Initial_Point & First_Location & Ending_Point & Final_Location & Distance_Unit
    
        Setup_HTTP.Open "GET", Output_Url, False
        Setup_HTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
        Setup_HTTP.Send ("")
    
        DistanceInKM = Round(Round(WorksheetFunction.FilterXML(Setup_HTTP.ResponseText, _
                                                              "//TravelDistance"), 3), 0)
    
    End Function
    
    Function ReplaceCharsKM(strInput As String) As String
    
        Dim i As Long
        Dim strOutput As String
        Dim char As String
    
        For i = 1 To Len(strInput)
            char = Mid(strInput, i, 1)
            Select Case char
                Case Application.International(xlDecimalSeparator)
                    strOutput = strOutput & "."
                Case Application.International(xlThousandsSeparator)
                    strOutput = strOutput & ","
                Case Else
                    strOutput = strOutput & char
            End Select
        Next i
    
        ReplaceCharsKM = strOutput
    
    End Function
    
    Function AddCommaInBetweenKM(inputString As String) As String
        
        Dim resultString As String
        Dim i As Integer
        
        resultString = Left(inputString, 1)
        
        For i = 2 To Len(inputString)
            If Mid(inputString, i, 1) = " " Then
                resultString = Left(resultString, Len(resultString) - 1) & ","
            Else
                resultString = resultString & Mid(inputString, i, 1)
            End If
        Next i
        
        AddCommaInBetweenKM = resultString
        
    End Function

    Hopefully, the VBA code will help you with your problem; good luck.

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  75. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Apr 4, 2024 at 2:29 PM

    Hello Vito Casa

    Thanks for visiting our blog and sharing your questions. Sheet1 contains a list of tasks with corresponding dates. You want to extract this information and plan the tasks on Sheet2 based on the dates. To do so, you can develop multiple formulas using VLOOKUP, INDEX, MATCH, and XLOOKUP. You can also use IFERROR to handle errors.

    SOLUTION Overview:

    1. Using VLOOKUP and IFERROR Functions
    2. Using INDEX, MATCH and IFERROR Functions
    3. Using XLOOKUP Function

    NOTE: If you are a Microsoft 365 user, you will be able to use the XLOOKUP function.

    I hope the formulas mentioned will reach your goal. I have attached the solution workbook as well; good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  76. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Apr 4, 2024 at 12:40 PM

    Dear Isaac Chavez

    Thanks for visiting our blog. As requested, I have developed an Excel VBA Sub-procedure that will send an Email notification if the Deadline is 7 days ahead.

    SOLUTION Overview:

    Note: If the Microsoft Outlook dialog box appears, click Allow to send an email.

    Excel VBA Sub-procedure:

    Sub SendNotificationEmails()
    
        Dim outlookApp As Object
        Dim outlookMail As Object
        Dim ws As Worksheet
        Dim lastRow As Long
        Dim i As Long
        
        Set ws = ThisWorkbook.Sheets("DATA")
        
        lastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).row
        
        For i = 5 To lastRow
            If ws.Cells(i, "C").Value - Date = 7 Then
                Set outlookApp = CreateObject("Outlook.Application")
                Set outlookMail = outlookApp.CreateItem(0)
                
                outlookMail.To = ws.Cells(i, "E").Value
                outlookMail.Subject = "Reminder: " & ws.Cells(i, "D").Value
                
                outlookMail.Body = "Dear " & vbCrLf & vbCrLf & _
                                    ws.Cells(i, "E").Value & "," & vbCrLf & _
                                   "Start Date: " & ws.Cells(i, "B").Value & vbCrLf & _
                                   "Deadline: " & Format(ws.Cells(i, "C").Value, "DD-MMM-YYYY") & vbCrLf & _
                                   "Mail Content: " & ws.Cells(i, "D").Value & vbCrLf & _
                                   "Please take necessary action." & vbCrLf & vbCrLf & _
                                   "Regards," & vbCrLf & _
                                   "Isaac Chavez"
                
                outlookMail.Display
                
                On Error Resume Next
                outlookMail.Send
                
                Set outlookMail = Nothing
                Set outlookApp = Nothing
            End If
        Next i
    
    End Sub

    I hope the sub-procedure will fulfil your requirements. I have attached the solution workbook as well; good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  77. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Apr 4, 2024 at 11:38 AM

    Dear Vyshnav V S

    As requested, I have improved the existing sub-procedure (previously provided) to fulfil your new requirements.

    SOLUTION Overview:

    NOTE: If the Microsoft Outlook dialog box appears, click Allow to send an email.

    Improved Excel VBA Sub-procedure:

    Sub SendReminderEmails()
    
        Dim outlookApp As Object
        Dim outlookMail As Object
        Dim ws As Worksheet
        Dim lastRow As Long
        Dim i As Long
        
        Set ws = ThisWorkbook.Sheets("DATA")
        
        lastRow = ws.Cells(ws.Rows.Count, "F").End(xlUp).row
        
        For i = 5 To lastRow
            If ws.Cells(i, "C").Value < Date Then
                Set outlookApp = CreateObject("Outlook.Application")
                Set outlookMail = outlookApp.CreateItem(0)
                
                outlookMail.To = ws.Cells(i, "F").Value
                outlookMail.Subject = "Reminder: " & ws.Cells(i, "E").Value
                
                outlookMail.Body = "Dear " & vbCrLf & vbCrLf & _
                                   "This is a reminder that Task ID " & ws.Cells(i, "D").Value & " is overdue." & vbCrLf & vbCrLf & _
                                    ws.Cells(i, "E").Value & "," & vbCrLf & _
                                   "Start Date: " & ws.Cells(i, "B").Value & vbCrLf & _
                                   "Planned Due Date: " & Format(ws.Cells(i, "C").Value, "DD-MMM-YYYY") & vbCrLf & _
                                   "Mail Content: " & ws.Cells(i, "F").Value & vbCrLf & _
                                   "Reviewer: " & ws.Cells(i, "G").Value & vbCrLf & vbCrLf & _
                                   "Please take necessary action." & vbCrLf & vbCrLf & _
                                   "Regards," & vbCrLf & _
                                   "Vyshnav V S"
                
                outlookMail.Display
                
                On Error Resume Next
                outlookMail.Send
                
                Set outlookMail = Nothing
                Set outlookApp = Nothing
            End If
        Next i
    
    End Sub

    I hope the sub-procedure will reach your goal. I have attached the solution workbook as well; good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  78. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Apr 3, 2024 at 5:19 PM

    Dear Vyshnav V S

    Thanks for clarifying your requirements further. I have modified the previously given code to fulfil your goal. Now, you can select any column containing email IDs, and all the other required information will be retrieved from columns C and D.

    SOLUTION Overview:

    Excel VBA Sub-procedure:

    Sub SendEmailUsingSelectedRows()
    
        Dim selectedRange As Range
        Dim outlookApp As Object
        Dim outlookMail As Object
        Dim row As Range
        
        For Each row In Selection.Rows
            
            If Not IsEmpty(row.Cells(1, 1).Value) Then
    
                Set outlookApp = CreateObject("Outlook.Application")
                Set outlookMail = outlookApp.CreateItem(0)
    
                outlookMail.To = row.Cells(1, 1).Value
                outlookMail.Subject = "Your ExcelDemy Registration Information"
    
                outlookMail.Body = "Greetings," & vbCrLf & vbCrLf & _
                                    Range("D" & row.row).Value & vbCrLf & vbCrLf & _
                                    "Deadline: " & Range("E" & row.row).Value & vbCrLf & vbCrLf & _
                                    "ExcelDemy Team"
                On Error Resume Next
                outlookMail.Display
                outlookMail.Send
    
                Set outlookMail = Nothing
                Set outlookApp = Nothing
            
            End If
        Next row
    
    End Sub

    Make changes to the code if necessary. I hope the code will fulfil your goal; good luck.

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  79. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Apr 3, 2024 at 4:47 PM

    Dear Issac

    Thanks for letting us know that the previously provided SendEmails sub-procedure worked on your side. Now, you want to automate this task daily. To achieve this, you create a Workbook_Open event and call the SendEmails sub-procedure. Later, you must use the Task Scheduler to open the workbook daily at a particular time. I have improved the SendEmails sub-procedure and created a solution to your problem using the mentioned idea.

    Follow these steps:

    1. Press ALt+F11, click on Insert followed by Module.
    2. Insert the following code in the module1 and save it:
      Sub SendEmails()
      
          Dim deadlineRange As Range
          Dim emailRange As Range
          Dim topicRange As Range
          Dim ws As Worksheet
          Dim outlookApp As Object
          Dim outlookMail As Object
          Dim i As Long
          
          On Error Resume Next
          Set deadlineRange = Application.InputBox("Select the range of deadlines:", Type:=8)
          On Error GoTo 0
          If deadlineRange Is Nothing Then Exit Sub
          
          On Error Resume Next
          Set emailRange = Application.InputBox("Select the range of emails:", Type:=8)
          On Error GoTo 0
          If emailRange Is Nothing Then Exit Sub
          
          On Error Resume Next
          Set topicRange = Application.InputBox("Select the range of topics:", Type:=8)
          On Error GoTo 0
          If topicRange Is Nothing Then Exit Sub
          
          Set ws = ThisWorkbook.ActiveSheet
          
          Set outlookApp = CreateObject("Outlook.Application")
          
          For i = 1 To emailRange.Rows.Count
      
              Set outlookMail = outlookApp.CreateItem(0)
              
              With outlookMail
                  .To = emailRange.Cells(i, 1).Value
                  .Subject = topicRange.Cells(i, 1).Value & " " & Format(deadlineRange.Cells(i, 1).Value, "dd mmmm yyyy")
                  .Body = "Dear," & vbCrLf & vbCrLf & _
                          "TEXT: " & topicRange.Cells(i, 1).Value & vbCrLf & _
                          "DEADLINE: " & Format(deadlineRange.Cells(i, 1).Value, "dd mmmm yyyy") & "." & vbCrLf & vbCrLf & _
                          "Kind regards,"
                  .Display
                  On Error Resume Next
                  .Send
              End With
              
              Set outlookMail = Nothing
          Next i
          
          Set outlookApp = Nothing
          Set ws = Nothing
          Set topicRange = Nothing
          Set emailRange = Nothing
          Set deadlineRange = Nothing
      
      End Sub
    3. Later, right-click on ThisWorkbook under the VBA Project section and click on View Code.
    4. Paste the following code in the workbook module and save it:
      Private Sub Workbook_Open()
          Call Module1.SendEmails
      End Sub

    5. Now, open the Task Scheduler and close the Excel file.
    6. In the Task Scheduler window, click on Create Basic Task.
    7. In the Create Basic Task Wizard:
      • >> Name the task and add descriptions.
      • >> Click on Next.
    8. Now, choose Task Trigger as daily.
    9. Select the desired date and time and click on Next.
    10. At this time, choose Action as Start a program.
    11. Browse to choose EXCEL.EXE.
    12. Insert the intended Excel file path in the Add arguments section.
    13. Finally, click on Finish.
    14. Now, wait until the start time is reached, previously set within Task Scheduler.
    15. When it is time, the Excel File will open.
    16. As a result, the SendEmails sub-procedure will called from the Workbook_Open event.

    Hopefully, you have found the ultimate solution to your requirements. I have attached the solution workbook; good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  80. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Apr 3, 2024 at 2:18 PM

    Hello Vyshnav V S

    Thanks for visiting our blog and sharing an exciting problem. I have developed an Excel VBA Sub-procedure that meets both requirements.

    To send email using VBA, you can use the Send property. When sending an email, the Microsoft Outlook dialog box will appear. Lastly, you must click on Allow to send email.

    SOLUTION Overview:

    1. Enabling Multi-row Selection:
    2. Send Emails by Selecting a Row (specifically the email ID column in that row):

    NOTE: Additionally, the sub-procedure will validate your selection. If you select another column instead of the ID column (assuming it is column B), the message will pop up saying to choose the intended column.

    Excel VBA Sub-procedure:

    Sub SendEmailUsingSelectedRows()
    
        Dim selectedRange As Range
        Dim outlookApp As Object
        Dim outlookMail As Object
        Dim row As Range
        
        If Selection.Column <> 2 Then
            MsgBox "Please select a range of cells in the Email ID column (Column B).", vbCritical
            Exit Sub
        End If
        
        For Each row In Selection.Rows
    
            If Not IsEmpty(row.Cells(1, 1).Value) Then
    
                Set outlookApp = CreateObject("Outlook.Application")
                Set outlookMail = outlookApp.CreateItem(0)
    
                outlookMail.To = row.Cells(1, 1).Value
                outlookMail.Subject = "Your ExcelDemy Registration Information"
    
                outlookMail.Body = "Greetings," & vbCrLf & vbCrLf & _
                                    row.Cells(1, 2).Value & vbCrLf & vbCrLf & _
                                    "Deadline: " & row.Cells(1, 3).Value & vbCrLf & vbCrLf & _
                                    "ExcelDemy Team"
                On Error Resume Next
                outlookMail.Display
                outlookMail.Send
    
                Set outlookMail = Nothing
                Set outlookApp = Nothing
            
            End If
        Next row
    
    End Sub

    Hopefully, the sub-procedure will meet your expectations. I have attached the solution workbook as well. Good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  81. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Apr 3, 2024 at 12:49 PM

    Hello Irish Jim

    Thanks for your compliments. Your appreciation and suggestions mean a lot to us. You are right; it would be great to mention right-clicking and clicking on View Code when inserting code in the sheet module. Also, it is important to mention the Target parameter when discussing events in Excel.

    Thank you once again for visiting our blog and providing beneficial suggestions. Based on your suggestion, we have updated the sections. Hopefully, other visitors will have a better reading experience.

    Regards
    ExcelDemy

  82. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 31, 2024 at 4:27 PM

    Hello Isaac Chavez

    Thanks for visiting our blog and sharing your problem. I have developed an Excel VBA Sub-procedure that automatically sends emails.

    SOLUTION Overview:

    All you need to do is to click on Allow when the Microsoft Outlook dialog box appears.

    Excel VBA Sub-procedure:

    Sub SendEmails()
    
        Dim deadlineRange As Range
        Dim emailRange As Range
        Dim topicRange As Range
        Dim ws As Worksheet
        Dim outlookApp As Object
        Dim outlookMail As Object
        Dim i As Long
        
        On Error Resume Next
        Set deadlineRange = Application.InputBox("Select the range of deadlines:", Type:=8)
        On Error GoTo 0
        If deadlineRange Is Nothing Then Exit Sub
        
        On Error Resume Next
        Set emailRange = Application.InputBox("Select the range of emails:", Type:=8)
        On Error GoTo 0
        If emailRange Is Nothing Then Exit Sub
        
        On Error Resume Next
        Set topicRange = Application.InputBox("Select the range of topics:", Type:=8)
        On Error GoTo 0
        If topicRange Is Nothing Then Exit Sub
        
        Set ws = ThisWorkbook.ActiveSheet
        
        Set outlookApp = CreateObject("Outlook.Application")
        
        For i = 1 To emailRange.Rows.Count
    
            Set outlookMail = outlookApp.CreateItem(0)
            
            With outlookMail
                .To = emailRange.Cells(i, 1).Value
                .Subject = topicRange.Cells(i, 1).Value & " " & Format(deadlineRange.Cells(i, 1).Value, "dd mmmm yyyy")
                .Body = "Dear," & vbCrLf & vbCrLf & _
                        "TEXT: " & topicRange.Cells(i, 1).Value & vbCrLf & _
                        "DEADLINE: " & Format(deadlineRange.Cells(i, 1).Value, "dd mmmm yyyy") & "." & vbCrLf & vbCrLf & _
                        "Kind regards,"
                .Display
                .Send
            End With
            
            Set outlookMail = Nothing
        Next i
        
        Set outlookApp = Nothing
        Set ws = Nothing
        Set topicRange = Nothing
        Set emailRange = Nothing
        Set deadlineRange = Nothing
    
    End Sub

    When testing the sub-procedure, I accidentally emailed you by pressing Allow in the Microsoft Outlook dialog box. Please ignore that. I hope the sub-procedure will fulfil your goal; good luck.

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  83. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 31, 2024 at 3:05 PM

    Hello Jim Pratt

    Thanks for noticing the typo. We have updated the section accordingly.

    Regards
    ExcelDemy

  84. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 31, 2024 at 2:26 PM

    Hello Bharti

    Thanks for visiting our blog and posting an exciting comment. As requested, I have developed an Excel VBA User-defined function to get Meteorological Week no.

    Standard Meteorological Weeks:

    Follow these steps:

    1. Press Alt+F11.
    2. Click on Insert followed by Module.
    3. Paste the following code into the module and save it:
      Function GETMeteorologicalWeekNo(inputDate As Date) As Long
          
          Dim dict As Object
          Set dict = CreateObject("Scripting.Dictionary")
      
          dict.Add 1, "01 Jan – 07 Jan"
          dict.Add 2, "08 Jan – 14 Jan"
          dict.Add 3, "15 Jan – 21 Jan"
          dict.Add 4, "22 Jan – 28 Jan"
          dict.Add 5, "29 Jan – 04 Feb"
          dict.Add 6, "05 Feb – 11 Feb"
          dict.Add 7, "12 Feb – 18 Feb"
          dict.Add 8, "19 Feb – 25 Feb"
          dict.Add 9, "26 Feb – 04 Mar"
          dict.Add 10, "05 Mar – 11 Mar"
          dict.Add 11, "12 Mar – 18 Mar"
          dict.Add 12, "19 Mar – 25 Mar"
          dict.Add 13, "26 Mar – 01 Apr"
          dict.Add 14, "02 Apr – 08 Apr"
          dict.Add 15, "09 Apr – 15 Apr"
          dict.Add 16, "16 Apr – 22 Apr"
          dict.Add 17, "23 Apr – 29 Apr"
          dict.Add 18, "30 Apr – 06 May"
          dict.Add 19, "07 May – 13 May"
          dict.Add 20, "14 May – 20 May"
          dict.Add 21, "21 May – 27 May"
          dict.Add 22, "28 May – 03 Jun"
          dict.Add 23, "04 Jun – 10 Jun"
          dict.Add 24, "11 Jun – 17 Jun"
          dict.Add 25, "18 Jun – 24 Jun"
          dict.Add 26, "25 Jun – 01 Jul"
          dict.Add 27, "02 Jul – 08 Jul"
          dict.Add 28, "09 Jul – 15 Jul"
          dict.Add 29, "16 Jul – 22 Jul"
          dict.Add 30, "23 Jul – 29 Jul"
          dict.Add 31, "30 Jul – 05 Aug"
          dict.Add 32, "06 Aug – 12 Aug"
          dict.Add 33, "13 Aug – 19 Aug"
          dict.Add 34, "20 Aug – 26 Aug"
          dict.Add 35, "27 Aug – 02 Sep"
          dict.Add 36, "03 Sep – 09 Sep"
          dict.Add 37, "10 Sep – 16 Sep"
          dict.Add 38, "17 Sep – 23 Sep"
          dict.Add 39, "24 Sep – 30 Sep"
          dict.Add 40, "01 Oct – 07 Oct"
          dict.Add 41, "08 Oct – 14 Oct"
          dict.Add 42, "15 Oct – 21 Oct"
          dict.Add 43, "22 Oct – 28 Oct"
          dict.Add 44, "29 Oct – 04 Nov"
          dict.Add 45, "05 Nov – 11 Nov"
          dict.Add 46, "12 Nov – 18 Nov"
          dict.Add 47, "19 Nov – 25 Nov"
          dict.Add 48, "26 Nov – 02 Dec"
          dict.Add 49, "03 Dec – 09 Dec"
          dict.Add 50, "10 Dec – 16 Dec"
          dict.Add 51, "17 Dec – 23 Dec"
          dict.Add 52, "24 Dec – 31 Dec"
          
          Dim yearValue As Integer
          Dim startDate As Date
          Dim endDate As Date
          
          yearValue = year(inputDate)
          
          Dim key As Variant
          For Each key In dict.Keys
              Dim dates() As String
              dates = Split(dict(key), "–")
              startDate = DateValue(Trim(dates(0)) & " " & yearValue)
              endDate = DateValue(Trim(dates(1)) & " " & yearValue)
              
              If inputDate >= startDate And inputDate <= endDate Then
                  GETMeteorologicalWeekNo = key
                  Exit Function
              End If
          Next key
      
          GETMeteorologicalWeekNo = 0
          
      End Function

    4. Return to the sheet and select cell B2.
    5. Insert the following formula: =GETMeteorologicalWeekNo(A2)
    6. Drap the Fill Handle icon to copy the formula down.

    I hope the Excel VBA User-defined function will fulfil your goal. I also have attached the solution workbook; good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  85. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 28, 2024 at 1:23 PM

    Hello Larry

    Thanks for sharing your problem. Being remote makes it difficult to provide an ultimate solution. However, you can try Repairing Office Installation, Checking Graphics Card Drivers, and Checking for Conflicting Add-ins.

    Sometimes, a corrupted Office installation can cause unexpected behavior. To fix this, you can try repairing your Office 365 installation. Outdated or incompatible graphics card drivers could also contribute to display issues. Update your graphics card drivers to the latest version from HP’s website.

    If none of the above solutions work, you might need to contact HP Support for further assistance. Though the exact cause (Office 365, HP Spectre, or Windows 11) remains unclear, hopefully, you can try these ideas to overcome your situation.

    Regards
    ExcelDemy

  86. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 28, 2024 at 12:59 PM

    Hello Andre Van Niekerk

    Thanks for sharing your problem. It is unusual for copying and pasting to stop working entirely in Excel 2013 if it worked perfectly in Excel 2010. Both versions rely on the same core Windows clipboard functionality.

    The PDF file copied from might have a complex layout or structure that Excel 2013 has difficulty handling during the copy-and-paste process. Also, if you’re trying to paste into merged cells in Excel 2013, it might cause problems. Try un-merging the cells before pasting.

    If you are still having trouble with the issue, we recommend you use the other approaches mentioned. Let us know if they lead you to the same result; good luck.

    Regards
    ExcelDemy

  87. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 28, 2024 at 12:34 PM

    Hello Ignacio Chavez

    Thanks for thanking me. Though, I was unable to access the link you have given, I understand your requirements. I have modified my previous VBA code in such a way that this time, it will identify the columns dynamically (Assuming column headings are in row 5).

    SOLUTION Overview:

    Excel VBA Code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Dim AffectedRange As Range
    
        If Not Intersect(Target, Me.Columns("D")) Is Nothing Then
    
            Set AffectedRange = Me.Range("D5:D" & Me.Cells(Me.Rows.Count, "D").End(xlUp).Row)
            
            If Not AffectedRange Is Nothing And AffectedRange.Cells.Count > 1 Then
                Call SortRangeByAverageSales
            End If
        
        End If
    
    End Sub
    
    Sub SortRangeByAverageSales()
    
        Dim ws As Worksheet
        Dim i As Long
        Dim lastRow As Long
        Dim lastColumn As Long
        Dim lastRowRank As Long
    
        Set ws = ThisWorkbook.Sheets("Sheet1")
    
        lastRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row
        lastColumn = ws.Cells(5, ws.Columns.Count).End(xlToLeft).Column
    
        With ws.Sort
            .SortFields.Clear
            .SortFields.Add Key:=ws.Range("D5:D" & lastRow), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlDescending
            .SetRange ws.Range(ws.Cells(5, 3), ws.Cells(lastRow, lastColumn))
            .Apply
        End With
    
        ws.Range("B" & lastRow).ClearContents
        lastRowRank = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
    
        For i = 5 To lastRowRank
            ws.Range("B" & i).Value = i - 4
        Next i
    
    End Sub

    Hopefully, you have found the idea. I have attached the solution workbook; good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  88. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 28, 2024 at 11:58 AM

    Hello Marvin

    Thanks for visiting our blog and sharing your questions so clearly. Basically, you want to have a timestamp for each row and store it in two different columns: one for the Date Entered and the other for the Date Modified.

    SOLUTION Overview:

    I have developed an Excel VBA Event Procedure regarding your problem:

    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Dim rng As Range
        Dim cell As Range
    
        Set rng = Me.Range("C2:C" & Me.Rows.Count)
    
        If Not Intersect(Target, rng) Is Nothing Then
            Application.EnableEvents = False
    
            For Each cell In Intersect(Target, rng)
                
                If Not IsEmpty(cell) Then
    
                    If IsEmpty(cell.Offset(0, -2)) Then
                        cell.Offset(0, -2).Value = Format(Now, "dd-mm-yyy hh:mm:ss")
                    End If
                    cell.Offset(0, -1).Value = Format(Now, "dd-mm-yyy hh:mm:ss")
                    
                Else
                    cell.Offset(0, -2).ClearContents
                    cell.Offset(0, -1).Value = Format(Now, "dd-mm-yyy hh:mm:ss")
                End If
                
            Next cell
    
            Application.EnableEvents = True
        
        End If
    
    End Sub

    Hopefully, you have found the idea. I have attached the solution workbook for better understanding; good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  89. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 27, 2024 at 5:38 PM

    Hello Hendrik

    Thanks for visiting our blog and posting your question. You must use the SUMIF or SUMIFS function to calculate the total sales.

    1. Using SUMIF: =SUMIF($B$5:$B$12, $H$4, $E$5:$E$12)
    2. Using SUMIFS: =SUMIFS($E$5:$E$12,$B$5:$B$12,$H$4)

    I hope these formulas will help; good luck.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  90. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 27, 2024 at 5:09 PM

    Hello Bonnie

    Thanks for reaching out. Though a few steps are needed, making mistakes along the way is obvious. But no worries! The ExcelDemy Forum is there to help. Just share your workbook and ask for advice.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  91. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 27, 2024 at 5:00 PM

    Hello Keith Shelly

    Thanks for visiting our blog and sharing your problem with such clarity. Instead of the current formula, you can try the following formula: =COUNTIF('Leased Parts'!$A:$A, $B1)

    Hopefully, you have found the idea helpful. I have attached the solution workbook; good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  92. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 24, 2024 at 4:28 PM

    Hello Tracey

    Thanks for your nice words. Your appreciation means a lot to us.

    Creating a Leave Tracker in Excel requires multiple steps, so you may often get unintentional errors when following these steps. Do not worry! You can share your problem within the ExcelDemy Forum by attaching your workbook.

    Regards
    ExcelDemy

  93. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 24, 2024 at 1:56 PM

    Hello Rob Devine

    Thanks a lot for your kind words. Your appreciation means a lot to us.

    You wanted to display the UserForm Calender when you select a single cell from the D75:D80 range and insert the date into the selected cell. Thanks once again for sharing such a practical requirement.

    SOLUTION Overview:

    To do so, follow these steps:

    1. Double-click on the UserForm named Calender.
    2. Paste the following code in the module and save it:
      Private Sub CommandButton1_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      Private Sub CommandButton2_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      Private Sub CommandButton3_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      Private Sub CommandButton4_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      Private Sub CommandButton5_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      Private Sub CommandButton6_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      Private Sub CommandButton7_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      Private Sub CommandButton8_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      Private Sub CommandButton9_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      Private Sub CommandButton10_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      Private Sub CommandButton11_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      Private Sub CommandButton12_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      Private Sub CommandButton13_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      Private Sub CommandButton14_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      Private Sub CommandButton15_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      Private Sub CommandButton16_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      Private Sub CommandButton17_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      
      Private Sub CommandButton18_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      
      Private Sub CommandButton19_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      
      Private Sub CommandButton20_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      
      Private Sub CommandButton21_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      
      Private Sub CommandButton22_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      
      Private Sub CommandButton23_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      
      Private Sub CommandButton24_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      
      Private Sub CommandButton25_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      
      Private Sub CommandButton26_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      Private Sub CommandButton27_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      Private Sub CommandButton28_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      Private Sub CommandButton29_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      
      Private Sub CommandButton30_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      
      Private Sub CommandButton31_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      
      Private Sub CommandButton32_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      
      Private Sub CommandButton33_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      
      Private Sub CommandButton34_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      
      Private Sub CommandButton35_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      
      Private Sub CommandButton36_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      Private Sub CommandButton37_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      
      Private Sub CommandButton38_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      Private Sub CommandButton39_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      Private Sub CommandButton40_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      Private Sub CommandButton41_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub
      
      Private Sub CommandButton42_Click()
          
          Dim btn As CommandButton
          Set btn = Me.ActiveControl
          
          Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
      
      End Sub

    3. Return to the sheet and make changes.

    I have attached the solution workbook for better understanding. I hope the solution will fulfil your needs; good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  94. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 24, 2024 at 12:34 PM

    Hello Denise Sano

    Thanks for visiting our blog and leaving an exciting comment. You want to rank salespeople by region based on number of units sold and then (in the case of ties) by total sales amount. However, developing such a formula using Excel’s built-in function would be time-consuming.

    So, I have developed an Excel VBA sub-procedure to help you overcome your situation.

    Follow these steps:

    1. Press Alt+F11.
    2. Click on Insert followed by Module.
    3. Paste the following code into the Module and run it:
      Sub RankSalesRepByRegion()
      
          Dim ws As Worksheet
          Dim lastRow As Long
          Dim salesRepCol As Range, regionCol As Range, unitsSoldCol As Range, salesAmountCol As Range, rankCol As Range
          Dim region As Range, uniqueRegions As New Collection
          Dim i As Long
          
          Set ws = ThisWorkbook.Sheets("Sheet1")
          
          lastRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row
          
          Set salesRepCol = ws.Range("A2:A" & lastRow)
          Set regionCol = ws.Range("B2:B" & lastRow)
          Set unitsSoldCol = ws.Range("C2:C" & lastRow)
          Set salesAmountCol = ws.Range("D2:D" & lastRow)
          Set rankCol = ws.Range("E2:E" & lastRow)
          
          For Each region In regionCol
              On Error Resume Next
              uniqueRegions.Add region.Value, CStr(region.Value)
              On Error GoTo 0
          Next region
          
          For i = 1 To uniqueRegions.Count
              Dim regionName As String
              regionName = uniqueRegions(i)
              
              Dim salesReps() As String
              Dim unitsSold() As Long
              Dim salesAmount() As Double
              Dim ranks() As Integer
              
              Dim j As Long
              Dim uniqueCount As Long
              uniqueCount = 0
              
              For j = 1 To lastRow - 1
                  If regionCol.Cells(j, 1).Value = regionName Then
                      uniqueCount = uniqueCount + 1
                      ReDim Preserve salesReps(1 To uniqueCount)
                      ReDim Preserve unitsSold(1 To uniqueCount)
                      ReDim Preserve salesAmount(1 To uniqueCount)
                      
                      salesReps(uniqueCount) = salesRepCol.Cells(j, 1).Value
                      unitsSold(uniqueCount) = unitsSoldCol.Cells(j, 1).Value
                      salesAmount(uniqueCount) = salesAmountCol.Cells(j, 1).Value
                  End If
              Next j
              
              For j = 1 To uniqueCount - 1
                  For k = j + 1 To uniqueCount
                      If unitsSold(j) < unitsSold(k) Or (unitsSold(j) = unitsSold(k) And salesAmount(j) < salesAmount(k)) Then
      
                          Dim tempSalesRep As String
                          tempSalesRep = salesReps(j)
                          salesReps(j) = salesReps(k)
                          salesReps(k) = tempSalesRep
                          
                          Dim tempUnitsSold As Long
                          tempUnitsSold = unitsSold(j)
                          unitsSold(j) = unitsSold(k)
                          unitsSold(k) = tempUnitsSold
                          
                          Dim tempSalesAmount As Double
                          tempSalesAmount = salesAmount(j)
                          salesAmount(j) = salesAmount(k)
                          salesAmount(k) = tempSalesAmount
                      End If
                  Next k
              Next j
              
              ReDim ranks(1 To uniqueCount)
              ranks(1) = 1
              For j = 2 To uniqueCount
                  If unitsSold(j - 1) = unitsSold(j) And salesAmount(j - 1) = salesAmount(j) Then
                      ranks(j) = ranks(j - 1)
                  Else
                      ranks(j) = j
                  End If
              Next j
              
              For j = 1 To uniqueCount
                  For k = 1 To lastRow
                      If salesRepCol.Cells(k, 1).Value = salesReps(j) Then
                          rankCol.Cells(k, 1).Value = ranks(j)
                          Exit For
                      End If
                  Next k
              Next j
          Next i
      
      End Sub

    As a result, you get the intended rank like the following GIF.

    I have attached the solution workbook for better understanding. Hopefully, the idea will help; good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards

    Lutfor Rahman Shimanto

    Excel & VBA Developer

    ExcelDemy

  95. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 21, 2024 at 10:33 AM

    Dear Steve

    It seems you are having some issues when dragging formulas across columns. Excel may be challenging, mainly when working with complicated formulas and extensive data.

    When creating a formula, use absolute references ($) for fixed cells (such as your time series cells) and relative references for cells that should change as you drag the formula across columns. Excel offers many built-in tools for data analysis, such as the Analysis ToolPak, which can assist with forecasting tasks. If your forecasting process is highly repetitive and complex, consider automating it with a VBA macro.

    I understand how you feel and your frustrations. Hopefully, these suggestions can help you overcome your problem.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  96. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 20, 2024 at 5:47 PM

    Dear Brent Hamilton

    Thanks for visiting our blog. You’re right; the article was described based on an older version of MS 365, likely pre-dating March 2024.

    From now on, in Excel 365, the Track Changes functionality has been integrated into the Show Changes button. It is important to note that saving the file to OneDrive is a prerequisite for using this feature.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  97. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 19, 2024 at 2:57 PM

    Hello Zakostelsky Jan

    You can use the Worksheet_Change event in Excel VBA to make the code run automatically whenever column H changes.

    Here’s how you can modify the code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    
        If Not Intersect(Target, Me.Columns("H")) Is Nothing Then
            Call SendEmailOnValueChange
        End If
    
    End Sub
    
    Private Sub SendEmailOnValueChange()
    
        Dim ws As Worksheet
        Dim lastRow As Long
        Dim i As Long
        Dim OutlookApp As Object
        
        Set OutlookApp = CreateObject("Outlook.Application")
        
        Dim MItem As Object
        
        Set ws = ThisWorkbook.Sheets("Sheet1")
        
        lastRow = ws.Cells(ws.Rows.Count, "H").End(xlUp).Row
        
        For i = 2 To lastRow
            If ws.Cells(i, "H").Value < ws.Cells(i, "I").Value Then
                
                Set OutlookApp = CreateObject("Outlook.Application")
                Set MItem = OutlookApp.CreateItem(0)
                
                With MItem
                    .To = "[email protected]" ' Change to your recipient email address
                    .Subject = "Value in column H has fallen below value in column I"
                    .body = "The value in row " & i & " of column H has fallen below the corresponding value in column I." & vbCrLf & _
                    "ID number from column A: " & ws.Cells(i, "A").Value
                    .Send
                End With
                
                Set MItem = Nothing
                Set OutlookApp = Nothing
            
            End If
        
        Next i
    
    End Sub

    I hope the idea will help you; good luck.

    Regards
    ExcelDemy

  98. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 25, 2024 at 6:25 PM

    Hello JAN ZAKOS TELSKY,

    I hope you are doing well. Thank u so much for your query. Well, I can see you want to add the ID number (Column A)  in the mail body when the changing row falls below the value from the corresponding cell. You also added that the changing values are in column H, and the static value is in column I.

    Now, follow the below VBA code to write the ID number from column A in the email body when the value in column H falls below the value of column I.

    Code:

    Sub SendEmailOnValueChange()
    
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim OutlookApp As Object
    
    Set OutlookApp = CreateObject("Outlook.Application")
    
    Dim MItem As Object
    
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your actual sheet name
    
    lastRow = ws.Cells(ws.Rows.Count, "H").End(xlUp).Row
    
    For i = 2 To lastRow
    If ws.Cells(i, "H").Value < ws.Cells(i, "I").Value Then
    Set OutlookApp = CreateObject("Outlook.Application")
    Set MItem = OutlookApp.CreateItem(0)
    
    With MItem
    .To = "[email protected]" ' Change to your recipient email address
    .subject = "Value in column H has fallen below value in column I"
    .body = "The value in row " & i & " of column H has fallen below the corresponding value in column I." & vbCrLf & _
    "ID number from column A: " & ws.Cells(i, "A").Value
    .Send
    End With
    
    Set MItem = Nothing
    Set OutlookApp = Nothing
    
    End If
    
    Next i
    
    End Sub

    Once you apply the code, you will get an email as below.

    Hope this information will help you. Please let us know if there is any further query in the comment section.

    Best Regards,
    Afrina Nafisa 
    Exceldemy

  99. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 19, 2024 at 2:29 PM

    Hello Ravi

    Thanks for visiting our blog and posting your problem. The ByRef argument type mismatch error typically occurs when the data types of variables are passed to a subroutine or function mismatch. In this case, the issue seems to be with the variable database in the UserForm_Activate() event.

    To fix the ByRef argument type mismatch error:

    1. Ensure that the database variable is declared as a Worksheet type.
    2. Check whether the database data type variable matches the expected data type in the subroutine.

    Regards
    ExcelDemy

  100. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 19, 2024 at 2:04 PM

    Hello Nirmani

    Thanks for reaching out and sharing your problem. You passed an argument that could not be manipulated into the type expected; the ByRef error happens. For example, when you try to supply an Integer variable when a Long is required.

    If you encounter a ByRef error related to your database variable, it could be due to how you pass the worksheet object reference. Ensure that the data types of the database variable matches the expected data type in the subroutine.

    When the UserForm_Activate event triggers, pass the correct worksheet reference. Double-check that the worksheet name you’re passing matches the actual name of the worksheet in your Excel file.

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  101. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 18, 2024 at 5:08 PM

    Hello Greg Smalls

    Thanks for visiting our blog and sharing your problem so clearly. After reviewing your code, I assume you have a dataset like the following image.

    The code you provided is OK; however, perhaps you have somehow messed up it when offsetting the names.

    I have revised your code and made some changes that work perfectly for the mentioned dataset. Follow these steps:

    1. Press Alt + F11.
    2. Click on Insert followed by Module.
    3. Paste the following code in the module:
      Sub Due_Date()
      
          Dim DueDate_Row As Range
          Dim Due As Range
          Dim PopUp_Notification As String
          
          Set DueDate_Row = Range("B4:I4")
          
          For Each Due In DueDate_Row
              If Not IsEmpty(Due.Value) And Date >= Due - Range("J4") Then
                  PopUp_Notification = PopUp_Notification & " " & Due.Offset(1, 0).Value
              End If
          Next Due
          
          If PopUp_Notification = "" Then
              MsgBox "PM Needed Soon."
          Else
              MsgBox "Schedule PM Maintenance Today: " & PopUp_Notification
          End If
          
      End Sub

    4. Press F5 or click on Run to get the intended output like the following image.

    I hope the idea and code will help you; good luck.

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  102. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 18, 2024 at 3:20 PM

    Hello Vipin

    Thanks for your nice words. Your appreciation means a lot to us.

    You are facing an issue with a worksheet with a Doughnut chart and a textbox in its center to display a value from another cell. The textbox is visible on the desktop version of Excel but not on the online version.

    You have tried moving the textbox to the front using Bring to Front, but it doesn’t work. You have also tried sending the chart to the back using Send to Back, but it doesn’t help either.

    The issue might be browser-specific. Different web browsers sometimes display online applications differently. For example, I do not have this type of issue at all.

     

    You may be using an older Excel Online version with this bug. Microsoft frequently updates Excel Online, and they might have fixed the issue in newer versions. Check if there are any updates available.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  103. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 18, 2024 at 12:32 PM

    Hello ALEXANDER WILCOX

    Thanks for visiting our blog and sharing your problem. When sorting categories, you get all the pictures from the unselected category in the top cell of the row.

    To overcome your situation, you can insert images using the Place in Cell feature, like the following GIF.

    I am delighted to inform you that I have developed an automated solution for you using the Excel VBA Event procedure and Sub-procedure. Please, follow these steps:

    1. Right-click on the sheet name tab.
    2. Click on View Code.
    3. Insert the following code in the sheet module and save it:
      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      
          If Not Intersect(Target, Me.Range("G5:G11")) Is Nothing Then
              Call InsertAndFitImage
          End If
      
      End Sub
      
      Sub InsertAndFitImage()
      
          Dim cellAddresses As Variant
      
          Dim cellAddress As Variant
          Dim imgFile As Variant
          Dim pic As Picture
          Dim i As Long
          Dim cell As Range
          Dim mergedWidth As Double
          Dim mergedHeight As Double
          Dim ThisPath As String
      
      
          cellAddresses = Array("G5:G11")
      
          With Application.FileDialog(msoFileDialogFilePicker)
              
              .Title = "Choose Images"
               If .Show = -1 Then
                  For i = 1 To .SelectedItems.Count
                      cellAddress = cellAddresses(i - 1)
      
                      Set cell = ThisWorkbook.ActiveSheet.Range(cellAddress)
      
                      ThisPath = .SelectedItems(1)
      
                      cell.InsertPictureInCell (ThisPath)
      
                  Next i
              End If
      
          End With
      
      End Sub

    4. Return to the sheet and insert the intended images in the selected cell, like the following GIF.

    As a result, you will no longer see pictures from the unselected categories in the top row cell when filtering or sorting categories, like the following GIF.

    I am attaching the solution workbook for better understanding; good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  104. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 14, 2024 at 4:33 PM

    Hello CHANDRA

    Thanks for visiting our blog and posting an exciting comment. You want to convert decimal numbers (up to two decimal places) to words in Rupees.

    To do so, follow these:

    1. Right-click on the sheet name tab.
    2. Click on View Code.
    3. Now, please insert the following code in the sheet module and save it:
      Function AdvancedWord(SNum As String)
      
          Dim zDPInt As Integer
          Dim zArrPlace As Variant
          Dim zRStr_Paisas As String
          Dim zNumStr As String
          Dim zP As Integer
          Dim zTemp As String
          Dim zStrTemp As String
          Dim zRStr As String
          Dim zBp As Integer
          
          zArrPlace = Array("", "", " Thousand ", " Lacs ", " Crores ", " Trillion ", "", "", "", "")
          
          On Error Resume Next
          
          If SNum = "" Then
              word = ""
              Exit Function
          End If
          
          zNumStr = Trim(Str(SNum))
          
          If zNumStr = "" Then
              word = ""
              Exit Function
          End If
          
          zRStr = ""
          zBp = 0
          
          If (zNumStr > 999999999.99) Then
              word = "Digit exceeds Maximum limit"
              Exit Function
          End If
          
          zDPInt = InStr(zNumStr, ".")
          
          If zDPInt > 0 Then
              If (Len(zNumStr) - zDPInt) = 1 Then
                  zRStr_Paisas = word_GetT(Left(Mid(zNumStr, zDPInt + 1) & "0", 2))
              ElseIf (Len(zNumStr) - zDPInt) > 1 Then
                  zRStr_Paisas = word_GetT(Left(Mid(zNumStr, zDPInt + 1), 2))
              End If
              zNumStr = Trim(Left(zNumStr, zDPInt - 1))
          End If
          
          zP = 1
          
          Do While zNumStr <> ""
              If (zP >= 2) Then
                  zTemp = Right(zNumStr, 2)
              Else
                  If (Len(zNumStr) = 2) Then
                      zTemp = Right(zNumStr, 2)
                  ElseIf (Len(zNumStr) = 1) Then
                      zTemp = Right(zNumStr, 1)
                  Else
                      zTemp = Right(zNumStr, 3)
                  End If
              End If
              
              zStrTemp = ""
              
              If Val(zTemp) > 99 Then
                  zStrTemp = word_GetH(Right(zTemp, 3), zBp)
                  If Right(Trim(zStrTemp), 3) <> "Lac" Then
                      zBp = zBp + 1
                  End If
              ElseIf Val(zTemp) <= 99 And Val(zTemp) > 9 Then
                  zStrTemp = word_GetT(Right(zTemp, 2))
              ElseIf Val(zTemp) < 10 Then
                  zStrTemp = word_GetD(Right(zTemp, 2))
              End If
              
              If zStrTemp <> "" Then
                  zRStr = zStrTemp & zArrPlace(zP) & zRStr
              End If
              
              If zP = 2 Then
                  If Len(zNumStr) = 1 Then
                      zNumStr = ""
                  Else
                      zNumStr = Left(zNumStr, Len(zNumStr) - 2)
                  End If
              ElseIf zP = 3 Then
                  If Len(zNumStr) >= 3 Then
                      zNumStr = Left(zNumStr, Len(zNumStr) - 2)
                  Else
                      zNumStr = ""
                  End If
              ElseIf zP = 4 Then
                  zNumStr = ""
              Else
                  If Len(zNumStr) <= 2 Then
                      zNumStr = ""
                  Else
                      zNumStr = Left(zNumStr, Len(zNumStr) - 3)
                  End If
              End If
              
              zP = zP + 1
          Loop
          
          If zRStr = "" Then
              zRStr = "No Rupees"
          Else
              zRStr = " Rupees " & zRStr
          End If
          
          If zRStr_Paisas <> "" Then
              zRStr_Paisas = " and " & zRStr_Paisas & " Paisas"
          End If
          
          AdvancedWord = zRStr & zRStr_Paisas & " Only"
      End Function
      
      Function word_GetH(zStrH As String, zBp As Integer)
          Dim zRStr As String
          
          If Val(zStrH) < 1 Then
              word_GetH = ""
              Exit Function
          Else
              zStrH = Right("000" & zStrH, 3)
              If Mid(zStrH, 1, 1) <> "0" Then
                  If (zBp > 0) Then
                      zRStr = word_GetD(Mid(zStrH, 1, 1)) & " Lac "
                  Else
                      zRStr = word_GetD(Mid(zStrH, 1, 1)) & " Hundred "
                  End If
              End If
              
              If Mid(zStrH, 2, 1) <> "0" Then
                  zRStr = zRStr & word_GetT(Mid(zStrH, 2))
              Else
                  zRStr = zRStr & word_GetD(Mid(zStrH, 3))
              End If
          End If
          
          word_GetH = zRStr
      End Function
      
      Function word_GetT(zTStr As String)
          Dim zTArr1 As Variant
          Dim zTArr2 As Variant
          Dim zRStr As String
          
          zTArr1 = Array("Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
          zTArr2 = Array("", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
          Result = ""
          
          If Val(Left(zTStr, 1)) = 1 Then
              zRStr = zTArr1(Val(Mid(zTStr, 2, 1)))
          Else
              If Val(Left(zTStr, 1)) > 0 Then
                  zRStr = zTArr2(Val(Left(zTStr, 1)) - 1)
              End If
              zRStr = zRStr & word_GetD(Right(zTStr, 1))
          End If
          
          word_GetT = zRStr
      End Function
      
      Function word_GetD(zDStr As String)
          Dim zArr_1() As Variant
          
          zArr_1 = Array(" One", " Two", " Three", " Four", " Five", " Six", " Seven", " Eight", " Nine", "")
          
          If Val(zDStr) > 0 Then
              word_GetD = zArr_1(Val(zDStr) - 1)
          Else
              word_GetD = ""
          End If
      End Function

    4. Return to the sheet and select the intended cell.
    5. Apply the following formula: =AdvancedWord(B5)
    6. Drag the Fill Handle icon to copy the formula down.

    I am also attaching the solution workbook for better understanding. I hope the solution will help you; good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards

    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  105. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 13, 2024 at 1:01 PM

    Hello MIke

    Thanks for visiting our blog and sharing an exciting query. It seems like you are attempting to expand the range of cells to which the multiple selection functionality applies.

    However, the line: “Target.Address = Range(“C4:K38”)” doesn’t quite work as expected because the “Target.Address” property returns the address of the changed cell, not the range as a whole.

    To check if a change occurred within a specific range of cells in Excel using VBA, use the Intersect method. It simply checks if any cells in the changed range overlap with your desired range. This way, your code responds accurately to changes within the specified range, regardless of the number of cells involved.

    I have developed a solution for your query. Follow these steps:

    1. Right-click on the sheet name tab.
    2. Click on View Code.
    3. Insert the following code in the sheet module and save it:
      Private Sub Worksheet_Change(ByVal Target As Range)
          
          Dim itemRange As Range
      
          Set itemRange = Me.Range("A4:B9")
          
          Dim dropdownRange As Range
          
          Set dropdownRange = Me.Range("C4:K38")
          
          If Not Intersect(Target, itemRange) Is Nothing Then
              dropdownRange.Cells.ClearContents
              Call CreateDropDown
          End If
          
          Dim old_val As String
          Dim new_val As String
          
          Application.EnableEvents = True
          On Error GoTo Exitsub
          
          If Not Intersect(Target, dropdownRange) Is Nothing Then
          If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
              GoTo Exitsub
            Else: If Target.Value = "" Then GoTo Exitsub Else
              Application.EnableEvents = False
              new_val = Target.Value
              Application.Undo
              old_val = Target.Value
                If old_val = "" Then
                  Target.Value = new_val
                Else
                  If InStr(1, old_val, new_val) = 0 Then
                      Target.Value = old_val & ", " & new_val
                Else:
                  Target.Value = old_val
                End If
              End If
            End If
          End If
          
          Application.EnableEvents = True
      Exitsub:
          Application.EnableEvents = True
      
      End Sub
      
      
      Sub CreateDropDown()
      
          Dim dataRange As Range
          Dim dropdownRange As Range
          Dim itemArray() As String
          Dim i As Integer
          Dim cell As Range
      
          Set dataRange = Worksheets("Sheet1").Range("A5:B9")
      
          ReDim itemArray(1 To dataRange.Rows.Count * dataRange.Columns.Count)
      
          i = 1
          For Each cell In dataRange
              itemArray(i) = cell.Value
              i = i + 1
          Next cell
      
          Set dropdownRange = Worksheets("Sheet1").Range("C4:K38")
      
          For Each cell In dropdownRange
              With cell.Validation
                  .Delete
                  .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                  xlBetween, Formula1:=Join(itemArray, ",")
              End With
          Next cell
      
      End Sub

    4. Return to the sheet and make the desired change, such as the following GIF.

    I am also attaching the solution workbook for better understanding; good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards

    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  106. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 13, 2024 at 11:02 AM

    Hello Peter Clarke

    Thanks for reaching out and sharing your problems and suggestions. You put a Public Variable to display in all procedures but got an “out of scope” report. You want a thorough explanation.

    This issue could arise due to several reasons:

    • Proper Declaration: Ensure the Public Variable is declared correctly at the module level, typically at the top of a module and outside any specific procedure.
    • Module Inclusion: Check that the module containing the Public Variable is included in the project. If it’s not included or there are visibility issues, the variable may not be accessible across all procedures.
    • Reference Check: If multiple modules or projects exist, ensure no conflicts or reference issues. Public Variables should be accessible across all referenced projects and modules.

    I have demonstrated watching a Public Variable in the Watch Window. I am using three modules: one for the public variable, one for displaying the public variable, and one for watching the public variable.

    VBA Code in Module1:

    Public totalSales As Long

    VBA Code in Module2:

    Public Sub DisplayTotalSales()
        Debug.Print totalSales
    End Sub

    VBA Code in Module3:

    Sub CalculateTotalSales()
        
        Dim ws As Worksheet
        Dim lastRow As Long
        Dim i As Long
        Dim productA As Variant
        Dim productB As Variant
        
        Set ws = ThisWorkbook.Sheets("Sheet1")
        lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
        
        For i = 6 To lastRow
            
            If IsNumeric(ws.Cells(i, 3).Value) Then
                productA = ws.Cells(i, 3).Value
            Else
                productA = 0
            End If
            
            If IsNumeric(ws.Cells(i, 4).Value) Then
                productB = ws.Cells(i, 4).Value
            Else
                productB = 0
            End If
            
            totalSales = productA + productB
            ws.Cells(i, 5).Value = totalSales
            
            Call DisplayTotalSales
        
        Next i
    
    End Sub

    OUTPUT Overview:

    Hopefully, the idea will overcome your situation. Good luck.

    Regards

    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  107. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 12, 2024 at 2:58 PM

    Hello Syarif,

    Thank you for reaching out to us and for your valuable query.

    As this article focuses on creating a cash flow projection format, you have to manually input your projected amounts for all of your cash inflows and outflows.

    To project cash flows for five years using an indirect method, first, you must collect your company’s balance sheets and income statements for two consecutive years. Using two-year data, you have to find cash flows manually for year 1 (base of projection). Next, for projecting cash flows for five years, estimate a percentage of expected growth in cash flow. Multiply each cash flow with your estimated projection of increase or decrease of cash flows. Use Fill Handle to apply the formula for all the years you want to project.

    Hope that the following article will provide you with valuable clues on creating cash flow projections using the indirect method:

    Thanks and Regards,
    Abdullah Al Masud
    ExcelDemy Team

  108. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 12, 2024 at 11:59 AM

    Dear VICTORI,

    Thanks for your feedback. It seems that you have mistakenly assumed that we didn’t include the 5.50% growth rate in our calculations.

    The growth rate is used for calculating the stream of future payments. As we showed this calculation in method 1 (i.e. Using the NPV Function method), we didn’t show it again in method 2 and suggested users to see it from the previous method.

    However, as it has dodged your eyes, we have included the detailed calculation in method 2 as well. You also suggested that the FV formula we provided, is for a regular annuity, not a growing annuity. This is true, and we have updated our article according to your feedback. You can check the updated article and share your feedback with us.

    Thank you again for your valuable comment.

    Regards,
    Seemanto Saha
    Team ExcelDemy

  109. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 11, 2024 at 7:15 PM

    Hello Aadi

    Thanks for visiting our blog and sharing such an interesting problem. I am delighted to inform you that I have developed an Excel VBA User-Defined function to fulfil your goal.

    Follow these steps:

    1. Press Alt+F11.
    2. Paste the following code into the module and Save.
      Function DivideIntoEqualGroups(range_1 As Range, numGroups As Integer) As Variant
      
          Dim output As Variant
          Dim numRows As Long
          Dim groupSize As Long
          Dim remainder As Long
          Dim i As Long, j As Long, k As Long
      
          numRows = range_1.Rows.Count
          groupSize = Application.WorksheetFunction.RoundDown(numRows / numGroups, 0)
          remainder = numRows Mod numGroups
          
          ReDim output(1 To numRows, 1 To 1)
      
          k = 1
          For i = 1 To numGroups
              For j = 1 To groupSize
                  If k <= numRows Then
                      output(k, 1) = i
                      k = k + 1
                  End If
              Next j
              If remainder > 0 Then
                  If k <= numRows Then
                      output(k, 1) = i
                      k = k + 1
                      remainder = remainder - 1
                  End If
              End If
          Next i
      
          DivideIntoEqualGroups = output
      
      End Function

    3. Return to the sheet and select cell B1.
    4. Insert the following formula: =DivideIntoEqualGroups(A1:A99,5)
    5. Hit Enter.

    As a result, you will see an output like the following GIF.

    Hopefully, the User-defined function has fulfilled your goal. I am also attaching the solution workbook for better understanding; good luck.

    Download Solution Workbook

     

    Regards

    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  110. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 10, 2024 at 6:23 PM

    Hello JULI

    Thanks for visiting our blog and sharing your query. You can modify the formula mentioned in example 1.4 and find the last cell with a number value on another sheet: =LOOKUP(2,1/(Week_1!C:C),ROW(Week_1!C:C))

    I hope the formula will overcome your issue; good luck.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  111. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 10, 2024 at 11:34 AM

    Hi Gunjan,

    Thank you for contacting us. We’ve also emailed you a practice data entry file. Feel free to download it and start practising.

    Best regards,
    ExcelDemy Team

  112. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 10, 2024 at 11:28 AM

    Hello Hardik

    Thanks for visiting our blog and sharing your difficulties. To find the total cost of the products sold that cost more than $1,000 using cell value as Criteria:

    1. Select cell L9.
    2. Insert the following formula: =SUMIF(G6:G92, K9, G6:G92)
    3. Hit Enter to see the following output.

    Hopefully, the formula will overcome the situation; good luck.

    Regards

    Lutfor Rahman Shimanto

    ExcelDemy

  113. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 10, 2024 at 10:39 AM

    Hey SMN,

    Sorry to hear that you were confused about Exercise 4. Let me help you by clarifying the problem.

    In the dataset of Exercise 4, look at the rows of Order ID A001, A002, and A003. These orders are placed at the same Date & Time by the same Customer. Therefore, only the row of Order ID A001 is considered a New Order. Similarly, rows of Order ID A015, A016, A017, A018, and  A019 are placed on the same Date & Time by the same Customer. Therefore, only the row of Order ID A015 is considered a New Order.

    In Exercise, 4 your primary task is to identify these New Orders. While this can be achieved with a simple formula, an additional requirement of this Exercise is using structured references with Excel tables.

    Therefore, you have to convert the data range into a table first. And then apply formulas to identify new orders.

    The primary concept behind solving this Exercise is to compare the Date Time and Customer values of each row with the Date Time and Customer values of the previous row. If any match is not found, then the current row is considered a New Order.

    I hope this explanation will help you understand the exercise problem and its solution. Let us know your feedback.

    Regards,

    Seemanto Saha

    Team ExcelDemy

  114. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 10, 2024 at 10:24 AM

    Dear KEITH BASKETT,

    Thanks for sharing your problem with us. I understand that you are using InputBox to take the number of rows you wish to fill in a column and then repeat the process for the next columns. However, you are facing errors when you enter a text or invalid input or press the OK, Cancel, or X button without entering any value.

    To fix these errors, you have to include a few error-handling situations in your code. Here, I have provided a sample code that contains error handling for your described situations:

    Sub HandleErrorsWhileFillingRows()
        Dim response As Variant
        Dim runAgain As Boolean
        Dim repeatCol As Variant
        Dim numRows As Variant
        Dim curCol As Integer
       
        runAgain = True
        curCol = 1
       
        While runAgain
            numRows = InputBox("Enter the number of rows:", "Number of Rows", 20)
            If Not numRows = "" Then
                If IsNumeric(numRows) Then
                    For i = 1 To Int(numRows)
                        Cells(i, curCol).Value = i
                    Next i
                    repeatCol = vbNo
                Else
                    MsgBox "The input was not a number"
                    repeatCol = MsgBox("Do you want to repeat this column?", vbYesNo, "Repeat Column?")
                End If
            Else
                MsgBox "You entered a blank value"
                repeatCol = MsgBox("Do you want to repeat this column?", vbYesNo, "Repeat Column?")
            End If
           
            If repeatCol = vbYes Then
                runAgain = True
            Else
                response = MsgBox("Do you want to enter numbers in next column?", vbYesNo, "Enter Number in Next Column")
                If response = vbYes Then
                    curCol = curCol + 1
                    runAgain = True
                Else
                    runAgain = False
                    MsgBox "You finished filling rows"
                End If
            End If
        Wend
       
    End Sub

    Here’s how this code operates:

    • >> This code starts with the current column value of 1 (column A) and asks users to enter the number of rows they wish to fill with numbers. The value is set to 20 by default, but the users can change it to any number or text or keep it void.
    • >> If the user input is numeric, then it enters values in cells of the current column (from 1 to the number of specified rows).
    • >> If the user input is non-numeric, then it shows the message “The input was not a number” and asks if the user wishes to repeat filling the current column again.
    • >> If the user clicks Yes, the previous steps are repeated for the current column. Otherwise, the code moves to the next column.
    • >> If a user clicks OK with a void input or clicks Cancel or X buttons, then it shows the message “You entered a blank value” and asks if the user wishes to repeat filling the current column again.
    • >> If the user clicks Yes, the previous steps are repeated for the current column. Otherwise, the code moves to the next column.

    You can preview the output in the following GIF:

    I hope this example will be helpful for you to understand how to handle errors while working with an InputBox in Excel VBA. Let us know your feedback.

    Regards,

    Seemanto Saha

    Team ExcelDemy

  115. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 10, 2024 at 10:18 AM

    Dear CYNTHIA,

    Thanks for reaching us. I understand that you want to create attendance sheets for many employees and shifts, including check-in/check-out time, break-in/break-out time, hours late in the morning, and overtime hours.

    Although the current article includes an attendance sheet for only 1 employee, our site contains multiple other articles that can help you to fulfill your requirements. Here are some recommended articles for creating an attendance sheet with your given requirements:

    This article contains the steps to create a weekly attendance sheet for many employees with check-in time, check-out time, and total work hours. You can apply the steps to create a weekly attendance sheet for each shift and each month.

    This article shows how to calculate overtime and overtime pay for any employee. You can use the formulas in this article to include overtime in the weekly attendance sheet for each sheet and each month.

    This article shows how to include break time in an attendance sheet in Excel.

    For the remaining requirement, the hours late value in the morning, you can add a column after the check-in time column and simply subtract the check-in time value from the advised check-in time (use absolute cell reference if it is specified in a single cell).

    I hope you will be able to develop your required attendance sheet with the help of the above-mentioned articles. Let us know your feedback.

    Regards,

    Seemanto Saha

    Team ExcelDemy

  116. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 7, 2024 at 6:10 PM

    Hello ALAN

    Thank you for reaching out. Please ensure that you’re using a 64-bit Windows system. Let us know if the issue persists.

    You can share your dataset by creating a Conversation with my forum account in the ExcelDemy Forum.

    Best regards
    Lutfor Rahman Shimanto
    ExcelDemy

  117. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 7, 2024 at 6:05 PM

    Hello! DIANA. Thanks for sharing your queries. Depending on your OS version, the user-defined functions may cause this error. Ensure you run 64-bit Windows, and let us know if you are still facing the error.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  118. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 7, 2024 at 6:04 PM

    Dear NADEE

    Thanks for sharing your problem with us. I understand that you want to extract the Julian date from a string and convert the Julian date to the Gregorian Date.

    This can be accomplished using a simple formula. To demonstrate an example, I have taken the following dataset. Here, column A contains the employee names, and column B contains the string code with Julian dates. In column C, I have applied the following formula to extract the Julian Date:

    =MID(B2,LEN(A2)+1,7)

    Afterward, I applied the following formula to convert the Julian date to the Gregorian date:

    =DATE(LEFT(C2,4),1,0)+MOD(C2,1000)

    Thus, you can easily convert a Julian date to a Gregorian date. I hope this solution will be helpful for your requirements. Let us know your feedback.

    Regards

    Seemanto Saha

    Team ExcelDemy

  119. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 7, 2024 at 5:53 PM

    Hey DAVID,

    Thanks for sharing your problem with us. We are glad to hear that you liked our article. We understand that you want to use the NOW function in Conditional Formatting to change the format of a range that contains visiting times.

    Here, we will provide such an example. Consider the following dataset. It contains Visiting Time and Appointment Duration for multiple clients. We have calculated the Visit End Time using the following formula:

    =C3+D3/24

    To highlight the rows where the current time falls within the Visiting Time and End Time using the NOW function in Conditional Formatting, follow the steps below:

    1. First, select the data range (B3:E13 in our case) where you want to apply the formatting.
    2. Then, click Conditional Formatting >> New Rules.
    3. Select the rule type Use a formula to determine which cells to format.
    4. Enter the following formula:
      =(NOW()>$C3)*(NOW()<$E3)
    5. Click Format.
    6. In the Format Cells dialog box, go to Font tab >> select a suitable color >> click OK.
      You can choose other formatting patterns, fonts, number formats, and borders here as well.
    7. Click OK in th conditional formatting dialog box.

    As a result, rows that meet the condition will be formatted.

    We hope this example will help you understand how to apply the NOW function in the Conditional Formatting rule. Let us know your feedback.

    Regards

    Seemanto Saha
    Team ExcelDemy

  120. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 7, 2024 at 5:06 PM

    Hello SHIVAM

    Thanks for your comment. To create a shift scheduler for a 5-member team with the specified shifts and off days, you can follow these steps:

    1. Create a table or spreadsheet with columns for the days of the week (Monday to Sunday) and rows for each team member.
    2. Assign each team member to a specific shift (Morning, Evening, Night, General) based on availability and preferences.
    3. Apply the off days for each member according to the given schedule (e.g., Member 1 off on Friday and Saturday).
    4. Ensure that Member 5 is assigned to the General shift and occasionally to the Morning shift when required.
    5. Repeat this process for each week or month, depending on the duration of your scheduling period.

    Here is an example of how you could structure the scheduler:

    Hopefully, the idea will help you; good luck.

    Regards

    Lutfor Rahman Shimanto
    ExcelDemy

  121. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 7, 2024 at 4:18 PM

    Hello CHARLIE

    Thanks for your comment. The existing article’s date picker displays the date based on the date order of the Machine (Windows, Mac or Linux). However, you wanted to modify the VBA code to adjust the date format to match the desired month/day/year (American format) for both the calendar display and input into the cell. Thus, the idea will ensure consistency in date formats between the calendar view and the input into the cell.

    To do so, you only need to modify the existing sub-procedure named Create_Calender by replacing it with the following.

    Excel VBA Sub-procedure:

    
    Sub Create_Calender()
            
        For i = 1 To 42
                
            If i < Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value)) Then
            Controls("C" & (i)).Caption = Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
            ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "d")
            
            Controls("C" & (i)).ControlTipText = Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
            ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "m/d/yyyy")
            
            ElseIf i >= Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value)) Then
            Controls("C" & (i)).Caption = Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
            ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "d")
            
            Controls("C" & (i)).ControlTipText = Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
            ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "m/d/yyyy")
            End If
            
            If Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
            ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "mmmm") = ((Month_Box.Value)) Then
            If Controls("C" & (i)).BackColor <> &HFFFFFF Then Controls("C" & (i)).BackColor = &HFFFFFF
            Controls("C" & (i)).Font.Bold = True
            
            If Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
            ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "m/d/yyyy") = Format(This_Day, "m/d/yyyy") Then Controls("C" & (i)).SetFocus
            Else
            If Controls("C" & (i)).BackColor <> &H80000016 Then Controls("C" & (i)).BackColor = &H8000000F
            Controls("C" & (i)).Font.Bold = False
            End If
                
        Next i
    
    End Sub
    

    I hope you have found the idea helpful. Stay blessed.

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  122. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 7, 2024 at 2:38 PM

    Hello SHIQIANG

    Thanks for sharing your problem. Typically, we are unable to perform such tasks in Excel. However, you can try using an Excel VBA Sub-procedure I have developed. It will display all the color indexes of the selected cells in the Immediate Window. The idea works perfectly if background is set manually or only one conditional formatting rule is applied in a cell. You can easily modify the formula for multiple conditional formatting rules based on your needs.

    OUTPUT Overview:

    Excel VBA Code:

    
    Sub GetConditionalFormattingColor()
    
        Dim cell As Range
        Dim colorIndex As Variant
    
        For Each cell In Selection
            If cell.FormatConditions.Count > 0 And cell.Value <> "" Then
                colorIndex = cell.FormatConditions(1).Interior.colorIndex
                Debug.Print "Background color index for cell " & cell.Address & ": " & colorIndex
            Else
                colorIndex = cell.Interior.colorIndex
                
                If colorIndex = -4142 Then
                    Debug.Print "Currently, the Cell " & cell.Address & " does not have any color."
                Else
                    Debug.Print "Background color index for cell " & cell.Address & ": " & colorIndex
                End If
    
            End If
        Next cell
        
    End Sub
    

    Reach out again if you have any further queries. Hopefully, the idea will help you; good luck.

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  123. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 7, 2024 at 12:58 PM

    Hello NADHAY SARY

    Thanks for visiting our blog and sharing your requirements. You wanted a Dynamic Leaderboard that updates automatically when the Average Sales will be changed.

    OUTPUT Overview:

    I am delighted to inform you that I have developed an Event Procedure and Sub-procedure using VBA to fulfil your goal.

    Excel VBA Code:

    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Dim AffectedRange As Range
    
        If Not Intersect(Target, Me.Columns("D")) Is Nothing Then
    
            Set AffectedRange = Me.Range("D5:D" & Me.Cells(Me.Rows.Count, "D").End(xlUp).Row)
            
            If Not AffectedRange Is Nothing And AffectedRange.Cells.Count > 1 Then
                Call SortRangeByAverageSales
            End If
        
        End If
    
    End Sub
    
    Sub SortRangeByAverageSales()
    
        Dim ws As Worksheet
        Dim i, lastRow, lastRowRank As Long
        
        Set ws = ThisWorkbook.Sheets("Sheet1")
        
        lastRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row
        
        With ws.Sort
            .SortFields.Clear
            .SortFields.Add Key:=Range("D5:D" & lastRow), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlDescending
            .SetRange Range("C5:D" & lastRow)
            .Apply
        End With
        
        ws.Range("B" & lastRow).ClearContents
        lastRowRank = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
        
        For i = 5 To lastRowRank
            ws.Range("B" & i).Value = i - 4
        Next i
    
    End Sub
    

    Follow the steps: Right-click on the sheet name tab >> View Code >> Paste the given code in the sheet module >> Save >> Return to the sheet and make your desired changes.

    Hopefully, the code will help you in reaching your goal.

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  124. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 7, 2024 at 11:58 AM

    Hello KARAN

    Thanks for reaching out and posting such an interesting comment. Yes, you can manually specify holidays using the NETWORKDAYS.INTL function to customize which days are considered weekends and also lets you include specific holidays.

    In your case, the first and second arguments in the formula are for the start and end dates; you can use 11 3rd arguments to consider only Sunday as the weekend. In the 4th argument, you insert the specified holiday.

    =NETWORKDAYS.INTL(startDate, endDate, weekend, holidays)

    I have developed a formula for January by focusing on your specified weekends and holidays.

    =NETWORKDAYS.INTL(B2, B3, 11, A6:A7)

    Hopefully, the formula will help; good luck.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  125. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Mar 7, 2024 at 11:08 AM

    Hello MATTEO

    Thanks for visiting our blog and sharing your problem. Excel’s internal precision for numerical calculations can sometimes lead to unexpected results, especially when dealing with tiny or huge numbers.

    To overcome your situation, you can combine the ROUND and CONVERT functions.

    “mm” to “ft”:

    =ROUND(CONVERT(C5, “mm”, “ft”), 2)

    “mm” to “in”:

    =ROUND(CONVERT(C5, “mm”, “in”), 2)

    Hopefully, the formula will help you overcome your situation; good luck.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  126. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 27, 2024 at 9:49 AM

    Hi ANDREW G,

    Thank you very much for reading our articles.

    You wanted to know a way to run the macro periodically without opening any file that will auto-refresh all the Excel files in a certain folder.

    Here, I will discuss a way to fulfill your requirements. But to run a macro you need to open an Excel file and insert a macro in it. After that, the macro will refresh all your files in the mentioned folder. We will use the Task Scheduler feature of Windows to run the macro periodically.

    1. First, insert the following macro in an Excel file named Refresh. The macro will be inserted into the workbook.
    2. Then, save and close the macro and workbook.
    3. The first part of the macro is a workbook event that will run whenever the workbook opens.
      Private Sub Workbook_Open()
      
          ThisWorkbook.Application.Visible = False
      
          Call AutoRefreshFolder
      
          ThisWorkbook.Application.Visible = True
      
          ThisWorkbook.Close
      
          Application.Quit
      
      End Sub
      
      Public Sub AutoRefreshFolder()
      
      Dim mrf As Object
      
      Dim mfolder As Object
      
      Dim mfile As Object
      
          mPath = "C:\Users\Alok Paul\Desktop\Softeko\Auto Refresh Excel File Without Opening\"
      
      Set mrf = CreateObject("Scripting.FileSystemObject")
      
      Set mfolder = mrf.GetFolder(mPath)
      
       With Application
      
              .DisplayAlerts = False
      
              .ScreenUpdating = False
      
              .EnableEvents = False
      
              .AskToUpdateLinks = False
      
       End With
      
       For Each mfile In mfolder.Files
      
          If Right(mfile.Name, 4) = "xlsx" Or Right(mfile.Name, 3) = "xls" Then
      
                  Workbooks.Open mPath & mfile.Name
      
                  ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
      
                  ActiveWorkbook.Close True
      
          End If
      
       Next
      
       With Application
      
              .DisplayAlerts = True
      
              .ScreenUpdating = True
      
              .EnableEvents = True
      
              .AskToUpdateLinks = True
      
       End With
      
      End Sub

    4. Type Task Scheduler in the Search box.
    5. Then click on the Open option of the application.
    6. In the Task Scheduler window, click the Create Task option from the Actions section.
    7. In the Create Task window, go to the General tab.
    8. Then, insert the name of the task in the Name box.
    9. Next, go to the Triggers tab and click on New.
    10. In the New Trigger window, mark Daily in the Settings section.
    11. Next in the Advanced settings section, mark Repeat task every and choose 5 minutes. You can any other time.
    12. Finally, press OK.
    13. Then, click on the New option in the Actions tab.
    14. The New Action window appears.
    15. Insert the path of the Excel application in the program/script section and the path of the Excel file containing the macro.
    16. Finally, press OK.
    17. After that close the Task Scheduler and the macro will run every 5 minutes to refresh the Excel files inside the mentioned folder.

    Best Regards,
    Alok Paul
    Team ExcelDemy

  127. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 27, 2024 at 9:15 AM

    Dear CARLOS,

    Thank you for sharing your problem with us. To print 7 graphs as a single image:

    1. Open the Excel file containing all the graphs and rearrange them to print as an image.
    2. Select the cells containing all the graphs.
    3. Right-click to open the Context menu and choose Copy.
    4. Then, open an image or document editing software like Microsoft Word.
    5. In Microsoft Word, right-click to get the Context menu.
    6. Hence, choose Picture from the Paste Options to paste the graphs as a picture.

    Following the steps, we will get an image like the picture below.

    1. Now, press CTRL+P to visit the Print window.
    2. Then, go to the Print tab, choose your Printer, and click Print.

    As a result, you will get all the graphs printed as a single image.

    Regards
    Wasim Akram
    Team ExcelDemy

  128. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 25, 2024 at 6:38 PM

    Hi Manisha

    It is working fine for me and returning 10:16. Please give us more details about the formula and/or the dataset you are using.

    Regards
    Niloy
    ExcelDemy

  129. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 25, 2024 at 12:40 PM

    Hello,

    Thank you for reaching out and sharing your experience. It seems like there might be an issue with the X-axis formatting.

    To address the x-axis displaying as 1900, try the following steps:

    Format X-Axis: After combining the scatter plots, right-click on the x-axis and select Format Axis. Check the settings to ensure the date format is correctly applied to your month values.

    Date Formatting: Make sure that the Month values on the x-axis are recognized as dates. You may need to format the cells containing the monthly data as dates if they are not already.

    Data Source: Double-check the data source for your scatter plot. Confirm that the Month values are correctly assigned to the x-axis.

    X-Axis Labels: Ensure that the x-axis labels correspond to the Month values. Adjust the labels if necessary.

    If you face the issues again, please provide more details about your data and the steps you followed, and I will do my best to help you.

    Please let us know if your problem is solved or not.

    Regards,
    Bishawajit Chakraborty
    ExcelDemy

  130. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 25, 2024 at 11:58 AM

    Hi ED,

    Thank you for your query. Unfortunately, directly importing your Yahoo Finance watchlist into Excel for stock details retrieval is not feasible due to technical limitations.

    Reason:

    Yahoo Finance watchlists are dynamic web pages primarily rendered by JavaScript, while Excel’s Data > From Web feature captures static HTML snapshots. This fundamental difference prevents Excel from capturing the interactive and user-specific nature of watchlists.

    As an alternative solution, I recommend downloading the template from this link. Add your company names from the watchlist to this template. This template facilitates tracking and decision-making for your listed stocks within Excel.

    We trust that this solution aligns with your interests and needs.

    Best regards,

    Ishrak Khan

    ExcelDemy

  131. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 25, 2024 at 11:25 AM

    Hi JAMES MARTIN,

    Thank you for your positive feedback and for taking the time to recreate the example from the article. I’m glad you found the non-VBA REGEX example useful for your learning. I rechecked the process, and it worked fine for me. Did you recreate the example on our provided Excel file or try it on another workbook? For our provided workbook, if you want to recreate another named range, then you might need another name or a space, as you mentioned. However, it’s always helpful to receive feedback. If you have any further questions or suggestions, feel free to let us know. Your input is appreciated!

    Regards
    Rafiul Hasan
    ExcelDemy

  132. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 25, 2024 at 11:06 AM

    Hi DINESH,

    Thanks for your comment. You can use the VBA code given below for the desired output.

    1. Copy the following VBA code and paste it into the Module window:
      Public Function FormatCurrencyText(ByVal inputNumber As String) As String
          Dim decimalPosition As Integer
          Dim placeArray As Variant
          Dim paisaText As String
          Dim numText As String
          Dim index As Integer
          Dim tempText As String
          Dim strTemp As String
          Dim resultText As String
          Dim loopCounter As Integer
      
          ' Change these values according to your requirements
          placeArray = Array("", "", " Thousand ", " Lacs ", " Crores ", " Trillion ", "", "", "", "")
      
          On Error Resume Next
          If inputNumber = "" Then
              FormatCurrencyText = ""
              Exit Function
          End If
      
          numText = Trim(Str(inputNumber))
          If numText = "" Then
              FormatCurrencyText = ""
              Exit Function
          End If
      
          resultText = ""
          loopCounter = 0
      
          If (numText > 999999999.99) Then
              FormatCurrencyText = "Digit exceeds the maximum limit"
              Exit Function
          End If
      
          decimalPosition = InStr(numText, ".")
      
          If decimalPosition > 0 Then
              If (Len(numText) - decimalPosition) = 1 Then
                  paisaText = CurrencyText_GetT(Left(Mid(numText, decimalPosition + 1) & "0", 2))
              ElseIf (Len(numText) - decimalPosition) > 1 Then
                  paisaText = CurrencyText_GetT(Left(Mid(numText, decimalPosition + 1), 2))
              End If
      
              numText = Trim(Left(numText, decimalPosition - 1))
          End If
      
          index = 1
      
          Do While numText <> ""
              If (index >= 2) Then
                  tempText = Right(numText, 2)
              Else
                  If (Len(numText) = 2) Then
                      tempText = Right(numText, 2)
                  ElseIf (Len(numText) = 1) Then
                      tempText = Right(numText, 1)
                  Else
                      tempText = Right(numText, 3)
                  End If
              End If
      
              strTemp = ""
              If Val(tempText) > 99 Then
                  strTemp = CurrencyText_GetH(Right(tempText, 3), loopCounter)
                  If Right(Trim(strTemp), 3) <> "Lac" Then
                      loopCounter = loopCounter + 1
                  End If
              ElseIf Val(tempText) <= 99 And Val(tempText) > 9 Then
                  strTemp = CurrencyText_GetT(Right(tempText, 2))
              ElseIf Val(tempText) < 10 Then
                  strTemp = CurrencyText_GetD(Right(tempText, 2))
              End If
      
              If strTemp <> "" Then
                  resultText = strTemp & placeArray(index) & resultText
              End If
      
              If index = 2 Then
                  If Len(numText) = 1 Then
                      numText = ""
                  Else
                      numText = Left(numText, Len(numText) - 2)
                  End If
              ElseIf index = 3 Then
                  If Len(numText) >= 3 Then
                      numText = Left(numText, Len(numText) - 2)
                  Else
                      numText = ""
                  End If
              ElseIf index = 4 Then
                  numText = ""
              Else
                  If Len(numText) <= 2 Then
                      numText = ""
                  Else
                      numText = Left(numText, Len(numText) - 3)
                  End If
              End If
      
              index = index + 1
          Loop
      
          If resultText = "" Then
              resultText = "No Rupees"
          End If
      
          If paisaText <> "" Then
              paisaText = " and " & paisaText & " Paise"
          End If
      
          FormatCurrencyText = resultText & paisaText
      End Function
      
      Function CurrencyText_GetH(ByVal inputStrH As String, ByVal loopCounter As Integer) As String
          Dim resultText As String
      
          If Val(inputStrH) < 1 Then
              CurrencyText_GetH = ""
              Exit Function
          Else
              inputStrH = Right("000" & inputStrH, 3)
      
              If Mid(inputStrH, 1, 1) <> "0" Then
                  If (loopCounter > 0) Then
                      resultText = CurrencyText_GetD(Mid(inputStrH, 1, 1)) & " Lac "
                  Else
                      resultText = CurrencyText_GetD(Mid(inputStrH, 1, 1)) & " Hundred "
                  End If
              End If
      
              If Mid(inputStrH, 2, 1) <> "0" Then
                  resultText = resultText & CurrencyText_GetT(Mid(inputStrH, 2))
              Else
                  resultText = resultText & CurrencyText_GetD(Mid(inputStrH, 3))
              End If
          End If
      
          CurrencyText_GetH = resultText
      End Function
      
      Function CurrencyText_GetT(ByVal inputTStr As String) As String
          Dim arrT1 As Variant
          Dim arrT2 As Variant
          Dim resultText As String
      
          arrT1 = Array("Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
          arrT2 = Array("", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
      
          resultText = ""
      
          If Val(Left(inputTStr, 1)) = 1 Then
              resultText = arrT1(Val(Mid(inputTStr, 2, 1)))
          Else
              If Val(Left(inputTStr, 1)) > 0 Then
                  resultText = arrT2(Val(Left(inputTStr, 1)) - 1)
              End If
      
              resultText = resultText & CurrencyText_GetD(Right(inputTStr, 1))
          End If
      
          CurrencyText_GetT = resultText
      End Function
      
      Function CurrencyText_GetD(ByVal inputDStr As String) As String
          Dim arr_1() As Variant
      
          arr_1 = Array(" One", " Two", " Three", " Four", " Five", " Six", " Seven", " Eight", " Nine", "")
      
          If Val(inputDStr) > 0 Then
              CurrencyText_GetD = arr_1(Val(inputDStr) - 1)
          Else
              CurrencyText_GetD = ""
          End If
      End Function
    2. Press Ctrl+S to save the code.
    3. Now, insert the formula in a cell and press Enter:
      =ConvertNumberToWords(Number)
      Or,
      =ConvertNumberToWords(Number containing cell reference)
      Like,
      =ConvertNumberToWords(54362543)
      Or,
      =ConvertNumberToWords(A1)

    I hope this user-defined function will solve your problem. Please let us know in the comment section if you have any other queries.

    Regards

    Maruf Hasan

    ExcelDemy

  133. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 25, 2024 at 10:57 AM

    Hi ISRAVEL,

    To achieve this in Excel, you can use the COUNTIF function to count the occurrences of each number and then check which numbers occur more than twice. Here’s how you can set up your Excel sheet:

    Assuming your numbers are in column A starting from A2, you can use the following formula in another cell to get the count of numbers that appear more than twice: =COUNTIF(A:A, A2)

    Drag down this formula for each number in your list. This will give you the count of each number.

    Then, you can use another column to check if the count is greater than 2: =IF(B2>2, "More than two", "Not more than two")

    Drag this formula down for each number in your list.

    This setup will give you a clear indication of which numbers occur more than twice in your list.

    I hope this comment will help you get your required output. Please let us know in the comment section if you have any other queries.

    Regards

    Maruf Hasan

    ExcelDemy

  134. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 25, 2024 at 10:51 AM

    Hello OLIVER,

    I hope you are doing well. Thank u for your query. Well, you can use the below formula to extract only the minimum value in the same cell.

    =MIN(IFERROR(VALUE(MID($B$4:$B$8, FIND(":", $B$4:$B$8) + 1, LEN($B$4:$B$8))), ""))

    Note: Change the range (B4:B8) according to your dataset.

    Hope this information will help you. Please let us know if there is any further query in the comment section.

    Best Regards,

    Afrina Nafisa 

    Exceldemy

  135. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 25, 2024 at 10:44 AM

    Hi Kim, thanks for reaching out. Here’s a solution to your query to run the VBA code from the command prompt.

    Here, I have used the VBA code shown in Method 4 of this article. The name of this macro is “Sheet3.SortPivotTableByValues” according to this article. Use it in your workbook and modify it according to your references.

    Now, create a Notepad/Text document file and copy the following code in it. Change the file location and macro name if needed. Keep in mind that this macro was written in a sheet. So the sheet name should be added before the name of the Sub procedure.

    Option Explicit
    
    On Error Resume Next 
    
    ExcelMacroExample
    
    Sub ExcelMacroExample()
    
      Dim xlApp
    
      Dim xlBook
    
      Set xlApp = CreateObject("Excel.Application")
    
      Set xlBook = xlApp.Workbooks.Open("C:\Users\DELL\Desktop\SortPivot.xlsm", 0, True)
    
      xlBook.Application.Visible = True
    
      xlApp.Run "Sheet26.SortPivotTableByValues"
    
      Set xlBook = Nothing
    
      Set xlApp = Nothing
    
    End Sub

    Now, save the file as a .vbs extension file. In this case, I named it RunMacro.vbs.

    After that, open the Command Prompt or cmd.exe. Press Windows + R buttons and type cmd in the Run dialog box and click OK.

    The Command Prompt will appear. Copy and paste the following line and press Enter: cd "C:\Users\DELL\Desktop”

    After that, copy and paste the following code in the next line and press Enter.

    cscript RunMacro.vbs

    Finally, your .xlsm file will open with the Pivot Table sorted. Hope this helps.

    Regards

    Meraz Al Nahian

    ExcelDemy

  136. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 25, 2024 at 10:14 AM

    Hi Chua!

    It brings me great joy to know that you found our advice helpful. You are most welcome. As for troubleshooting your problem, it would be best if you could provide us with the Excel file. However, you could use the following steps to try to solve your problem on your own.

    1. Go to your workbook. Right-click on the sheet tab where you are performing your calculation and select View Code.
    2. Inside the code writing space, write the following code:
      Private Sub Worksheet_Change(ByVal Target As Range)
      
          Call ForcedReCalculation
      
      End Sub
      
      Sub ForcedReCalculation()
      
          Application.CalculateFull
      
      End Sub
    3. Save your workbook (as .xlsm format)
    4. Rerun your calculation.

    If your problem is not yet solved, please join our ExcelDemy Forum and post this problem with your Excel file attached to it.

    Regards,

    Nafis

    ExcelDemy

  137. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 25, 2024 at 10:04 AM

    Dear MARCOS,

    Thank you for your query regarding the convertible bond pricing model presented in this article. Your query is valid, convertible bonds are bonds with the option to be converted into common stock. The conversion price refers to the price per share at which a convertible security (such as corporate bonds) can be converted into common stock.

    The formula for conversion pricing is: Conversion Price = Market Price of Convertible Bond / Conversion Ratio

    To find the conversion price, you need the “Conversion Ratio”, which is the number of shares that investors receive upon conversion. You can divide the Bond Value (C17) by the Conversion Ratio (C18) to find the Conversion Price (C19) as illustrated below.

    I hope this clarification is helpful. If you have any further questions, please feel free to ask.

    Kind Regards,

    Sumaiya Mirza

    ExcelDemy

  138. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 25, 2024 at 9:50 AM

    Dear DORABABU

    Thank you for your comment and your insightful observation. You are correct in pointing out that making prepayments towards the principal amount of a home loan can indeed result in a reduction in the total number of EMIs. This is an important consideration and can have a significant impact on the overall loan repayment timeline.

    To incorporate this feature into the home loan EMI calculator, you can make the following adjustments to the existing formula:

    Update Total Number of Months (N):

    Instead of fixing the total number of months as G4×12, you can dynamically adjust it based on the prepayments made. You can introduce a new variable, say M, representing the total number of months after considering prepayments. The formula for M would be: M=G4×12−Number of Prepayment Months

    Modify the PMT Function for EMI Calculation:

    Update the formula for EMI calculation (in cell C7) to reflect the adjusted total number of months (M): =ABS(PMT(E4/12,M,F7))

    By making these changes, the calculator will dynamically adjust the total number of EMIs based on the prepayments made, providing you with a more accurate representation of the impact of principal payments on the loan tenure.

    I hope this addresses your query. If you have any further questions or if there’s anything else you’d like to discuss, please feel free to ask.

    Best Regards

    Sumaiya Mirza

    ExcelDemy

  139. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 22, 2024 at 6:32 PM

    Dear Khristine,

    Thank you for your comment. I have made some modifications to the code. Now, when you run it, a file picker dialog box will appear, allowing you to select a file. Once you’ve chosen the file, the Outlook app will open and a new email will be generated with the file attached. You can then manually send the email at your convenience. Here is the updated code:

    Sub Paste_Range_Outlook()
    
        Dim rng As Range
    
        Dim Outlook As Object
    
        Dim OutlookMail As Object
    
        Set rng = Nothing
    
        On Error Resume Next
    
        Set rng = Selection.SpecialCells(xlCellTypeVisible)
    
        On Error GoTo 0
    
        If rng Is Nothing Then
    
            MsgBox "Not a range or protected sheet" & _
    
                   vbNewLine & "please correct and try again.", vbOKOnly
    
            Exit Sub
    
        End If
    
        With Application
    
            .EnableEvents = False
    
            .ScreenUpdating = False
    
        End With
    
        ' Create a file dialog object
    
        Set FileDialog = Application.FileDialog(msoFileDialogFilePicker)
    
       
    
        ' Allow the user to select only one file
    
        FileDialog.AllowMultiSelect = False
    
       
    
        ' Display the file dialog box
    
        If FileDialog.Show = -1 Then
    
            ' User selected a file
    
            SelectedFile = FileDialog.SelectedItems(1)
    
        End If
    
        Set Outlook = CreateObject("Outlook.Application")
    
        Set OutlookMail = Outlook.CreateItem(0)
    
        On Error Resume Next
    
        With OutlookMail
    
            .To = ""
    
            .CC = ""
    
            .BCC = ""
    
            .Subject = "Excel Data you requested for"
    
            .HTMLBody = RangetoHTML(rng)
    
            .Attachments.Add SelectedFile
    
            .Display   'or use .Send
    
        End With
    
        On Error GoTo 0
    
        With Application
    
            .EnableEvents = True
    
            .ScreenUpdating = True
    
        End With
    
        Set OutlookMail = Nothing
    
        Set Outlook = Nothing
    
    End Sub
    
    Function RangetoHTML(rng As Range)
    
        Dim obj As Object
    
        Dim txtstr As Object
    
        Dim File As String
    
        Dim WB As Workbook
    
        File = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
    
        rng.Copy
    
        Set WB = Workbooks.Add(1)
    
        With WB.Sheets(1)
    
            .Cells(1).PasteSpecial Paste:=8
    
            .Cells(1).PasteSpecial xlPasteValues, , False, False
    
            .Cells(1).PasteSpecial xlPasteFormats, , False, False
    
            .Cells(1).Select
    
            Application.CutCopyMode = False
    
            On Error Resume Next
    
            .DrawingObjects.Visible = True
    
            .DrawingObjects.Delete
    
            On Error GoTo 0
    
        End With
    
        With WB.PublishObjects.Add( _
    
             SourceType:=xlSourceRange, _
    
             Filename:=File, _
    
             Sheet:=WB.Sheets(1).Name, _
    
             Source:=WB.Sheets(1).UsedRange.Address, _
    
             HtmlType:=xlHtmlStatic)
    
            .Publish (True)
    
        End With
    
        Set obj = CreateObject("Scripting.FileSystemObject")
    
        Set txtstr = obj.GetFile(File).OpenAsTextStream(1, -2)
    
        RangetoHTML = txtstr.readall
    
        txtstr.Close
    
        RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
    
                              "align=left x:publishsource=")
    
        WB.Close savechanges:=False
    
        Kill File
    
        Set txtstr = Nothing
    
        Set obj = Nothing
    
        Set WB = Nothing
    
    End Function

    I hope it will do the job for you.

    Regards
    Aniruddah Alam
    ExcelDemy

  140. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 22, 2024 at 6:20 PM

    Hello Miriam,

    Thank you for sharing your problem with us. We are sorry you’re experiencing issues with the Excel VBA script for getting cell color.

    Here are a few steps you can take to address the problem:

    1. First, to get your desired results, ensure you’re using Microsoft 365. Activate it on your PC to use the given methods for obtaining color indexes.
    2. Then, follow the below steps to Check Macro Security Settings:
      • Make sure that your Excel settings allow the execution of macros. Go to File > Options > Trust Center > Trust Center Settings > Macro Settings and ensure that “Enable all macros” or “Enable all macros with notification” is selected.

    Please let us know if your problem is solved or not.

    Regards,
    Bishawajit Chakraborty
    ExcelDemy

  141. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 22, 2024 at 6:13 PM

    Hello ROSEMARIE OLIVERA,

    Thanks for your response. Yes, you can apply the CELL function to the filtered table.

    Regards
    MD Naimul Hasan

  142. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 22, 2024 at 6:10 PM

    Hello HANS ENGELS,

    Thanks for your query. To extract data from the website to Excel automatically when the website requires login credentials, you must apply web scrapping techniques and automation tools.

    First, you need to understand the web structure of that website and then use web scraping tools to get access to that website and extract data based on your needs.

    Regards
    MD Naimul Hasan
    ExcelDemy

  143. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 22, 2024 at 6:00 PM

    Hi Sandhya,

    You can add 45 days with the invoice date and use it as a replacement for TODAY() in any of the formulas from above. Or, you can use a simple IF formula.
    Here is an example, the invoice date is in cell C13. Use the formula: =IF(C5>$C$13+45,"Overdue","Not Overdue")

    Make sure to use the correct reference style if you intend to drag the fill handle.

    Regards
    Abrar-ur-Rahman Niloy
    ExcelDemy

  144. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 22, 2024 at 5:42 PM

    Dear NORTH80,

    Thank you very much for reading our articles.
    You have mentioned that, when pulling historical dividend information you faced a problem. The problem is:
    “Expression.Error: The column ‘Dividends’ of the table wasn’t found.
    Details:
    Dividends”

    Here is the modified query code to solve the mentioned problem.

    
    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&"?period1"&StartDate&"&period2"&EndDate&"&interval=1d&events=div&includeAdjustedClose=true"),[Delimiter=",", Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.None]),
        #"Use First Row as Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
        #"Change Type" = Table.TransformColumnTypes(#"Use First Row as Headers",{{"Date", type date}, {"Dividends", type number}})
    in
        #"Change Type"
    

    If you face further problems, please write in the comment box.

    Best Regards,
    Alok Paul
    Team ExcelDemy

  145. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 22, 2024 at 5:16 PM

    Hello VARUN

    Thanks for reaching out and sharing your requirements. Your appreciation means a lot to us.

    Yes, We can provide information on the Indian stock market with charts in dollars (INR) instead of dollars ($).

    You can download this template below for your help. Additionally, I’m also writing down the steps so that you can modify it yourself.

    Download Excel file: Track-Indian-Stock-Market.xlsx

    Use these steps for indian stock market tracking:

    1. Add Indian Stock company Names.
    2. Select the range (i.e. B5:B9).
    3. Go to the Data tab > Data Types group > Stocks.
      It will automatically update the Stock Tracker names. However, the values will show an Error Warning.
    4. Select the cell (i.e.C5) > Click the drop-down menu beside the error warning > Click Update format.
      It will automatically update the price in INR format.
    5. Use the Fill handle feature to apply the change in the column.

    Repeat the same process for other columns where necessary till J or Beta Column.

    To update the format of Our Stock information section (Column K to P), follow these steps:

    1. Select the Current Price Column (i.e. B5:B9)
    2. Go to the Home tab > Clipboard group > Format Painter icon.
    3. Select the range (i.e. L5:M9) to apply the format painter. You will notice the paint icon beside the cursor.

    It will apply the same currency format to the applied range. Use the same process for Column O and P.

    To update the format with Conditional formatting of Current Investment Column, copy the formatting of Changes (INR) column using Format Painter and paste it. It will automatically update both currency format and conditional formatting.

    Note: The charts will update automatically.

    Hope this helps you out.

    Regards,

    Ishrak Khan
    ExcelDemy

  146. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 22, 2024 at 5:11 PM

    Hi Zafar Iqbal,

    In response to your request for some data entry work files, we have sent a PDF file for you to practice. Kindly check your email. You can also download the practice file provided with the article. Stay connected with ExcelDemy.

    Regards
    Rafiul Hasan
    ExcelDemy

  147. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 22, 2024 at 4:58 PM

    Hi Laurie!

    It feels good to know that you found our content understandable. Judging from you query, I think you want a continuing compound interest formula where the loan doesn’t have a specific term/year.

    The term continuous compound interest is used to emphasize the continuous compounding of interest, as opposed to discrete compounding periods. Here,  the compounding frequency becomes infinite, resulting in a continuous growth formula. Let us look at the formula below:

    A=P0ert 

    Where,

    • A is the future value,
    • P0 is the principal amount,
    • r is the annual interest rate,
    • t is the time in years, and
    • e is the mathematical constant approximately equal to 2.71828.

    For example, if you invest $1,000 at an annual interest rate of 5% continuously compounded for 2 years, the future value can be calculated using the continuous compounding formula.

    Regards,
    Nafis
    ExcelDemy

  148. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 22, 2024 at 4:44 PM

    Hello LAKSHMI,

    I hope you are doing well. Thank you for your query. You can combine sheets row-wise using the VBA code provided in the first method of this article. (Merge Data Sets from Multiple Sheets into One Sheet with VBA Row-wise). Once you go through this method of merging data sets, you will know where to change the code as the details are written in the “Note”.

    Best Regards,
    Afrina Nafisa
    Exceldemy

  149. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 22, 2024 at 4:37 PM

    Hello Lea, thanks for reaching out. Here’s a solution for your query.

    
    Private Sub Worksheet_Change(ByVal Target As Range)
        On Error Resume Next
        Application.EnableEvents = False
        
        ' Check if the changed range is in Sheet1 A1:A67
        If Not Intersect(Target, Sheets("Sheet1").Range("A1:A67")) Is Nothing Then
            ' Update data in Sheet2 A26:A85
            Sheets("Sheet2").Range("A26:A85").Value = Sheets("Sheet1").Range("A1:A67").Value
            
            ' Copy formatting from Sheet1 A1:A67 to Sheet2 A26:A85
            Sheets("Sheet1").Range("A1:A67").Copy
            Sheets("Sheet2").Range("A26:A85").PasteSpecial Paste:=xlPasteFormats
            Application.CutCopyMode = False
        End If
        
        Application.EnableEvents = True
        On Error GoTo 0
    End Sub
    

    Procedure:

    1. Right click on the Sheet1 tab of your workbook and select View Code.

    2. Paste the code and save the workbook.

    3. Now, if you do any formatting and insert a value in the range of A1:A67 of Sheet1, you can see the values and formatting get copied in the Sheet2.

    Regards
    Meraz Al Nahian
    ExcelDemy

  150. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 22, 2024 at 4:29 PM

    Hello ODMA

    Thanks for reaching out and sharing your queries. The previous question by ABIGAYLE PAULSON was to automate the idea that the filtering algorithm will be applied if the drop-down list in Sheet8 is changed. After analyzing your requirements, I understand that you do not want to use the filter algorithm if the drop-down is empty.

    I have developed an Excel VBA Worksheet_Change Event that will fulfil your goal. All you need is to paste the following code in the sheet module of Sheet8 (the sheet that contains the drop-down).

    Excel VBA Event Procedure:

    
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        
        Dim category As Range
        Dim dropdownCell As Range
        Dim ws7 As Worksheet
        Dim ws8 As Worksheet
        
        Set ws7 = Worksheets("Sheet7")
        Set ws8 = Worksheets("Sheet8")
        
        Set dropdownCell = ws8.Range("C2")
        
        If Not Intersect(Target, dropdownCell) Is Nothing Then
    
            If dropdownCell.Value = "" Then
                ws7.AutoFilterMode = False
            Else
                Set category = dropdownCell
                With ws7.Range("B4:G13")
                    .AutoFilter Field:=2, Criteria1:=category.Value
                End With
            End If
        End If
    
    End Sub
    

    OUTPUT Overview:

    Hopefully, the event procedure will help; good luck.

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  151. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 22, 2024 at 4:12 PM

    Hello POOJA

    Thanks for reaching out and sharing your requirements with such clarity. The problem you want a solution for can quickly be developed using several Excel VBA Sub-procedures, Event Procedures, and a UserForm.
    Here is an algorithm you can follow:

    1. You have to declare a public variable to store the file path.

    2. Adding sheet names to combo box list.

    3. Displaying the data in a list box.

    4. Copying the selected columns to display the Data Sheet.

    OUTPUT Overview:

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  152. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 22, 2024 at 4:08 PM

    Hello ANDREW

    Yes, that’s correct. In both methods described in the article, you would need to open the file containing the macro to auto-refresh the target Excel files. However, once the macro is executed, it will open and refresh the specified Excel files without manually opening them individually.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  153. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 11, 2024 at 4:47 PM

    Hello GRAHAM

    Thanks for reading our blogs and sharing your requirements. You wanted to display the range-converted images horizontally in the email body. This can be achieved by slightly modifying the article’s Excel VBA code.

    OUTPUT OVERVIEW:

    Excel VBA Code:

    
    Sub PasteMultipleRangeinMail()
        
        Dim FilePath As String
        Dim Outlook As Object
        Dim OutlookMail As Object
        Dim HTMLBody As String
        Dim rng As Range
        Dim Sheet As Worksheet
        Dim AcSheet As Worksheet
        Dim FileName As String
        Dim Src As String
        
        On Error Resume Next
        FilePath = Environ$("temp") & "\RangeImage\"
        
        If Len(VBA.Dir(FilePath, vbDirectory)) = False Then
          VBA.MkDir FilePath
        End If
        
        Set AcSheet = Application.ActiveSheet
        
        For Each Sheet In Application.Worksheets
            Sheet.Activate
            Set rng = Sheet.Application.Selection
            If rng.Cells.Count > 1 Then
                Call createJpg(Sheet.Name, rng.Address, "DashboardFile" & VBA.Trim(VBA.Str(Sheet.Index)))
            End If
        Next
        
        AcSheet.Activate
        With Application
            .Calculation = xlManual
            .ScreenUpdating = False
            .EnableEvents = False
        End With
        
        Set Outlook = CreateObject("outlook.application")
        Set OutlookMail = Outlook.CreateItem(olMailItem)
        Src = ""
        
        FileName = Dir(FilePath & "*.*")
        Do While FileName <> ""
            Src = Src + "<img src='cid:" + FileName + "'>" ' Display images horizontally
            FileName = Dir
            If FileName = "" Then Exit Do
        Loop
        
        HTMLBody = "<span LANG=EN>" _
                    & "<p class=style1><span LANG=EN><font FACE=Times New Roman SIZE=4>" _
                    & "Dear GRAHAM," _
                    & "<br>" _
                    & "This is the Excel data you requested for:<br> " _
                    & "<br>" _
                    & Src _
                    & "<br><br>Best Regards</font></span>" _
                    & "<br>Lutfor Rahman Shimanto</font></span>" _
                    & "<br>Excel & VBA Developer</font></span>" _
                    & "<br>ExcelDemy</font></span>"
    
        With OutlookMail
            .Subject = "Displaying the Range-Converted Images horizontally in the Email Body"
            .HTMLBody = HTMLBody
            FileName = Dir(FilePath & "*.*")
            Do While FileName <> ""
                .Attachments.Add FilePath & FileName, olByValue
                FileName = Dir
                If FileName = "" Then Exit Do
            Loop
            .To = "[email protected]"
            .CC = ""
           .Display
        End With
        
        If VBA.Dir(FilePath & "*.*") <> "" Then
            VBA.Kill FilePath & "*.*"
        End If
    
    End Sub
    
    Sub createJpg(SheetName As String, rngAddrss As String, nameFile As String)
        
        Dim rngPic As Range
        ThisWorkbook.Activate
        Worksheets(SheetName).Activate
        
        Set rngPic = ThisWorkbook.Worksheets(SheetName).Range(rngAddrss)
        rngPic.CopyPicture
        
        With ThisWorkbook.Worksheets(SheetName).ChartObjects.Add(rngPic.Left, rngPic.Top, rngPic.Width, rngPic.Height)
            .Activate
            .Chart.Paste
            .Chart.Export Environ$("temp") & "\RangeImage\" & nameFile & ".jpg", "JPG"
        End With
        
        Worksheets(SheetName).ChartObjects(Worksheets(SheetName).ChartObjects.Count).Delete
    
        Set rngPic = Nothing
    
    End Sub
    

    I have another present for you. If you ever want to attach the images generated from the selected ranges, you can use the following code:

    
    Sub AttachMultipleRangeAsImageInMail()
    
        Dim FilePath As String
        Dim Outlook As Object
        Dim OutlookMail As Object
        Dim rng As Range
        Dim Sheet As Worksheet
        Dim FileName As String
    
        On Error Resume Next
        FilePath = Environ$("temp") & "\RangeImage\"
    
        If Len(VBA.Dir(FilePath, vbDirectory)) = False Then
            VBA.MkDir FilePath
        End If
    
        For Each Sheet In Application.Worksheets
            Set rng = Sheet.UsedRange
    
            If Not rng Is Nothing Then
                Call createJpg(Sheet.Name, rng, "DashboardFile" & VBA.Trim(VBA.Str(Sheet.Index)))
            End If
        Next
    
        Set Outlook = CreateObject("outlook.application")
        Set OutlookMail = Outlook.CreateItem(olMailItem)
    
        FileName = Dir(FilePath & "*.*")
        Do While FileName <> ""
            OutlookMail.Attachments.Add FilePath & FileName
            FileName = Dir
        Loop
    
        With OutlookMail
            .Subject = "Your Subject Here"
            .Body = "Dear Concerned," & vbCrLf & _
                    "This is the Excel data you requested for." & vbCrLf & vbCrLf & _
                    "Best Regards!"
            .To = "[email protected]"
            .Display
        End With
    
        If VBA.Dir(FilePath & "*.*") <> "" Then
            VBA.Kill FilePath & "*.*"
        End If
    
    End Sub
    
    Sub createJpg(SheetName As String, rng As Range, nameFile As String)
    
        Dim rngPic As Range
    
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = "TempSheet"
        Sheets("TempSheet").Activate
        rng.Copy
        ActiveSheet.Paste
        Set rngPic = ActiveSheet.UsedRange
    
        With ActiveSheet.ChartObjects.Add(rngPic.Left, rngPic.Top, rngPic.Width, rngPic.Height)
            .Chart.Paste
            .Chart.Export Environ$("temp") & "\RangeImage\" & nameFile & ".jpg", "JPG"
        End With
    
        Application.DisplayAlerts = False
        Sheets("TempSheet").Delete
        Application.DisplayAlerts = True
    
        Set rngPic = Nothing
    
    End Sub
    

    Hopefully, the codes will help in various situations. I have also attached the solution workbook; good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  154. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 11, 2024 at 3:20 PM

    Hello RAMI

    Thanks for visiting our blog and sharing your queries. You want to create multiple stopwatches in a sheet. Of course, the requirement is very much achievable.

    I have displayed four stopwatches in the B4, F4, B15, and F15 cells.

    OUTPUT OVERVIEW:

    Follow these steps:

    Press Alt+F11 >> Click on Insert followed by Module >> Insert the following code in the module >> Save.

    
    Option Explicit
    
    Public countDown1 As Date
    Public countDown2 As Date
    Public countDown3 As Date
    Public countDown4 As Date
    
    'For Stopwatch 1:
    Sub StartTimer1()
        countDown1 = Now + TimeValue("00:00:01")
        Range("B4") = Range("B4") + TimeValue("00:00:01")
        Application.OnTime countDown1, "StartTimer1"
    End Sub
    Sub ResetTimer1()
        Range("B4") = TimeValue("00:00:0")
    End Sub
    Sub StopTimer1()
        Application.OnTime EarliestTime:=countDown1, Procedure:="StartTimer1", Schedule:=False
    End Sub
    
    'For Stopwatch 2:
    Sub StartTimer2()
        countDown2 = Now + TimeValue("00:00:01")
        Range("F4") = Range("F4") + TimeValue("00:00:01")
        Application.OnTime countDown2, "StartTimer2"
    End Sub
    Sub ResetTimer2()
        Range("F4") = TimeValue("00:00:0")
    End Sub
    Sub StopTimer2()
        Application.OnTime EarliestTime:=countDown2, Procedure:="StartTimer2", Schedule:=False
    End Sub
    
    'For Stopwatch 3:
    Sub StartTimer3()
        countDown3 = Now + TimeValue("00:00:01")
        Range("B15") = Range("B15") + TimeValue("00:00:01")
        Application.OnTime countDown3, "StartTimer3"
    End Sub
    Sub ResetTimer3()
        Range("B15") = TimeValue("00:00:0")
    End Sub
    Sub StopTimer3()
        Application.OnTime EarliestTime:=countDown3, Procedure:="StartTimer3", Schedule:=False
    End Sub
    
    
    'For Stopwatch 4:
    Sub StartTimer4()
        countDown4 = Now + TimeValue("00:00:01")
        Range("F15") = Range("F15") + TimeValue("00:00:01")
        Application.OnTime countDown4, "StartTimer4"
    End Sub
    Sub ResetTimer4()
        Range("F15") = TimeValue("00:00:0")
    End Sub
    Sub StopTimer4()
        Application.OnTime EarliestTime:=countDown4, Procedure:="StartTimer4", Schedule:=False
    End Sub
    

    I hope you have learned how to create multiple stopwatches in a sheet. I am also attaching the solution workbook for better understanding; good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  155. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 11, 2024 at 1:59 PM

    Hello SINTA R

    Thanks for reaching out and sharing your queries. The formula you have mentioned is almost correct. However, you miss to insert the <> sign somehow in your formula.

    You can apply the following formulas:
    For Important & Urgent:
    =IFERROR(INDEX($C$4:$C$30,SMALL(IF((LEN($C$4:$C$30)<>0)*($D$4:$D$30="Yes")*($E$4:$E$30="Yes"),ROW($C$4:$C$30)-ROW($C$3),""),ROW(C4)-ROW($C$3))),"")
    For Important & Not Urgent:
    =IFERROR(INDEX($C$4:$C$30,SMALL(IF((LEN($C$4:$C$30)<>0)*($D$4:$D$30="Yes")*($E$4:$E$30="No"),ROW($C$4:$C$30)-ROW($C$3),""),ROW(C4)-ROW($C$3))),"")
    For Not Important & Urgent:
    =IFERROR(INDEX($C$4:$C$30,SMALL(IF((LEN($C$4:$C$30)<>0)*($D$4:$D$30="No")*($E$4:$E$30="Yes"),ROW($C$4:$C$30)-ROW($C$3),""),ROW(C4)-ROW($C$3))),"")
    For Not Important & Not Urgent:
    =IFERROR(INDEX($C$4:$C$30,SMALL(IF((LEN($C$4:$C$30)<>0)*($D$4:$D$30="No")*($E$4:$E$30="No"),ROW($C$4:$C$30)-ROW($C$3),""),ROW(C4)-ROW($C$3))),"")

    OUTPUT OVERVIEW:

    Hopefully, you have found your solution. I have also attached the solution workbook for better understanding; good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    ExcelDem
    y

  156. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 11, 2024 at 1:08 PM

    Hello DORY

    Thanks a Ton! for your nice words. Your appreciation means a lot to us.

    You would like to copy only a specific range from the source workbook. I am presenting an Excel VBA Sub-procedure that will fulfil your requirements.

    OUTPUT OVERVIEW:

    Excel VBA Sub-procedure:

    
    Sub CopySheets()
        
        Dim Source As String
        Dim Destination As Workbook
        
        Source = "C:\Users\PC 50\Downloads\SOURCE.xlsx"
        Set Destination = ThisWorkbook
        
        Dim Worksheets As Variant
        ReDim Worksheets(3)
        
        Worksheets(1) = "Sheet1"
        Worksheets(2) = "Sheet2"
        Worksheets(3) = "Sheet3"
        
        Dim i As Variant
        Dim rng As Range
        
        For i = 1 To UBound(Worksheets)
            Set rng = Workbooks.Open(Source).Sheets(Worksheets(i)).Range("A1:D11")
            rng.Copy Destination:=Destination.Sheets(Worksheets(i)).Range("A1")
        Next i
        
    End Sub
    

    Hopefully, you will like the example and the sub-procedure. Good luck.

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  157. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 11, 2024 at 11:33 AM

    Hello JAMES

    Thanks for reaching out and sharing your queries. To return the text value of two cells in different columns if they match, you can combine the EXACT, IF and ISTEXT functions.

    Excel Formula: =IF(AND(ISTEXT(A2), ISTEXT(B2), EXACT(A2, B2)), A2:B2, "No Match")

    So, in simpler terms, the formula checks if both cells A2 and B2 contain text and if the text in both cells is the same. If they are, it returns the values from cells A2 and B2 together. If not, it returns “No Match”.

    OUTPUT:

    Hopefully, the idea will help you; good luck.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  158. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 7, 2024 at 6:24 PM

    Hello BRYAN

    Thanks for visiting our blog. All the methods described in the article are working perfectly on our end. It is important to remember the intended effect you want to see when you have printed the sheet.

    OUTPUT OVERVIEW:

    So, I recommend you reread the article; good luck.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  159. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 7, 2024 at 5:40 PM

    Hello GARY SHERMAN

    Thanks for reaching out and sharing your problem. You want to find the total number of sheets for each material type. To do so, you can use the SUMIF function to reach your goal.

    Follow these steps:

    1. Select an empty cell.

    2. Insert the following formula: =SUMIF($A$2:$A$22,$D5, $B$2:$B$22)

    3. Hit Enter.

    4. Use the Fill Handle icon to copy the formula down.

    As a result, you will see an output like the following image.

    Hopefully, the idea will help; Good luck.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  160. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 7, 2024 at 5:00 PM

    Dear Kelly

    Thank you so much for your kind words! It’s fantastic to hear that our tutorial has been helpful to you. You’ve already made quite an impact in your new role, becoming the Excel expert in your office – that’s quite an accomplishment!

    Let us know if you ever need more help with Excel or anything else. We’re here for you. Best wishes for your continued success at the mom & pop shop!

    Best Regards
    Lutfor Rahman Shimanto
    ExcelDemy Team

  161. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 7, 2024 at 4:48 PM

    Hello CHRISTOF

    Thanks for sharing your queries. Depending on your OS Version, the User-defined functions may give this type of error. Ensure you run a 64-bit Windows, and let us know if you still face the error.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  162. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 7, 2024 at 4:26 PM

    Hi Karthick

    Thank you for reaching out with your inquiry! Designing a shift schedule for a 4-member team roster with diverse shift patterns can be challenging.

    Each shift, A, B, C, and D, has a schedule for weekdays, weekends, and special arrangements on Wednesdays. Setting these parameters upfront allows you to assign team members to their shifts more efficiently.

    On Wednesdays, all four members are active but in different roles. It would be best if you had a flexible framework to handle this. One idea is to create a dynamic formula that adjusts shift assignments based on the day of the week and specific requirements. This might involve using conditional logic to distribute team members across day and night shifts, ensuring we have enough coverage while using resources effectively.

    Don’t forget to explore the resources in the article, including the practice workbook, to gain hands-on experience with building and refining your shift scheduler. Good luck.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  163. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 7, 2024 at 4:05 PM

    Hello ERAL

    Good to see you again. You wanted shifts like, Instead of using D1, D2, D3, D4, OFF, A1, A2, A3, OFF, is it possible to use just like this only D, D, D, D, OFF, A, A, A, OFF. Technically, it is impossible without VBA, but I have found a trick to reach your goal. I have used extra spaces in the Settings_Shift_Legend to make the shifts unique.

    OUTPUT OVERVIEW

    Hopefully, I will like the idea. I have attached the solution workbook to help you understand better; good luck.

    Download Solution Workbook

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  164. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 7, 2024 at 3:20 PM

    Hello FRED FUTCH

    Thanks for sharing your problem. You might be experiencing some formatting issues in Excel when inputting numbers.

    Ensure that the cells where you are typing numbers are formatted as General or Number. Sometimes, Excel may automatically apply a different format if it detects a specific pattern in the data.

    Sometimes, Excel’s behaviour can be influenced by the regional settings on your computer. Make sure that your computer’s regional settings are configured correctly.

    Hopefully, the idea will help in your situation; good luck.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  165. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 7, 2024 at 2:09 PM

    Hello FRANKY LAM

    Thanks for visiting our blog and sharing your problem. Based on your problem, it seems the VBA User-defined function named Code128 is not recalculating on your system.

    So, you can try forced fully recalculating using the CalculateFull property of the Excel Application.

    Follow these steps: Right-click on the sheet name tab >> Click on View Code >> Paste the following code in the sheet module and Save:

    
    Private Sub Worksheet_Change(ByVal Target As Range)
        Call ForcedReCalculation
    End Sub
    
    Sub ForcedReCalculation()
        Application.CalculateFull
    End Sub
    

    Hopefully, the idea will help you good luck.

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  166. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 7, 2024 at 1:38 PM

    Hello Samir

    Thanks for visiting our blog and posting an interesting comment. You want to convert a number (e.g., 10245) into a string where each digit of the number is represented by its word form (e.g., “One Zero Two Four Five”).

    I am delighted to inform you that I have developed an Excel VBA User-defined function named NumberToString to fulfil your goal. I am using the function in a Sub-procedure. However, you can also use it in Excel cells like other worksheet functions.

    OUTPUT OVERVIEW:

    Excel VBA Code:

    
    Function NumberToString(ByVal num As String) As String
        
        Dim result As String
        Dim i As Integer
        Dim digit As String
        
        Dim digits() As String
        digits = Split("Zero,One,Two,Three,Four,Five,Six,Seven,Eight,Nine", ",")
        
        For i = 1 To Len(num)
            digit = Mid(num, i, 1)
            result = result & digits(Val(digit)) & " "
        Next i
        
        NumberToString = Trim(result)
    
    End Function
    
    Sub TestNumberToWords()
        
        Dim num As Long
        num = 146897
        Debug.Print NumberToString(CStr(num))
    
    End Sub
    

    Hopefully, the user-defined function will help you reach your goal; good luck.

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  167. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 7, 2024 at 1:00 PM

    Hello ASNATH

    Thanks for reaching out and posting your comment. You want to know how to use the COUNT, IF and AVERAGE functions in a formula. I am presenting an example where I will form a formula using these functions.

    OUTPUT OVERVIEW:

    Suppose your dataset has Employee ID, Name, Department, Age, and Salary columns. You want to find out the average salary based on department. You can get the result using only the AVERAGEIF function, but if the department does not exist, it will provide a #DIV/0! Error. So, to handle this type of situation, you can combine COUNT, IF, SUM and AVERAGE functions:

    =IF(SUM(IF(C2:C11=B14, COUNT(1),""))<=0,"There is no employee.",AVERAGE(IF(C2:C11=B14, E2:E11)))

    Hopefully, the idea will help you. Good luck.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  168. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 7, 2024 at 11:32 AM

    Hello JAN ZÁKOSTELSKÝ

    Thanks for reaching out and sharing your problem. You want to compare two Excel rows: one that remains constant and another that changes. You need to receive an email whenever a number in any cell of the changing row becomes lower than the value in the corresponding cell of the constant row.

    I am delighted to inform you that I have developed an Excel VBA Sub-procedure and an Event Procedure. These will fulfil your requirements.

    Follow these steps:

    1. Right-click on the sheet name tab.

    2. Click on View Code.

    3. Insert the following code in the sheet module and Save.

    
    Private Sub Worksheet_Change(ByVal Target As Range)
        
        Dim staticRow As Range
        Dim changingRow As Range
        Dim cellStatic As Range
        Dim cellChanging As Range
        Dim emailSubject As String
        Dim emailBody As String
        Dim emailTo As String
        
        Set staticRow = Range("A2:D3")
        Set changingRow = Range("A3:D3")
        
        emailTo = "[email protected]"
        
        If Not Intersect(Target, changingRow) Is Nothing Then
    
            For Each cellChanging In Intersect(Target, changingRow)
                Set cellStatic = staticRow.Cells(cellChanging.Column - changingRow.Column + 1)
    
                If IsNumeric(cellStatic.Value) And IsNumeric(cellChanging.Value) Then
                    If cellChanging.Value < cellStatic.Value Then
                        emailSubject = "Value Below Threshold"
                        emailBody = "The value in cell " & cellChanging.Address & " is below the threshold (" & cellStatic.Value & ")."
                        SendEmail emailTo, emailSubject, emailBody
                        Exit Sub
                    End If
                End If
            Next cellChanging
        End If
    End Sub
    
    Sub SendEmail(emailTo As String, subject As String, body As String)
    
        Dim outlookApp As Object
        Dim emailItem As Object
        
        Set outlookApp = CreateObject("Outlook.Application")
        Set emailItem = outlookApp.CreateItem(0)
        
        With emailItem
            .To = emailTo
            .subject = subject
            .body = body
            .Display
        End With
        
        Set emailItem = Nothing
        Set outlookApp = Nothing
    
    End Sub
    

    4. Return to the sheet and make changes to see an output like the following GIF.

    Hopefully, the idea will help you; good luck.

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  169. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 7, 2024 at 10:07 AM

    Hello VICTORIA SHARP

    Thank you for reaching out and sharing your requirements. Based on your comment, it seems like you’re looking for a dynamic link between your Excel sheet and Word document to ensure that any updates in the Excel sheet reflect immediately in the Word document.

    The solution provided in the article serves this purpose quite effectively. Using the Mail Merge feature in Word, you can connect your Excel sheet (containing the Conflict of Interest Register data) and your Word document.

    So, do not hesitate to follow the steps outlined in the article to establish the connection between the two. Once set up, any changes you make to the Excel sheet will automatically propagate to the Word document when you update the merge fields. This way, you can maintain an accurate and current Conflict of Interest Register without manual effort. Good luck.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  170. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 6, 2024 at 5:49 PM

    Hello ANUPAM

    Thanks for reaching out and sharing your queries. You not only want to count total links but also to display cells containing links.

    I am delighted to inform you that I have an Excel VBA sub-procedure that will fulfil your goal. The code will display the total links in a MsgBox and show all the cells containing links in the Immediate window.

    OUTPUT OVERVIEW:

    Excel VBA Sub-procedure:

    
    Sub CountAndListLinks()
        Dim wb As Workbook
        Dim linkCount As Integer
        Dim linkCell As Range
        
        Set wb = Application.ActiveWorkbook
        linkCount = 0
        
        If Not IsEmpty(wb.LinkSources(xlExcelLinks)) Then
            For Each linkCell In wb.Sheets(1).UsedRange.SpecialCells(xlCellTypeFormulas)
                If InStr(1, linkCell.Formula, "[") > 0 Then
                    linkCount = linkCount + 1
                    Debug.Print "Cell " & linkCell.Address & " contains a link."
                End If
            Next linkCell
            MsgBox "Total links found: " & linkCount
        Else
            MsgBox "No links found."
        End If
    End Sub
    

    Hopefully, the code will help you; good luck.

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  171. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 5, 2024 at 7:11 PM

    Hello VIKAS

    Thanks for reaching out and sharing your queries. You wanted to count the total number of named ranges in a workbook.

    I am delighted to inform you that I have developed an Excel VBA sub-procedure to display the total number of named ranges in an Excel MsgBox.

    OUTPUT OVERVIEW:

    Excel VBA Sub-procedure:

    
    Sub CountNamedRanges()
    
        Dim namedRange As Name
        Dim count As Integer
        
        count = 0
        
        For Each namedRange In ThisWorkbook.Names
            count = count + 1
        Next namedRange
        
        MsgBox "Total named ranges in this workbook: " & count
    
    End Sub
    

    The CountNamedRanges sub-procedure loops through all the named ranges in the workbook and increments a counter variable.

    Hopefully, the code will help you. Good luck.

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  172. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 5, 2024 at 6:42 PM

    Hello EXCELFLASH

    Thanks a ton for taking the time to dive into our article and sharing your thoughts! We’re thrilled to hear that you found it excellent overall – that means a lot to us.

    We’re sorry you encountered an issue with the formula from method 1. After investigating, we discovered that you are correct about the line spacing and quotation marks issues in that formula when copying.

    We’ve updated the article, so feel free to retry it. Hopefully, this time around, you won’t encounter the same hassle with the formula. Let us know if you have any further questions or suggestions.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  173. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 5, 2024 at 6:15 PM

    Hello BHARATH L

    Thanks for your nice words. Your appreciation means a lot to us. You wanted to mention a series of numbers for every copied row. In your words, if you have copied 50 columns using the code, you need to mention 1 to 50 numbers in each row.

    I am delighted to inform you that I have developed an Excel VBA sub-procedure to fulfil your requirements. I have modified the existing article code and made it an advanced version.

    OUTPUT OVERVIEW:

    Advanced Excel VBA Code:

    
    Sub RepeatMultipleRows()
        
        Dim rng As Range, crng As Range
        Dim fnum As Integer, rn As Integer
        Dim i As Integer
        On Error Resume Next
        
    SelectRange:
        xTxt = ActiveWindow.RangeSelection.Address
        Set rng = Application.InputBox("Select the repeating number", "ExcelDemy.com", xTxt, , , , , 8)
        
        If rng Is Nothing Then Exit Sub
        If rng.Columns.Count > 1 Then
            MsgBox "Select single column only!"
            GoTo SelectRange
        End If
        
        Application.ScreenUpdating = False
        
        For fnum = rng.Count To 1 Step -1
            Set crng = rng.Item(fnum)
            rn = crng.Value
            For i = 1 To rn
                crng.Offset(0, 1).Resize(1, 1).Value = i
                crng.EntireRow.Copy
                crng.Offset(1).EntireRow.Insert
            Next i
            crng.Offset(0, 1).Value = crng.Offset(0, 0).Value + 1
            crng.Offset(0, 1).Font.Bold = True
        Next
        
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
    
    End Sub
    

    Hopefully, the code will fulfil your goal. Good luck.

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  174. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 5, 2024 at 4:50 PM

    Hello TIM

    Thanks for reaching out and sharing your query. You want an Excel VBA code that works perfectly for multiple columns and rows of checkboxes. I am presenting an enhanced VBA sub-procedure that may help you.

    OUTPUT OVERVIEW:

    Enhanced Excel VBA Code:

    
    Sub AlignCheckbox()
        
        Dim xRg As Range
        Dim chkBox As OLEObject
        Dim chkFBox As CheckBox
        Dim cell As Range
        Dim cellWidth As Double
        Dim cellHeight As Double
        
        On Error Resume Next
        Application.ScreenUpdating = False
        
        For Each chkBox In ActiveSheet.OLEObjects
            If TypeName(chkBox.Object) = "CheckBox" Then
                Set xRg = chkBox.TopLeftCell
                With xRg
                    cellWidth = .Width
                    cellHeight = .Height
                    chkBox.Width = cellWidth * 2 / 3
                    chkBox.Left = .Left + (cellWidth - chkBox.Width) / 2
                    chkBox.Top = .Top + (cellHeight - chkBox.Height) / 2
                End With
            End If
        Next
        
        For Each chkFBox In ActiveSheet.CheckBoxes
            Set xRg = chkFBox.TopLeftCell
            With xRg
                cellWidth = .Width
                cellHeight = .Height
                chkFBox.Width = cellWidth * 2 / 3
                chkFBox.Height = cellHeight
                chkFBox.Left = .Left + (cellWidth - chkFBox.Width) / 2
                chkFBox.Top = .Top + (cellHeight - chkFBox.Height) / 2
            End With
        Next
        
        Application.ScreenUpdating = True
    
    End Sub
    

    Hopefully, the sub-procedure will reduce your hassle. Good luck.

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  175. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 5, 2024 at 3:02 PM

    Hello KHALED

    Thanks for your nice words. Your appreciation means a lot to us. You only want to get the city name from lat and long values.

    I am delighted to inform you that I have developed another VBA User-defined function named ExtractCityNames that will extract the city names by taking input from the User-defined function mentioned in this article.

    Follow these steps:

    1. Press Alt+F11.

    2. Click on Insert followed by Module.

    3. Paste the following code in the Module and save it.

    
    Function ExtractCityNames(inputTextFromUDF As String) As String
        
        Dim inputText As String
        Dim splitText() As String
        Dim cityName As String
        Dim i As Integer
        
        inputText = inputTextFromUDF
        
        splitText = Split(inputText, vbCrLf)
        
        For i = 0 To UBound(splitText)
            splitText(i) = Trim(splitText(i))
            Dim parts() As String
            parts = Split(splitText(i), ",")
            
            If IsNumeric(Trim(parts(UBound(parts) - 1))) Then
                cityName = Trim(parts(UBound(parts) - 2))
            Else
                cityName = Trim(parts(UBound(parts) - 1))
            End If
            
            ExtractCityNames = ExtractCityNames & cityName & vbCrLf
        Next i
    
    End Function
    
    Function ReverseGeocoder(lati As Double, longi As Double) As String
    
        On Error GoTo 0
        Dim xD As New MSXML2.DOMDocument
        Dim URL As String, vbErr As String
        xD.async = False
        URL = "https://nominatim.openstreetmap.org/reverse?lat=" + CStr(lati) + _
        "&lon=" + CStr(longi)
        xD.Load ("https://nominatim.openstreetmap.org/reverse?lat=" + CStr(lati) + _
        "&lon=" + CStr(longi))
        If xD.parseError.ErrorCode <> 0 Then
            Application.Caller.Font.ColorIndex = vbErr
            ReverseGeocoder = xD.parseError.reason
        Else
            xD.SetProperty "SelectionLanguage", "XPath"
            Dim loca As MSXML2.IXMLDOMElement
            Set loca = xD.SelectSingleNode(" / reversegeocode / result")
            If loca Is Nothing Then
                Application.Caller.Font.ColorIndex = vbErr
                ReverseGeocoder = xD.XML
            Else
                Application.Caller.Font.ColorIndex = vbOK
                ReverseGeocoder = loca.Text
            End If
        End If
        Exit Function
    0:
        Debug.Print Err.Description
    
    End Function
    

    4. Return to the sheet and select cell D3.

    5. Insert the following formula and hit Enter.

    =ExtractCityNames(ReverseGeocoder(B3,C3))

    6. Finally, copy the formula down using the Fill Handle icon.

    Things to Remember: The return time can be longer than usual as we use complex Excel VBA User-defined functions.

    I am attaching the solution workbook for better understanding. Hopefully, the idea will fulfil your goal. Stay Blessed.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  176. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 5, 2024 at 12:49 PM

    Hello Abdul

    Thanks for reaching out and posting your comment. You want to highlight the scenario where a student, Raju, fails in one subject out of five but still manages to achieve a high percentage overall due to scoring well in the remaining subjects.

    Basically, you want to ensure that Raju isn’t labelled a failure just because he fails one subject. In your view, Raju’s overall performance across all subjects should involve when determining his pass/fail status, not just the grade in one subject.

    In this case, you use the following formula: =IF(COUNTIF(C5:G5, "<33")=0, "Pass", IF(AVERAGE(C5:G5)>=60, "Pass", "Fail"))

    Here, the passing threshold for individuals is 33 and the average is 60.

    Hopefully, you have found the idea helpful. Good luck.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  177. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 4, 2024 at 6:24 PM

    Hello NIKKI

    Thanks for reaching out and sharing your queries. You want to automatically fill a cell background to yellow anytime a change is made to the data inside the cell.

    I am delighted to inform you that I have developed an Excel VBA change event that will fulfil your goal. To demonstrate, assume you want to auto-fill a cell in column A to yellow.

    To do so, open the sheet module => Insert the following code and Save.

    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        If Not Intersect(Target, Me.Range("A1:A100")) Is Nothing Then
            Target.Interior.Color = RGB(255, 255, 0) ' Yellow color
        End If
    
    End Sub
    

    Now, return to the sheet and change some cell values in column A to get an output like the following GIF.

    Hopefully, the idea will help you. Good luck.

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  178. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 4, 2024 at 5:51 PM

    Hello SCOTT

    Thanks a lot for your kind words. You want to specify a particular column from a table in Excel VBA ComboBox’s RowSource property.

    Output Overview:

    Let’s assume you have a table named Data in the sheet Database with several columns, and you want to populate your ComboBox with the values from a specific column, let’s say the Project column.
    You can use the following format for the RowSource property: =Database!Data[Project]

    Hopefully, the idea will help. I have also attached the solution workbook for better understanding. Good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  179. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 4, 2024 at 5:04 PM

    Hello ARONN

    Thanks for reaching out and sharing your requirements. You want to link a checkbox directly to another one with no cell reference.

    I am delighted to inform you that I have developed an Excel Event Procedure that will fulfil your goal.

    Output Overview:

    If you check the first box, a linked check box (second check box) will also be checked or vice versa.

    Follow these steps:

    1. Insert two checkboxes from ActiveX Control.

    2. Go to the sheet module, insert the following code, and Save.

    
    Private Sub CheckBox1_Change()
    
        If CheckBox1.Value = True Then
            CheckBox2.Value = True
        Else
            CheckBox2.Value = False
        End If
    
    End Sub
    

    Hopefully, the idea will help you. I have attached the solution workbook for better understanding. Good luck.

    SOLUTOIN WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  180. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 4, 2024 at 3:53 PM

    Hello RENE

    Thanks for reaching out and sharing your problem. You have observed that the formula works for the first eight rows. When you expand to include cells B5:B144 and C5:C144, it returns #N/A. However, our machine works perfectly fine, even though we work with an extensive range. The FILTER function is only available in Excel for Microsoft 365 and Excel 2021. Assuming you are using one of these versions.

    To resolve your problem, double-check the references in the formula to ensure they correspond to the correct columns and cells. Confirm that the formula is entered correctly without any typos or syntax errors. Good luck.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  181. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 4, 2024 at 3:32 PM

    Hello JITHU

    Thanks for reaching out. You are right about the file not allowing the opening of macros. The password is recovered using the Zip Tool, which will not let you open macros. In the article, the writer has demonstrated the file as a macro-free file when using the Zip tool. That’s why when changing back to the file type Zip to Excel, the .xlsx is used.

    If you are working on a macro-enabled file when recovering a password using Zip Tool, change the file’s extension (when changing the file type Zip to Excel) to .xlsm instead of .xlsx.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  182. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 4, 2024 at 2:58 PM

    Hello DENNIS IVAN FORD

    For the Time_Box to work properly, paste the following code in the UserForm.

    
    Private Sub Time_Box_Change()
    
        Dim cellValue As String
        
        cellValue = Selection.Value
        
        If cellValue <> "" Then
            If Time_Box.Value = True Then
                If InStr(1, cellValue, ":") > 0 Then
                    Exit Sub
                Else
                    Selection.Value = Selection.Value & ": " & Time()
                End If
        
            Else
                If InStr(1, cellValue, ":") > 0 Then
                    Selection.Value = Left(cellValue, InStr(1, cellValue, ":") - 1)
                Else
                    Exit Sub
                End If
            End If
        End If
        
    End Sub
    

    Through this code, the UserForm will be able to add time with a date when the Add Time check box is ticked.

    Download the practice workbook provided in the article for better understanding. Good luck.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  183. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 4, 2024 at 2:53 PM

    Hello DENNIS IVAN FORD

    Thanks for your comment. For the Month_Box to work properly, paste the following code in the UserForm.

    
    Private Sub Month_Box_Change()
        
        If Me.Month_Box.Value <> "" And Me.Year_Box.Value <> "" Then
            Call Create_Calender
        End If
    
    End Sub
    

    Additionally, you can remove the Option Explicit statement at the beginning when declaring initial variables.

    The article has been updated. So, I recommend you to go to the article again. Good luck.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  184. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Feb 4, 2024 at 12:49 PM

    Hello SHASHA

    Thanks for reaching out and posting your question. I would say yes, you have found the solution to your problem. You can fix this problem by changing the date format in your computer’s date and time settings. Change the date format to your desired one.

    However, I am delighted that I have an ultimate solution. I am presenting an Excel VBA sub-procedure that will display the date format based on the PC date & time setting.

    OUTPUT Overview:

    All you need to do is replace the existing Create_Calender sub-procedure with the following enhanced sub-procedure.

    
    Sub Create_Calender()
            
        For i = 1 To 42
            If Application.International(xlDateOrder) = 0 Then
                
                If i < Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value)) Then
                Controls("C" & (i)).Caption = Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
                ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "d")
                
                Controls("C" & (i)).ControlTipText = Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
                ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "m/d/yyyy")
                
                ElseIf i >= Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value)) Then
                Controls("C" & (i)).Caption = Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
                ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "d")
                
                Controls("C" & (i)).ControlTipText = Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
                ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "m/d/yyyy")
                End If
                
                If Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
                ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "mmmm") = ((Month_Box.Value)) Then
                If Controls("C" & (i)).BackColor <> &HFFFFFF Then Controls("C" & (i)).BackColor = &HFFFFFF
                Controls("C" & (i)).Font.Bold = True
                
                If Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
                ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "m/d/yyyy") = Format(This_Day, "m/d/yyyy") Then Controls("C" & (i)).SetFocus
                Else
                If Controls("C" & (i)).BackColor <> &H80000016 Then Controls("C" & (i)).BackColor = &H8000000F
                Controls("C" & (i)).Font.Bold = False
                End If
                
                
            ElseIf Application.International(xlDateOrder) = 1 Then
                
                
                If i < Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value)) Then
                Controls("C" & (i)).Caption = Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
                ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "d")
                
                Controls("C" & (i)).ControlTipText = Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
                ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "d/m/yyyy")
                
                ElseIf i >= Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value)) Then
                Controls("C" & (i)).Caption = Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
                ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "d")
                
                Controls("C" & (i)).ControlTipText = Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
                ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "d/m/yyyy")
                End If
                
                If Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
                ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "mmmm") = ((Month_Box.Value)) Then
                If Controls("C" & (i)).BackColor <> &HFFFFFF Then Controls("C" & (i)).BackColor = &HFFFFFF
                Controls("C" & (i)).Font.Bold = True
                
                If Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
                ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "d/m/yyyy") = Format(This_Day, "d/m/yy") Then Controls("C" & (i)).SetFocus
                Else
                If Controls("C" & (i)).BackColor <> &H80000016 Then Controls("C" & (i)).BackColor = &H8000000F
                Controls("C" & (i)).Font.Bold = False
                End If
                
            ElseIf Application.International(xlDateOrder) = 2 Then
                
                If i < Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value)) Then
                Controls("C" & (i)).Caption = Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
                ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "d")
                
                Controls("C" & (i)).ControlTipText = Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
                ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "yyyy/m/d")
                
                ElseIf i >= Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value)) Then
                Controls("C" & (i)).Caption = Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
                ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "d")
                
                Controls("C" & (i)).ControlTipText = Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
                ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "yyyy/m/d")
                End If
                
                If Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
                ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "mmmm") = ((Month_Box.Value)) Then
                If Controls("C" & (i)).BackColor <> &HFFFFFF Then Controls("C" & (i)).BackColor = &HFFFFFF
                Controls("C" & (i)).Font.Bold = True
                
                If Format(DateAdd("d", (i - Weekday((Month_Box.Value) & "/1/" & (Year_Box.Value))), _
                ((Month_Box.Value) & "/1/" & (Year_Box.Value))), "yyyy/m/d") = Format(This_Day, "yyyy/m/d") Then Controls("C" & (i)).SetFocus
                Else
                If Controls("C" & (i)).BackColor <> &H80000016 Then Controls("C" & (i)).BackColor = &H8000000F
                Controls("C" & (i)).Font.Bold = False
                End If
                
            End If
        Next i
    
    End Sub
    

    Hopefully, the idea will help you good luck.

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  185. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Jan 18, 2024 at 3:11 PM

    Hello Karan

    Thanks for sharing your problem. The practice workbook attached to the article should be usable in Windows 11 OS.

    As the Excel file is macro-enabled, check your Excel security settings: Go to Excel Options > Trust Center > Trust Center Settings > Macro Settings, and enable macros if they are disabled.

    Besides, ensure that the Code 128 font is correctly installed on your system.

    Hopefully, these actions will resolve your issue. Good luck.

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  186. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Jan 18, 2024 at 1:24 PM

    Hello Jon Jacobson

    Thanks for your nice words. The code and font work perfectly fine in our machine for numbers greater than 999. See the following Image,

    You can test the barcodes shown in the image using your barcode gun. On our end, we have found the original text accurately.

    It is impossible to thoroughly observe your problem remotely and not glance at your machine. Make sure you have installed the Code 128 barcode font properly. You can check the scanner manual for specific settings related to Code 128 barcodes.

    If you still face this type of problem, you can share the issue within the ExcelDemy Forum with details of your machine. Stay blessed.

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  187. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Jan 18, 2024 at 9:54 AM

    Hello TH

    Thanks for reaching out and sharing your requirements. You wanted a roaster including D1, D2, DN, N1, N2, and O to rotate full month and continue next month.

    I am delighted to inform you that I have created a roaster to fulfil your goal. So, follow these:

    1. Change the Shift Type based on your need.

    2. Modify the named range for year values.

    3. Select cell P5 and apply the following formula: =EOMONTH(I5,R4) + E5 -1

    4. Select cell F7, insert the formula: =IF(E7>=$P$5,"",E7+1) And drag the Fill Handle icon to copy the formula down.

    5. Select cell E6, insert the formula: =TEXT(F7, "ddd") And drag the Fill Handle icon to copy the formula down.

    6. Select cell E8, insert the formula: =IF(OR($C8="",E$7=""), "",IF(D8= "",C8, INDEX(Settings_Shift_Legend, IF(MATCH(D8,Settings_Shift_Legend,0)+1>COUNTA(Settings_Shift_Legend), 1,MATCH(D8,Settings_Shift_Legend,0)+1))))
    And drag the Fill Handle icon to copy the formula.

    Return to the sheet, and use the file like the following GIF.

    Hopefully, the idea will work for you. I am attaching the solution workbook for better understanding. Good luck.
    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  188. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Jan 17, 2024 at 1:42 PM

    Hello KIMHONG

    Thanks for sharing your query. In your case, an employee works 11 hours and starts working at 5:30 AM. If the overtime is between 0 and 15 minutes, you wanted a formula that returns 0 minutes. If it’s 15 minutes or more, it’s considered as 30 minutes.

    You can achieve the goal by developing a formula using the IF and TIME functions. Follow these steps:

    1. Select the intended cell.

    2. Apply the following formula: =IF((E3-TIME(5,30,0)-(11/24) > TIME(0, 15, 0)), TIME(0, 30, 0)*24, TIME(0, 0, 0))

    3. Drag the Fill Handle icon to copy the formula down.

    Hopefully, the idea will help. Good luck.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  189. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Jan 15, 2024 at 10:48 AM

    Hello NC

    Thanks for sharing your requirements. You wanted a setup not to overwrite the existing entry but to create a new entry timestamp when an item is checked in or out more than once.

    I am delighted to inform you that I have developed an Excel VBA Sub-procedure to fulfil your requirements. So, follow these steps:

    1. Press Alt+F11 to the VBA Editor window.

    2. Click on Insert followed by Module.

    3. Insert the following code in the module and Run.

    
    Sub DuplicateCheckInAndOut()
        
        Dim ws As Worksheet
        Dim lastRow As Long
        Dim searchValue As Variant
        Dim cellB As Range
        Dim foundCell As Range
        Dim loopingRange As Range
        Dim foundInMiddle As Boolean
        
        Set ws = ThisWorkbook.Sheets("Sheet1")
        
        lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
        
        searchValue = ws.Range("C4").Value
        
        If lastRow < 9 Then lastRow = 9
        
        Set loopingRange = ws.Range("B9:B" & lastRow)
        Set foundCell = loopingRange.Find(What:=searchValue, LookIn:=xlValues, LookAt:=xlWhole)
        
        If foundCell Is Nothing Then
            If lastRow = 9 Then
                ws.Cells(lastRow, "B").Value = searchValue
                ws.Cells(lastRow, "C").Value = Date & "  " & Time
                ws.Cells(lastRow, "C").NumberFormat = "m/d/yyyy h:mm AM/PM"
            Else
                ws.Cells(lastRow + 1, "B").Value = searchValue
                ws.Cells(lastRow + 1, "C").Value = Date & "  " & Time
                ws.Cells(lastRow + 1, "C").NumberFormat = "m/d/yyyy h:mm AM/PM"
            End If
            
            Exit Sub
            
        End If
        
        For Each cellB In loopingRange
            
            If cellB.Value = searchValue Then
                If IsEmpty(cellB.Offset(0, 2).Value) Then
                    cellB.Offset(0, 2).Value = Date & "  " & Time
                    cellB.Offset(0, 2).NumberFormat = "m/d/yyyy h:mm AM/PM"
                    foundInMiddle = False
                Else
                    If cellB.Row = lastRow Then
                        ws.Cells(lastRow + 1, "B").Value = searchValue
                        ws.Cells(lastRow + 1, "C").Value = Date & "  " & Time
                        ws.Cells(lastRow + 1, "C").NumberFormat = "m/d/yyyy h:mm AM/PM"
                    Else
                        foundInMiddle = True
                    End If
                End If
            End If
        
        Next cellB
        
        If foundInMiddle = True Then
            ws.Cells(lastRow + 1, "B").Value = searchValue
            ws.Cells(lastRow + 1, "C").Value = Date & "  " & Time
            ws.Cells(lastRow + 1, "C").NumberFormat = "m/d/yyyy h:mm AM/PM"
        End If
        
    End Sub
    

    After inserting the bar code and running the sub-procedure, you will see an output like the following GIF.

    Hopefully, the idea will help you to reach your goal. I have attached the solution workbook. Good luck.

    DOWNLOAD SOLUTION WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  190. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Jan 14, 2024 at 1:38 PM

    Hello KAJA POHAR

    Thanks for your compliments. Your appreciation means a lot to us. You wanted to work with a read-only Excel file; however, you had issues with the existing VBA code.

    I am delighted to inform you that I have developed an Excel VBA Sub-procedure by modifying the existing VBA code. The main idea behind the sub-procedure is it changes the File Access property to xlReadWrite, and before closing, it changes the File Access back to xlReadOnly.

    Follow these steps:

    1. Press Alt+F11 to open VBE.

    2. Click on Insert followed by Module.

    3. Paste the following code in the module and Run.

    
    Public Sub AutoRefreshFile()
    
        Dim mrf As Object
        Dim mfile As Object
        Dim mPath As String
        
        With Application.FileDialog(msoFileDialogFilePicker)
            If .Show = -1 Then
                mPath = .SelectedItems(1)
            Else
                Exit Sub
            End If
        End With
        
        Set mrf = CreateObject("Scripting.FileSystemObject")
        
        With Application
            .DisplayAlerts = False
            .ScreenUpdating = False
            .EnableEvents = False
            .AskToUpdateLinks = False
        End With
         
        If Right(mrf.GetFileName(mPath), 4) = "xlsx" Or Right(mrf.GetFileName(mPath), 3) = "xls" Then
            Dim wb As Workbook
            Set wb = Workbooks.Open(Filename:=mPath)
            
            If wb.ReadOnly = True Then
                wb.ChangeFileAccess Mode:=xlReadWrite
            End If
            
            If wb.ReadOnly Then
                wb.ChangeFileAccess Mode:=xlReadWrite
            End If
            
            wb.UpdateLink Name:=wb.LinkSources
            
            wb.ChangeFileAccess Mode:=xlReadOnly
            
            wb.Close SaveChanges:=True
        
        End If
        
        With Application
            .DisplayAlerts = True
            .ScreenUpdating = True
            .EnableEvents = True
            .AskToUpdateLinks = True
        End With
    
    End Sub
    

    As a result, you will see an output like the following GIF.

    Hopefully, the idea will help you to reach your goal. Good luck!

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  191. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Dec 28, 2023 at 9:31 AM

    Hello L. O.

    Thanks for reaching out and sharing your problem. You are facing difficulties with including a heading in a chart, and those headings affect the graph’s appearance.

    As the headings are treated as zeros, you can exclude them from the data by combining the IF, ISNUMBER and NA functions. The formula structure can be like the following:

    =IF(ISNUMBER(A2), A2, NA())

    This formula checks if the value in cell A2 is a number. If it is, it includes the value. Otherwise, it returns #N/A. The #N/A values will be ignored in the chart, and the line won’t connect to them.

    Instead of a scatter plot, you might consider using a line chart or other chart types that suit your needs better. Line charts, for example, automatically ignore #N/A values and don’t connect them.

    Hopefully, the ideas will help you to overcome your situation. Good luck!

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  192. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Dec 27, 2023 at 6:57 PM

    Hello NUNOF

    Thanks for your beautiful words. Your appreciation means a lot to us. We are delighted to know you have learned a lot from our blog.

    The issue you describe hampers performance for sure in such scenarios. I have gone through the article and also investigated your problem. What I have found: Perhaps you are calling your desired macro within another worksheet event like Worksheet_Calculate instead of the Worksheet_Change event of cell $K$10, which results in calling the macro unintentionally.

    When the cell values change by formulas, the Worksheet_Change event will not trigger. So, call your macro or sub-procedure within a change event. In your case, the code within the sheet module can be like the following:

    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        If Not Intersect(Target, Range("K10")) Is Nothing Then
            
            Call GreetingToNUNOF
        
        End If
    
    End Sub
    
    
    Sub GreetingToNUNOF()
        MsgBox "Hello from ExcelDemy!"
    End Sub
    

    Hopefully, you have found the idea to overcome your situation. Good luck!

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  193. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Dec 26, 2023 at 6:30 PM

    Hello RAY

    Thanks for reaching out and sharing your query.

    Unfortunately, the Paste Special feature is unavailable in Excel Online compared to the desktop version.

    There is a list of missing features that you will also not find in Excel Web:

    1. Create or Edit Named Ranges

    2. No Macro Support

    3. No Cell, Workbook, or Sheet Protection

    4. No Checkboxes

    5. No Solver or Other Add-ins

    Hopefully, the answer and list will help you regarding your queries. Good luck!

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  194. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Dec 26, 2023 at 5:00 PM

    Hello RAMNATH TAKIAR

    Thanks a Ton! for your nice words. Your appreciation means a lot to us. Thank you once again for sharing your expertise with our ExcelDemy blog!

    The extra values you have added prove effective. We appreciate you sharing your program with us, and we believe it will be valuable to others who visit our blog.

    To generate even 500 random samples from the selected population of 200:
    OUTPUT OVERVIEW:

    Excel VBA Sub-procedure (Contributed by RAMNATH TAKIAR):

    
    Sub Random_Sample()
        
        Dim xRow As Long
        Dim J As Long
        
        For J = 5 To 55
            Dim I As Long
            For I = 1 To 20
                xRow = [RandBetween(1,200)]
                Cells(I, J) = Cells(xRow + 1, 1)
            Next I
        Next J
    
    End Sub
    

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  195. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Dec 26, 2023 at 11:07 AM

    Hello ALEX

    Thanks for your nice words. Your appreciation means a lot to us.

    You are facing a difficulty like when you press the enter button on cell D5 with the formula, the barcode changes. It’s different from the original one and can’t be scanned. Providing the ultimate solution without properly glancing at your problem is very hard. However, I am giving you some ideas that might be helpful.

    Suggestions: Your issue concerns the font encoding or handling of the Code 128 font in Excel. Make sure that the Code 128 font is installed correctly on your system. Check the scanner manual for any specific settings related to Code 128 barcodes. Format the cell as Text when using the Code 128 barcode font with a custom VBA function to ensure accurate content and avoid unintended changes by Excel formatting rules.

    You can also share your problem with more details in the ExcelDemy Forum. Good luck.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  196. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Dec 26, 2023 at 9:57 AM

    Hello GSFRAGARO

    Thanks for sharing your problem. You have encountered an issue like you have a string with two consecutive zeros, but the code generates an empty character. In Our machine, the code and font work perfectly fine if we have a string with two consecutive zeros. See the following Image,

    The possible reason for the issue you encountered could be not using the Code 128 font. Maybe you are using some other barcode font. So, please ensure you are using the intended font. Good luck.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  197. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Dec 24, 2023 at 8:20 PM

    Hello NURIT

    Thanks for sharing another exciting problem. The issue is raised because the UDF (User Defined Function) is not re-calculating.

    I am excited to let you know that I have resolved the issue by developing an Excel VBA Sub-procedure named ForcedReCalculation (responsible for application re-calculation).

    OUTPUT OVERVIEW:

    Follow these steps: Open the VBA Editor window => Right-click on the sheet module => Click on View Code => Replace the previous code with the following code => Save.

    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Dim rng As Range
        Dim cell As Range
    
        Set rng = Me.Range("S8:AO43")
    
        If Not Intersect(Target, rng) Is Nothing Then
    
            For Each cell In Intersect(Target, rng)
    
                If cell.Value = "VAC 8" Then
                    cell.Interior.colorIndex = 43
                ElseIf cell.Value = "FMLA 47" Then
                    cell.Interior.colorIndex = 47
                ElseIf cell.Value = "SL 6" Then
                    cell.Interior.colorIndex = 6
                ElseIf cell.Value = "FLT 33" Then
                    cell.Interior.colorIndex = 33
                ElseIf cell.Value = "WB 44" Then
                    cell.Interior.colorIndex = 44
                ElseIf cell.Value = "P-VAC 7" Then
                    cell.Interior.colorIndex = 7
                ElseIf cell.Value = "SVC 8" Then
                    cell.Interior.colorIndex = 8
                Else
                    cell.Interior.colorIndex = xlColorIndexNone
                End If
    
            Next cell
        End If
        
        Call ForcedReCalculation
        
    End Sub
    
    Sub ForcedReCalculation()
        Application.CalculateFull
    End Sub
    

    Hopefully, you have found your solution. I look forward to seeing you at the ExcelDemy Forum with another exciting problem. Stay blessed.

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  198. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Dec 24, 2023 at 6:11 PM

    Hello JOHN

    Thanks for sharing your query. You wanted to split strings within Excel cells that do not always have 3 words separated by commas. In contrast, there can be a string like one or at most three words like the following image. Besides, we must keep the cell empty if more than 3 words mean more than 2 commas exist.

    I am delighted to inform you that I have developed two formulas. The first formula consists of the IF, LEN, SUBSTITUTE, TRIM, and MID functions. The other formula consists of the IF, LEN, SUBSTITUTE, TRANSPOSE, and FILTERXML functions.
    OUTPUT OVERVIEW:

    1. Using IF, LEN, SUBSTITUTE, TRIM, and MID functions
    Follow these steps:
    Step 1: Select cell C5, insert the given formula and drag the Fill Handle icon to E5.

    =IF(LEN($B5)-LEN(SUBSTITUTE($B5,”,”,””))>2,””,TRIM(MID(SUBSTITUTE($B5,”,”,REPT(” “,LEN($B5))),(C$4-1)*LEN($B5)+1,LEN($B5))))

    Step 2: Select range C5:E5 and drag the Fill Handle icon to cell E10.

    2. Using FILTERXML, TRANSPOSE, IF, LEN, and SUBSTITUTE functions

    I am attaching the solution workbook for better understanding. I hope these ideas will help you to reach your goal. Good luck.

    DOWNLOAD WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  199. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Dec 14, 2023 at 1:34 PM

    Dear NURIT

    It is good to see you again. Thanks for thanking me. Your appreciation means a lot to us.

    You are right. When a cell background color is changed by conditional formatting, the cell color index remains xlColorIndexNone. When using conditional formatting to change the cell background based on a condition, it fetches the intended color, but the cell background is not changed ultimately.

    SOLUTION to the Mentioned Problem: Remove the conditional formatting from the S8:AO43 range. Use an Excel VBA Event procedure that I developed to solve your problem. This event will ultimately change the background color based on the condition.

    Follow these steps: Right-click on the sheet name tab => Click on View Code => Paste the code below in the sheet module, and Save => Return to the sheet and make changes to see the output like the following GIF.

    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Dim rng As Range
        Dim cell As Range
    
        Set rng = Me.Range("S8:AO43")
    
        If Not Intersect(Target, rng) Is Nothing Then
    
            For Each cell In Intersect(Target, rng)
    
                If cell.Value = "VAC 8" Then
                    cell.Interior.colorIndex = 43
                ElseIf cell.Value = "FMLA 47" Then
                    cell.Interior.colorIndex = 47
                ElseIf cell.Value = "SL 6" Then
                    cell.Interior.colorIndex = 6
                ElseIf cell.Value = "FLT 33" Then
                    cell.Interior.colorIndex = 33
                ElseIf cell.Value = "WB 44" Then
                    cell.Interior.colorIndex = 44
                ElseIf cell.Value = "P-VAC 7" Then
                    cell.Interior.colorIndex = 7
                ElseIf cell.Value = "SVC 8" Then
                    cell.Interior.colorIndex = 8
                Else
                    cell.Interior.colorIndex = xlColorIndexNone
                End If
    
            Next cell
        End If
    End Sub
    

    Enhanced User-defined Function: I am delighted to share an Enhanced version of the previous sub-procedure.
    Follow these: Hover over Insert => Click on Module => Paste the following code in the module => Save.

    
    Function SumColoredCells(rng As Range, colorIndex As Long) As Double
    
        Dim cell As Range
        Dim sumValue As Double
    
        sumValue = 0
    
        For Each cell In rng
            If cell.Interior.colorIndex = colorIndex Then
    
                Dim numericPart As Double
                
                If Len(cell.Value) = 4 Then
                    numericPart = Val(Mid(cell.Value, 3))
                ElseIf Len(cell.Value) = 5 Then
                    numericPart = Val(Mid(cell.Value, 4))
                ElseIf Len(cell.Value) = 6 Then
                    numericPart = Val(Mid(cell.Value, 5))
                ElseIf Len(cell.Value) = 7 Then
                    numericPart = Val(Mid(cell.Value, 6))
                ElseIf Len(cell.Value) = 8 Then
                    numericPart = Val(Mid(cell.Value, 7))
                End If
    
                sumValue = sumValue + numericPart
            End If
        Next cell
    
        SumColoredCells = sumValue
    
    End Function
    

    Finally, to calculate the sum, Select cell Q8 => Insert the following formula => Drag the Fill Handle icon to cell Q14.

    =SumColoredCells($S$8:$AO$43,P8)

    Besides, I am presenting an sub-procedure that will display the color index of a selected cell.

    
    Sub ShowColorIndex()
    
        Dim selectedCell As Range
        Dim colorIndex As Long
        
        If TypeName(Selection) = "Range" And Selection.Cells.Count = 1 Then
    
            Set selectedCell = Selection
            
            colorIndex = selectedCell.Interior.colorIndex
            
            MsgBox "Color Index of Selected Cell: " & colorIndex
        
        Else
            MsgBox "Please select a single cell to get its color index.", vbExclamation
        End If
    
    End Sub
    

    A Friendly Suggestion: Dear Nurit, we are discussing a topic different from the article. So, another visitor may get confused. It would be great if you shared your problem through the ExcelDemy Forum.

    I am also attaching the solution workbook for better understanding. Hopefully, this idea will help you reach your goal. Good luck.

    DOWNLOAD WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  200. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Dec 14, 2023 at 12:41 PM

    Hello ZAPPER

    Thanks for reading ExcelDemy Blogs. You wanted a button to click and copy some sheets in a folder.

    I am delighted to inform you that I have enhanced the sub-procedure so you will like it. The sub-procedure will create a folder dynamically, and the path will be the workbook path. Besides, it will handle errors as well. I have inserted a print icon and assigned the sub-procedure as follows.

    OUTPUT OVERVIEW:

    Enhanced Excel VBA Sub-procedure:

    
    Sub AdvancedSaveSheetByArrayVariable()
    
        Dim currentPath As String
        currentPath = ThisWorkbook.Path
        
        Dim folderPath As String
        folderPath = currentPath & "\" & "SavedSheetsFolder"
        
        On Error GoTo ErrHandle
        
        MkDir folderPath
        
        Sheets(Array("dataset1", "dataset2", "dataset3")).Select
        Sheets("dataset1").Activate
        Sheets(Array("dataset1", "dataset2", "dataset3")).Copy
    
        With ActiveWorkbook
            .SaveAs Filename:=folderPath & "\" & "SaveSheetsByVBA_2.xlsx", CreateBackup:=False
            .Close False
        End With
        
        Exit Sub
    
    ErrHandle:
    
        MsgBox "Folder and File Exist. Remove the previous folder and file.", vbCritical
    
    End Sub
    

    I am attaching the solution workbook for better understanding. Good luck.

    DOWNLOAD WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  201. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Dec 13, 2023 at 7:12 PM

    Hello MR MARTYN WILLIETS

    Thanks for reaching out and sharing your requirements. You wanted to create a personal payment tracker sheet. To create such a sheet, we can work with fields like Date, Start Time, End Time, Total Paid Hours, Overtime Hours, Overtime Pay, Normal Hours Pay, and Total Pay. Assuming you get $76.5 for overtime hours and $51 for regular hours. I am delighted to share how you can create a sheet to fulfil your goal.

    Overview of Personal Payment Tracker Sheet:

    Follow these steps:

    Step 1: Total Paid Hours
    Select cell E3 => Insert the following formula => Drag the Fill Handle icon to cell E10.

    =TEXT((D3-C3-TIME(0,30,0)),”h:mm”)

    Step 2: Overtime Hours
    Select cell F3 => Insert the formula below => Drag the Fill Handle icon to cell F10.

    =IF(OR(C3TIME(18,0,0)), TEXT((D3-C3-TIME(10,30,0)),”h:mm”), 0)

    Step 3: Overtime Pay
    Choose cell G3 => Insert the formula below => Drag the Fill Handle icon to cell G10.

    =IFERROR(TIMEVALUE(F3)*(76.5/0.041667),0)

    Step 4: Normal Hours Pay
    Choose cell H3 => Insert the formula below => Drag the Fill Handle icon to cell H10.

    =IFERROR(TIMEVALUE(E3)*(51/0.041667),0)

    Step 5: Total Pay
    Choose cell I3 => Insert the formula below => Drag the Fill Handle icon to cell I10.

    =G3+H3

    I am also attaching the solution workbook for better understanding. Hopefully, the idea will help you to reach your goal. Good luck.

    DOWNLOAD WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  202. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Dec 13, 2023 at 2:55 PM

    Hello ASIF MAHMOOD

    Thanks for reaching out and sharing your requirements with such clarity. You wanted to know how to convert a string like 159.3 B into a regular unit format (for example, 159300000000.00).

    I am delighted to inform you that I have developed an Excel VBA Event Procedure that will trigger when a value is inserted in a format like 13 B in column B and convert the values into a regular unit. Besides, I have developed an Excel formula to fulfil your goal using the TEXT and SUBSTITUTE functions.

    OUTPUT OVERVIEW (Excel VBA Event Procedure):

    Follow these steps:

    Step 1: Right-click on the sheet name tab => Click on View Code.

    Step 2: Hover over Insert => Click on Module => Paste the following code in the module => Save.

    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Dim ws As Worksheet
    
        Set ws = ThisWorkbook.Sheets("Sheet2")
    
        Set IntersectRange = Intersect(Target, ws.Columns("B"))
    
        If Not Intersect(Target, ws.Columns("B")) Is Nothing Then
            
            Application.EnableEvents = False
            
            On Error Resume Next
        
            If Not IsEmpty(Target.Value) Then
                Target.Value = Application.WorksheetFunction.Text(Application.WorksheetFunction.Substitute(Target.Value, " B", "") * 1000000000, "0.00")
                Target.NumberFormat = "0.00"
            End If
    
            Application.EnableEvents = True
        
        End If
    
    End Sub
    

    OUTPUT OVERVIEW (Using Excel TEXT and SUBSTITUTE Functions):

    Follow these steps:

    Step 1: Select cell C5 => Insert the following formula => Hit Enter.

    =TEXT(SUBSTITUTE(B5, ” B”, “”) * 1000000000, “0.00”)

    Step 2: Drag the Fill Handle icon to cell C14 to copy the formula down.

    I am also attaching the solution workbook for better understanding. Good luck.

    DOWNLOAD WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  203. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Dec 11, 2023 at 4:52 PM

    Hello GARY PHILLIPS

    Thanks for reaching out and sharing your queries. You want to know how to use the Monthly Log presented in the article. Copy the drop-down of symbols and paste it in all the day cells. Choose the intended symbols using drop-down for all the day sections of the month. If a task is complete, mark it as Complete in the left section above the calendar.

    You can easily use the Monthly Log like the following GIF.

    Hopefully, the idea will help you. Good luck!

    Regards
    Lutfor Rahman Shimanto

  204. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Dec 11, 2023 at 2:10 PM

    Hello DAN H

    Thanks for your nice words. Your appreciation means a lot to us. Thanks once again for sharing your requirements with such clarity.

    You wanted to get an Excel VBA Sub-procedures or Excel VBA User-defined functions to calculate the distance between two addresses using Address String (for example, New York, Alaska) instead of Latitudes and Longitudes values.

    I am delighted that I developed some Excel VBA User-defined functions to fulfil your requirements.

    OUTPUT OVERVIEW:

    Follow these steps:

    Step 1: Hover over Insert => Click on Module => paste the following code in the module.

    
    Public Function DistanceInKM(First_Location As String, Final_Location As String, Target_Value As String)
    
        Dim Initial_Point As String, Ending_Point As String, Distance_Unit As String, Setup_HTTP As Object, Output_Url As String
        Initial_Point = "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="
        Ending_Point = "&destinations="
        Distance_Unit = "&travelMode=driving&o=xml&key=" & Target_Value & "&distanceUnit=km"
        
        Set Setup_HTTP = CreateObject("MSXML2.ServerXMLHTTP")
        
        Output_Url = Initial_Point & First_Location & Ending_Point & Final_Location & Distance_Unit
        Setup_HTTP.Open "GET", Output_Url, False
        Setup_HTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
        Setup_HTTP.send ("")
        DistanceInKM = Round(Round(WorksheetFunction.FilterXML(Setup_HTTP.responseText, "//TravelDistance"), 3), 0)
    End Function
    
    Public Function DistanceInMiles(First_Location As String, Final_Location As String, Target_Value As String)
    
        Dim Initial_Point As String, Ending_Point As String, Distance_Unit As String, Setup_HTTP As Object, Output_Url As String
        Initial_Point = "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="
        Ending_Point = "&destinations="
        Distance_Unit = "&travelMode=driving&o=xml&key=" & Target_Value & "&distanceUnit=mi"
        
        Set Setup_HTTP = CreateObject("MSXML2.ServerXMLHTTP")
        
        Output_Url = Initial_Point & First_Location & Ending_Point & Final_Location & Distance_Unit
        
        Setup_HTTP.Open "GET", Output_Url, False
        Setup_HTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
        Setup_HTTP.send ("")
        DistanceInMiles = Round(Round(WorksheetFunction.FilterXML(Setup_HTTP.responseText, "//TravelDistance"), 3), 0)
    End Function
    
    Function Co_Ordinates(address As String) As String
    
        Application.Caller.Font.ColorIndex = xlNone
        Dim xDoc As New MSXML2.DOMDocument
        xDoc.async = False
        xDoc.Load ("https://nominatim.openstreetmap.org/search?format=xml&q=" + WorksheetFunction.EncodeURL(address))
        If xDoc.parseError.ErrorCode <> 0 Then
            Application.Caller.Font.ColorIndex = vbErr
            Co_Ordinates = xDoc.parseError.reason
        Else
            xDoc.SetProperty "SelectionLanguage", "XPath"
            Dim loc As MSXML2.IXMLDOMElement
            Set loc = xDoc.SelectSingleNode("/searchresults/place")
            If loc Is Nothing Then
                Application.Caller.Font.ColorIndex = vbErr
                NominatimGeocode = xDoc.XML
            Else
                Application.Caller.Font.ColorIndex = vbOK
                Co_Ordinates = loc.getAttribute("lat") & "," & loc.getAttribute("lon")
            End If
        End If
    
    End Function
    

    Step 2: Select cell C9 => Insert the following formula.

    =DistanceInMiles(Co_Ordinates(C6),Co_Ordinates(C7),C2)

    Step 3: Hit Enter to see the result in cell C9.

    Step 4: Select cell C10 => Insert the following formula.

    =DistanceInKM(Co_Ordinates(C6),Co_Ordinates(C7),C2)

    Step 5: Hit Enter to see the result in cell C10.

    Things to Keep in Mind: Ensure to check Microsoft XML, v3.0 from the References – VBAProject window.

    I am attaching the solution workbook. Hopefully, these user-defined functions will help you reach your goal. Good luck.

    DOWNLOAD WORKBOOK

    Regards
    Lutfor Rahman Shimanto
    Excel & VBA Developer
    ExcelDemy

  205. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Dec 11, 2023 at 11:47 AM

    Hello BRIAN

    Your appreciation means a lot us. Thanks for reaching out and posting a well-layout question. You encounter an issue with hidden columns and buttons that toggle between different sheet views. And you want to delete these buttons and the hidden column features. It isn’t easy to give an exact solution without the exact details of the worksheet views. However, I have some general ideas to help you with your problem.

    First, identify the toggle buttons and go to sheet views, followed by view options. Later, delete that sheet view. Additionally, you remove the applied filters and groupings. Select the columns surrounding the hidden ones, right-click, and choose Unhide to ensure all columns are visible.

    If you still have difficulties, you can share your problem in the ExcelDemy Forum with more detailed information. Good luck!

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  206. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Dec 10, 2023 at 9:36 PM

    Hello NURIT

    Thanks for your nice words. Your appreciation means a lot to us. You wanted a VBA code that will ignore the text within a cell and sum only the number portion on that cell of the S8:AO43 range based on specific conditionally formatted colors.

    I am delighted to inform you that I have developed an Excel VBA User-defined function to fulfil your goal.

    OUTPUT OVERVIEW:

    Excel VBA User-defined Function:

    
    Function SumColoredCells(rng As Range, colorIndex As Long) As Double
    
        Dim cell As Range
        Dim sumValue As Double
    
        sumValue = 0
    
        For Each cell In rng
            If cell.Interior.colorIndex = colorIndex Then
    
                Dim numericPart As Double
                numericPart = Val(Mid(cell.Value, 4))
    
                sumValue = sumValue + numericPart
            End If
        Next cell
    
        SumColoredCells = sumValue
    
    End Function
    

    Follow these steps:
    Step 1: Open the VBA Editor window => Hover over Insert and click on Module => Insert the code above in the module and Save.

    Step 2: Select cell P20 => Insert the following formula => Hit Enter.

    =SumColoredCells(S8:AO43,43)

    Hopefully, the idea will help you. Good luck.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  207. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Dec 10, 2023 at 7:10 PM

    Hello BERNARD

    Thanks for reaching out and posting your observation. The ZIP Tool method mentioned in the article can be applicable If you have a protected sheet within a workbook.

    I have experienced the same error when the workbook is encrypted with a Password. But, if only a sheet is protected, the ZIP Tool works perfectly. When you preserve your workbook with an Encrypted Password, the ZIP Tool method will not work. So, use the VBA code to remove the password from the encrypted workbook. Good luck.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  208. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Dec 7, 2023 at 11:44 AM

    Dear AVA

    Your appreciation means a lot to us. Thanks for describing your problem with such clarity. You want a column of comments and a column of texts in which a comment was made (e.g. Josef Albers | Artist).

    I am delighted to let you know that I modified the previous code to fulfil your requirements.

    Excel VBA Sub-procedure:

    
    Sub AdvancedExtractCommentsAndTextToExcel()
    
        Dim xAPP As Object
        Dim xWB As Workbook
        Dim xDoc As Object
        Dim j As Integer
        Dim filePath As String
        
        filePath = "C:\Users\User\Downloads\AVA-DEMO-DOC.docx"
        
        Set xAPP = CreateObject("Word.Application")
        xAPP.Visible = True
        
        Set xDoc = xAPP.Documents.Open(filePath)
        
        Set xWB = Application.ThisWorkbook
        
        With xWB.Worksheets(1)
    
            For j = 1 To xDoc.Comments.Count
                '.Cells(j, 1).Formula = xDoc.Comments(j).Initial
                .Cells(j, 2).Formula = xDoc.Comments(j).Range.Text
                .Cells(j, 3).Formula = xDoc.Comments(j).Scope.Text
                '.Cells(j, 4).Formula = Format(xDoc.Comments(j).Date, "dd/MM/yyyy")
            Next j
        End With
        
        xDoc.Close False
        
        Set xWB = Nothing
        Set xDoc = Nothing
        Set xAPP = Nothing
    
    End Sub
    

    OUTPUT OVERVIEW:

    I hope the code presented will make your task easier. Good luck!

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  209. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Dec 6, 2023 at 5:26 PM

    Hello AVA

    Thanks for reaching out and posting an exciting comment. You wanted to extract comments besides the links within the comment from Word to Excel.

    I am delighted to inform you that the requirement you describe can be met with the help of Excel VBA. I am presenting a sub-procedure that will fulfil your goal.

    Excel VBA Sub-procedure:

    
    Sub AdvancedExtractCommentsAndTextWithLinksToExcel()
    
        Dim xAPP As Object
        Dim xWB As Workbook
        Dim xDoc As Object
        Dim j As Integer
        Dim filePath As String
        
        filePath = "C:\Users\User\Downloads\AVA (DEMO DOC).docx"
        
        Set xAPP = CreateObject("Word.Application")
        xAPP.Visible = True
        
        Set xDoc = xAPP.Documents.Open(filePath)
        
        Set xWB = Application.ThisWorkbook
        
        With xWB.Worksheets(1)
    
            For j = 1 To xDoc.Comments.Count
                .Cells(j, 1).Formula = xDoc.Comments(j).Initial
                .Cells(j, 2).Formula = xDoc.Comments(j).Range.Text
                .Cells(j, 3).Formula = Format(xDoc.Comments(j).Date, "dd/MM/yyyy")
                
                If xDoc.Comments(j).Range.Hyperlinks.Count > 0 Then
                    .Cells(j, 4).Formula = xDoc.Comments(j).Range.Hyperlinks(1).Address
                End If
            Next j
        End With
        
        xDoc.Close False
        
        Set xWB = Nothing
        Set xDoc = Nothing
        Set xAPP = Nothing
    
    End Sub
    

    OUTPUT OVERVIEW:

    To learn more you can read the following articles.

    1. How to Convert Word to Excel with Columns

    2. How to Convert Word to Excel but Keep Formatting

    3. How to Import Data from Word to Excel

    4. How to Convert Word Table to Excel Spreadsheet

    You can download the Solution Workbook and Word Document for better understanding.

    Download Excel Workbook
    Download Word Document

    I hope the given code and resources will help. Stay blessed.

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  210. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Dec 6, 2023 at 3:50 PM

    Hello GURU

    Thanks for sharing your queries. Your Excel Data is like “01/01/23 14.20 PM” (24-Hour Clock Time does not need AM/PM extensions). You want to store “01/01/23” and “14.20 PM” in another column.

    I am presenting another sub-procedure that will fulfil your requirements by modifying the sub-procedure mentioned in the article.

    Excel VBA Sub-procedure:

    
    Sub AdvancedSplitDateAndTime()
    
        Dim ws As Worksheet
        Dim lastRow As Long
        Dim i As Long
        Dim dateWithTime As Date
        Dim separatedDate As Date
        Dim separatedTime As Date
    
        Set ws = ThisWorkbook.Sheets("Sheet1")
        
        ws.Range("C:C").NumberFormat = "[$-en-US]h:mm AM/PM;@"
        
        lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
        For i = 1 To lastRow
    
            dateWithTime = ws.Cells(i, 1).Value
    
            separatedDate = DateSerial(Year(dateWithTime), Month(dateWithTime), Day(dateWithTime))
            separatedTime = TimeValue(Format(dateWithTime, "h:mm AM/PM"))
    
            ws.Cells(i, 3).Value = separatedTime
    
            ws.Cells(i, 2).Value = separatedDate
    
        Next i
        
    End Sub
    

    OUTPUT OVERVIEW:

    You have one more doubt when using Text to Column features. Typically, the Text to Column features will display dates in the desired format you mention. You can apply a custom format like “m/d/yyyy” after that if it does not.

    Hopefully, the suggestions and the presented code will help you. Good luck!

    Regards
    Lutfor Rahman Shimanto

  211. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Dec 5, 2023 at 6:05 PM

    Hello KURT

    Thank you for reading our article and providing helpful input. We sincerely appreciate your time and effort in reviewing the content and pointing out the calculation error.

    We have already taken corrective action and have updated the article accordingly. Thank you one more. Best wishes!

    Regards
    Lutfor Rahman Shimanto
    ExcelDemy

  212. Reply Lutfor Rahman Shimanto
    Lutfor Rahman Shimanto Dec 5, 2023 at 4:18 PM

    Hello LUIS CARRERA

    Thanks for sharing your problem. The error you are facing is like the following:

    Run-time error ‘1004’
    Application-defined or object-defined error

    On the line below:

    Set RG = RG.Offset(1, 0)

    The error arises from setting the range offset outside the worksheet’s boundaries. The range RG starts at cell H5, and when you try to set the offset to (1, 0), it might go beyond the last row of the worksheet.

    However, I am presenting an Excel VBA code to handle that error.

    Excel VBA Code:

    
    Sub AdvancedCombinationsFor6Columns()
        
        On Error GoTo ErrorHandler
        
        Dim X1, X2, X3, X4, X5, X6 As Range
        Dim RG As Range
        Dim xStr As String
        Dim FN1, FN2, FN3, FN4, FN5, FN6 As Integer
        Dim SV1, SV2, SV3, SV4, SV5, SV6 As String
        
        Set X1 = Range("B5:B19")