Md. Abu Sina Ibne Albaruni

About author

Md. Abu Sina Ibne Albaruni holds a BSc in Mechanical Engineering from Bangladesh University of Engineering and Technology. He is a dedicated employee of the ExcelDemy project. He has written quite a large number of articles for ExcelDemy. Besides, he has expertise in VBA. He efficiently automates Excel issues using VBA macros and actively engages in the ExcelDemy forum, offering valuable solutions for user interface challenges. His areas of interest in work and study span MATLAB, Machine Learning, Image Processing, Solidworks, LaTex, Python, Microsoft Office, and creating Excel applications.

Designation

Excel & VBA Content Developer at ExcelDemy in SOFTEKO.

Lives in

Dhaka, Bangladesh.

Education

B.Sc. in Mechanical Engineering, BUET.

Expertise

Content Writing, MATLAB, Machine Learning, Image Processing, Solidworks, LaTex, Python, Microsoft Office.

Experience

  • Technical Content Writing
  • Undergraduate Projects
    • Implementation of Machine Learning for Detection of Rice Blast Disease Using Digital Image Processing
    • Automatic Potato Cutter Machine

Research

  • A Model for SARS-COV-2 VirusTransmission on The Open Deck of A Passenger Ship Bound for A Short Trip [under review] (2021 - 2022)

Latest Posts From Md. Abu Sina Ibne Albaruni

0
Tracker in Excel (Task Tracker, Progress Tracker and so on)

A tracker in Excel can be used for many purposes including project management, inventory management, event planning, data organization, employee tracking, and ...

0
How to make Excel Auto Calculate (6 Easy Methods)

  The dataset, Sales of Products, has four columns: Product Name, Unit Price, Quantity, and Total Price. To calculate the total price for ...

0
Excel AutoFill (16 Useful Examples)

Here's an overview of the AutoFill feature when working with days of the week. Download the Practice Workbook Excel AutoFill.xlsm How ...

0
Serial Number in Excel (With Practical Examples)

In this article, you will learn about creating the serial number in Excel. We will show you how to create a serial number in Excel using built-in features and ...

1
How to Make Periodic Table in Excel: 2 Easy Ways

Method 1 - Manual Periodic Table 1.1. Insert Data in Properties Table Each element of a periodic table has a unique set of properties. These properties refer ...

0
Excel VBA Global Constant (2 Convenient Ways)

In this article, we have explored how to declare Excel VBA global constant. We have demonstrated 2 convenient ways to declare global constants. Then, we ...

1
How to Perform Data Analysis and Visualization with Excel (A Complete Guideline)

What is Data Analysis and Visualization? Data analysis involves collecting, organizing, and analyzing raw data to extract relevant information. This process ...

0
How to Use the CONCAT Function in Excel – 7 Examples

The CONCAT function can be used to concatenate different values and ranges of cells. It can handle dynamic data from different sources. Download ...

0
Excel Convert Timestamp to Time (4 Easy Ways)

Download Practice Workbook Converting Timestamp to Time.xlsm Method 1 - Convert Timestamp to Time Using Combination of TIME and MID Functions ...

0
Excel VBA to Filter Pivot Table Based on List

In this article, we will use Excel VBA to filter Pivot Table Based on a list. The list can be an array, range, or a dynamic named range. Imagine a scenario ...

0
Use Excel VBA to Create Chart from Pivot Table

Are you tired of spending countless hours manually creating charts from your Pivot Tables in Excel? Well, afraid no more! We can use Excel VBA to create chart ...

0
How to Use Excel SUMIF Function Based on Cell Color

Excel SUMIF function calculates the sum of a range of cells based on specified criteria. It allows the users to specify a range of cells to be evaluated ...

0
How to Filter ComboBox Data in Excel VBA

A ComboBox allows users to select from a list of predefined items. However if the list is too big, It may become difficult to find a required item. We can ...

0
How to Use Excel VBA Index Match with Array (4 Methods)

In this guide, we’ll explore four methods for utilizing INDEX and MATCH functions with an array in Excel VBA. To illustrate these techniques, we’ll work with a ...

0
How to Use VBA in Excel to Capitalize All Letters (4 Examples)

It is very common to use VBA in Excel to capitalize all letters in a range of cells. Capitalizing letters is a very straightforward process in Excel VBA. In ...

