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)

In this article, we have discussed different examples of tracker in Excel. You will see the step-by-step process of creating a task tracker. You will get to ...

0
Excel Auto Calculate (6 Easy Ways)

In this article, we have talked about how to make Excel to auto calculate formulas. We have discussed 6 easy methods to do so. We have also explored ways to ...

0
Excel Characters: A Complete Guide

In this article, you will learn about Excel characters. We will show you how to create a list of Excel characters. We will apply formulas and cell formatting ...

0
Excel Area Chart (2-D & 3-D Formats)

In this article, we have shown you the step-by-step process to create an Excel Area Chart. We have discussed the Stacked Area Chart and 100% Stacked Area ...

0
Outliers in Excel: A Complete Guide

In this article, we will show you different methods to calculate outliers in Excel. You will get to know about various statistical concepts and Excel formulas ...

0
Excel AutoFill (16 Useful Examples)

In this article, we have discussed in detail how to use the Excel AutoFill feature. We have demonstrated 16 different examples to show the use of Excel ...

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 ...

0
How to Rotate Text in Excel

In this article, we have discussed 5 different ways in Excel to rotate text. We have also shown you how to rotate text at any custom angle. Then, we explored ...

1
How to Make Periodic Table in Excel (2 Easy Ways)

In this article, we have explored 2 easy methods of how to make periodic table in Excel. The first one is manual. It involves various functions and features of ...

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
Data Analysis and Visualization with Excel (A Complete Guideline)

In this article, we will show you how to perform data analysis and visualization with Excel. There are three stages in data analysis and visualization. To ...

0
How to Use CONCAT Function in Excel

In this article, we have talked about how to use the CONCAT function in Excel. We have demonstrated various examples of using the CONCAT function with texts, ...

0
Excel Convert Timestamp to Time (4 Easy Ways)

The timestamp is widely used to record and analyze time-based information, such as financial transactions, online meetings, and event logs. However, dealing ...

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 ...

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