Browsing All Comments By: Md. Abu Sina Ibne Albaruni
  1. Reply Avatar photo
    Md. Abu Sina Ibne Albaruni Mar 9, 2023 at 1:33 PM

    Hi,

    Thank you for your comment. From the problem you have stated, it looks like you need to just modify the previous code a little bit. You can use the following code to accomplish your task.

    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    'Autopupulate Cells with date depending on particular cell value
    
        Dim Cell As Range
        For Each Cell In Target
            If Cell.Column = Range("D:D").Column Then
                If Cell.Value = "Start" Then
                    Cells(Cell.Row, "G").Value = Now
                ElseIf Cell.Value = "In-Progress" Then
                    Cells(Cell.Row, "H").Value = Now
                ElseIf Cell.Value = "Pending" Then
                    Cells(Cell.Row, "I").Value = Now
                ElseIf Cell.Value = "Completed" Then
                    Cells(Cell.Row, "J").Value = Now
                End If
                
    ' You can add additional cases if you want
    ' Just follow the above method and change the cell reference
    
            ElseIf Cell.Column = Range("G:J").Column Then
             
                If Cells(Cell.Row, "D").Value = "" Then
                
                    If Cell.Column = Range("G:G").Column Then
                        Cells(Cell.Row, "G").Value = ""
                    ElseIf Cell.Column = Range("H:H").Column Then
                        Cells(Cell.Row, "H").Value = ""
                    ElseIf Cell.Column = Range("I:I").Column Then
                        Cells(Cell.Row, "I").Value = ""
                    ElseIf Cell.Column = Range("J:J").Column Then
                        Cells(Cell.Row, "J").Value = ""
                    End If
                    
                End If
                
            End If
            
        Next Cell
          
    ' You may want to clear columns (G:J), if cell value is changed
    
    End Sub
      

    Regards,
    Md. Abu Sina Ibne Albaruni
    Team ExcelDemy

  2. Reply Avatar photo
    Md. Abu Sina Ibne Albaruni Jul 9, 2023 at 3:47 PM

    Dear VSP

    Thank you very much for your question. I appreciate your interest in our article. I will show you the step-by-step process to create a macro according to your preferences. Also, you will be able to send automated emails on the due date without running the code.

    ● First, create the workbook. I am creating a sample workbook that has Message, Date, Status of work, and Email addresses in specific positions that you have mentioned.

    Create Dataset
    ● Select your entire dataset.
    ● Go to the Insert tab >> Table.
    Check the My table has headers option.
    ● Press OK.

    Create Table
    ● Sort your data according to your Email address.

    Sort Email Addresses
    ● Now go to the Developer tab >> Visual Basic.

    Open Visual Basic
    ● Go to ThisWorkbook and paste the following code into the VBA Macro Editor.
    Save the file as Excel Macro-Enabled Workbook (.xlsm) file.

    VBA Code

    
    Private Sub Workbook_Open()
    
        'variable declaration
        Dim myOutlookApp As Object
        Dim myOutlookMail As Object
        Dim i As Long
        Dim Receiver As String
        Dim EmailBody As String
        Dim WS As Worksheet
        
        'create outlook application object
        Set myOutlookApp = CreateObject("Outlook.Application")
        'set worksheet
        Set WS = ActiveSheet
        
        'loop through rows
        'starting from the second row assuming row 1 is the Header
        For i = 2 To WS.Range("C" & Rows.Count).End(xlUp).Row
            'check if the task is assigned today
            If WS.Range("C" & i) = Date Then
            'check status
            If WS.Range("E" & i) = "In progress" Or WS.Range("E" & i) = "Outstanding" Then
                'check if the email address is same as the previous one
                If WS.Range("G" & i) = Receiver Then
                    'merge email bodies for same email addresses
                    EmailBody = EmailBody & vbCrLf & WS.Range("A" & i)
                Else
                    'if the email address is different, send the previous email
                    'create a new email
                    If Receiver <> "" Then
                        'create a new mail item
                        Set myOutlookMail = myOutlookApp.CreateItem(0)
                        
                        'compose the email
                        With myOutlookMail
                            'set the receiver email address
                            .To = Receiver
                            'set the subject of the email
                            .Subject = "Task Summary"
                            'set the body of the email
                            .body = EmailBody
                            'send email
                            .Send
                        End With
                        
                        'release the mail object
                        Set myOutlookMail = Nothing
                    End If
                    
                    'set the receiver and body for the new email
                    Receiver = WS.Range("G" & i)
                    EmailBody = WS.Range("A" & i)
                End If
            End If
            End If
        Next i
        
        'send the last email (if exists)
        If Receiver <> "" Then
            Set myOutlookMail = myOutlookApp.CreateItem(0)
            
            With myOutlookMail
                .To = Receiver
                .Subject = "Task Summary"
                .body = EmailBody
                .Send
            End With
    
            Set myOutlookMail = Nothing
        End If
        
        'release the Outlook application object
        Set myOutlookApp = Nothing
        
        'display a confirmation message
        MsgBox "The emails have been sent successfully from your outlook"
    
    End Sub
    

    ● Now, to automatically send emails, type Run in your Start Menu and open the Run dialog box.
    ● Paste the following path there and press OK.

    %AppData%\Microsoft\Windows\Start Menu\Programs\Startup

    Run Dialog Box
    ● Place your Excel file in the Startup folder.

    Put Excel File in Startup Folder
    Now whenever you open your PC, the VBA code of your Excel file will be automatically executed. Emails will be sent to the given email addresses according to the criteria you mentioned.

    Notes:
    ● Stay logged in to your outlook application. We are using the OUTLOOK Microsoft 365 Office application.
    ● The provided code sends an email for the current date. You can customize it for any date.
    ● Make sure that your active worksheet contains the sorted table and save the file accordingly.

    You can download the sample Excel file from here.
    Send Emails.xlsm

    If you have any more questions, please let us know in the comment sections.

    Regards,
    Md. Abu Sina Ibne Albaruni
    Team ExcelDemy

  3. Reply Avatar photo
    Md. Abu Sina Ibne Albaruni Jul 2, 2023 at 12:54 PM

    Dear SHERI

    Thank you very much for your inquiry. I appreciate your interest in our article. You can copy worksheets from one workbook to another without mentioning the names of the worksheets. You have to slightly change the VBA code. I have modified the code and provided it here for your convenience.

    Keep two things in mind before running the code:
    You must keep both workbooks open.
    You must run the code in the destination workbook.

    Paste this code in the VBA Macro Editor of your destination workbook and press the Run button or F5 key to run the code.

    
    Sub Copy_Worksheets()
        
        'variable declaration
        Dim Source As Workbook
        Dim Destination As Workbook
        
        'set source file
        Set Source = Workbooks("Source.xlsx")
        
        'set destination file
        Set Destination = Workbooks("Destination.xlsm")
        
        'copy worksheets from source file to destination file
        Dim i As Long
        For i = 1 To Source.Sheets.Count
            Source.Sheets(i).Copy After:=Destination.Sheets(Destination.Sheets.Count)
        Next i
        
    End Sub
    

    If you have any more questions, please let us know in the comment section.

    Regards
    Md. Abu Sina Ibne Albaruni
    Team ExcelDemy

  4. Reply Avatar photo
    Md. Abu Sina Ibne Albaruni Jun 15, 2023 at 11:25 AM

    Dear NIRMAL

    Thank you for taking the time to read this article. I can see that you are facing a problem while executing the code in method 3. I guess It is Run-time error ‘1004’.

    Run-time error 1004

    Kindly note that you need to modify the file path according to the folder where you are storing your pictures. You also have to specify the type of the image (jpg, jpeg, png, etc.) correctly. So, you should change the LocationPic variable in the code accordingly.

    For your convenience, I am attaching a photo indicating the exact portions of the code that you need to re-write.

    Modify Code

    If you have any more questions, please let us know in the comments.

    Regards
    Md. Abu Sina Ibne Albaruni
    Team ExcelDemy

  5. Reply Avatar photo
    Md. Abu Sina Ibne Albaruni Jun 12, 2023 at 4:42 PM

    Dear BENJAMIN THORPE

    Thank you very much for your interest in our article. You can create an auto-prompt dropdown in Excel. In the dataset, we have the names of multiple people.

    Dataset for Auto-Prompt Dropdown

    Simply follow the steps below:

    ● Go to the Developer tab >> Insert >> Combo Box (ActiveX Control).
    ● Then place the newly created ComboBox.

    Create ActiveX Control ComboBox

    ● Double-click on the ComboBox.
    ● A VBA window will pop up. Paste the following code into the window.

    VBA Code to Create Dropdown

    
    Private Sub ComboBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
        
        'variable declaration
        Dim myRng As Range
        Dim myArr() As Variant
        Dim i As Long
        
        'user input
        Set myRng = Application.InputBox("Insert the Data Range for the Drop-down List", Type:=8)
        
        myArr = myRng.Value
        
        'sort selected data
        For i = LBound(myArr, 1) To UBound(myArr, 1) - 1
            For j = i + 1 To UBound(myArr, 1)
                If myArr(i, 1) > myArr(j, 1) Then
                    Dim temp As Variant
                    temp = myArr(i, 1)
                    myArr(i, 1) = myArr(j, 1)
                    myArr(j, 1) = temp
                End If
            Next j
        Next i
        
        'clear Drop-down list
        ComboBox1.Clear
        
        'add item to the drop-down list
        For i = LBound(myArr, 1) To UBound(myArr, 1)
            ComboBox1.AddItem myArr(i, 1)
        Next i
    
    End Sub
    
    

    ● Now, go back to the worksheet and click on the Design Mode from the Developer tab to turn it off.

    Turn off Design Mode

    ● Double-click on the ComboBox and you will see an InputBox asking for the list.
    ● Select the list from your dataset and press OK.

    Input List

    ● You will have the auto-prompt drop-down list.

    Auto-Prompt Dropdown

    You can also download the Excel file from here.
    Excel Auto Prompt Drop-Down.xlsm

    If you have any more queries, please let us know in the comments.

    Regards
    Md. Abu Sina Ibne Albaruni
    Team ExcelDemy

  6. Reply Avatar photo
    Md. Abu Sina Ibne Albaruni May 24, 2023 at 11:02 AM

    Dear KAZEM

    Thank you for taking the time to read this article. I see that you are facing a problem while executing the code. It is the Run-time error ‘1004’. There are several possible reasons for this error. In your case, I believe you are having this issue because the file path you specified is incorrect or unavailable. Please note that you need to modify the file path according to the destination where you want to save the PDF file. You have to change the file path portion in the Filename variable.

    For your convenience, I am attaching a photo indicating the exact portion of the code that you need to re-write.

    If you have any more questions, please let us know in the comments.

    Regards
    Md. Abu Sina Ibne Albaruni
    Team ExcelDemy

    VBA Code to Convert Excel Range to PDF

  7. Reply Avatar photo
    Md. Abu Sina Ibne Albaruni May 3, 2023 at 10:29 AM

    Hi SUMBO,

    We have sent you an email. Please see the attached document in it.

    Regards
    Md. Abu Sina Ibne Albaruni
    Team ExcelDemy

  8. Reply Avatar photo
    Md. Abu Sina Ibne Albaruni Apr 17, 2023 at 12:45 PM

    Hello Z L

    Thank you very much for your response. We have modified this article and introduced some new statistical concepts and functions. Would you please go through it again?

    If you have any more questions, let us know in the comment section.

    Thanks

    Md. Abu Sina Ibne Albaruni
    Team ExcelDemy

  9. Reply Avatar photo
    Md. Abu Sina Ibne Albaruni Apr 4, 2023 at 11:23 AM

    Hi YOGAAGA

    Thank you very much for your comment. A few limitations while working with these functions have been discussed in the NOTES section. Please go through them. In addition, here in the dataset, the VLOOKUP function has been used in combination with the LARGE function. Both the VLOOPUP and LARGE functions go through the entire range of data every time they are called. The LARGE function needs to sort the data before determining the result. The VLOOKUP function looks for the data in a specified range. The whole process can be computationally expensive in large datasets. So, this formula can be time-consuming when dealing with large amounts of data.

    Moreover, this formula may return identical values if it encounters duplicates in the dataset. For example, in our dataset, if there are multiple students with the same CGPA, the formula will return the same student’s name for each duplicate value.

    Thank you once again. Let me know in the comments if you have any further queries.

    Regards,
    Md. Abu Sina Ibne Albaruni
    Team ExcelDemy

  10. Reply Avatar photo
    Md. Abu Sina Ibne Albaruni Mar 16, 2023 at 11:14 AM

    Dear Datla Srinivas,

    We have sent you an email. Please find the attached documents in it.

    Best wishes
    Md. Abu Sina Ibne Albaruni
    Team ExcelDemy

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo