Junaed-Ar-Rahman

About author

Md Junaed-Ar-Rahman, a Biomedical Engineering graduate from Bangladesh University of Engineering and Technology, has contributed to the ExcelDemy project for one year. As a technical content developer, he has authored 15+ unique articles and actively addressed user problems. He participated in 2 specialized training programs on VBA and Chart & Dashboard design in Excel. His passion lies in solving problems uniquely and exploring new functions and formulas. Eager for future exploration, he aims to gain proficiency in applications beyond Excel.

Designation

Technical Content Developer at ExcelDemy in SOFTEKO.

Lives in

Dhaka, Bangladesh.

Education

B.sc in Biomedical Engineering, Bangladesh University of Engineering and Technology

Expertise

MATLAB, Solidworks, Microsoft Office, Microsoft Excel.

Experience

  • Technical Content Writing
  • Biomedical Engineer
    • Troubleshooting of medical device (ECG, Echo, USG)
    • Installation and dismantle of X-ray and CT Scan machines
    • Collaboration of users and service provider
  • Undergraduate Projects
    • Development of Cost-effective CPAP machine for New born babies
    • Developing setup for Heat Conductivity measurement of biological tissues

Latest Posts From Junaed-Ar-Rahman

0
Excel VBA: Work with Variable in MsgBox

In this article, we will discuss how to work with the VBA MsgBox variable. We will explain the different types of variables available in VBA and use them in ...

0
Excel VBA: Running a Backwards For Loop

In this article, we will discuss how to use the For Loop statement in Excel VBA to loop through a range of cells backwards. We will explain the syntax of the ...

0
Excel VBA Select Case Between Two Values (6 Examples)

In this article, we will discuss the use of Excel VBA Select Case to pick between two values. We will explain the syntax of the Select Case and use it for ...

0
Excel VBA Offset Column (5 Suitable Cases)

Often we need to move the active cell to the next cell without explicitly specifying its row and column coordinates. The Offset function provides a very ...

0
How to Make a Roster in Excel (with Detailed Steps)

A roster usually indicates a schedule that divides the turn of duties (or even leave) in an organization for an individual or a group. From attendance tracking ...

0
How to Create Clustered Scatter Plot in Excel (with Easy Steps)

A clustered scatter plot is a type of chart in Excel that displays data points as individual dots on a graph. Unlike a regular scatter plot where all data ...

0
Excel MATCH Function Match Type (Greater/Less Than & Exact Match)

The MATCH function in Excel is a very useful function to find out exact or approximate data matches in Excel. However, you may have trouble choosing the match ...

0
How to Calculate Sales Conversion Rate Using Formula in Excel

In this article, we will demonstrate how to calculate the sales conversion rate formula in Excel. We may need to calculate various types of calculations for ...

0
How to Create Excel Checkbook Register with Reconciliation

In this article, we will demonstrate how to create an Excel checkbook register with Reconciliation. Bankers typically employ reconciliation in their daily ...

0
How to Reduce Excel File Size by Deleting Blank Rows

In this article, we will demonstrate how to reduce Excel file size by deleting blank rows. Often a file in Excel becomes very large and takes a huge space in ...

0
How to Use Cost Benefit Analysis Calculator in Excel

In this article, we will demonstrate how to use the cost-benefit analysis calculator in Excel. Cost-benefit analysis is a very important term in the business ...

0
How to Use ISBLANK Function in Excel for Multiple Cells

ISBLANK is the function that we use to check whether a cell in Excel is empty or has value. We may use this for a cell or in a range of cells. In this ...

0
How to Use ISNA and MATCH Functions in Excel (2 Examples)

We often have to compare two rows or two columns in an Excel workbook. But if we do it without applying direct formulas, it becomes very tiresome. Excel helps ...

0
How to Convert Currencies in Excel (7 Methods)

Currency conversion in Excel means converting the value of one currency into the equivalent value of another currency using Excel functions and features. In ...

0
Goal Seek in Excel (Examples, Advantages, and Disadvantages)

Goal Seek in Excel means using the Goal Seek tool of Excel to calculate the specific input value that will produce a desired result in a formula. In this ...

Browsing All Comments By: Junaed-Ar-Rahman
  1. Thanks JASON for your comment.

    The issue you are encountering is the result of two potential situations. The first scenario is that you have implemented an additional conditional formatting within your data range. The second scenario is that you have not adjusted the formula to align with your data.
    However, you can modify the formula for conditional formatting and replace it with the following one.
    =IF(ISBLANK(Search_box),"",SEARCH(Search_box,$B5&$C5&$D5))

    Remove all the conditional formatting except the dedicated one given in the method. Also, check whether there are any VBA codes running in your worksheet affecting the changes.

    Regards
    Md Junaed Ar Rahman

  2. Thank you DENNIS for your comment.
    The code is automatic since whenever you run it, you don’t need to check which deadlines are 1 to 7 days away from the current date. Also, the code creates a draft automatically. However, if you want to automate the whole process, you will have to modify the code slightly and create a task scheduler. Copy the following code and paste it into the VBA module:

    
    Private Sub Workbook_Open()
        ' Call the SendReminderMailAutomatically subroutine when the workbook is opened
        SendReminderMailAutomatically
    End Sub
    
    Public Sub SendReminderMailAutomatically()
        Public Sub SendReminderMailAutomatically()
        ' Declare the variables
        Dim ws As Worksheet
        Dim XDueDate As Range
        Dim XRcptsEmail As Range
        Dim xMailContent As Range
        Dim xCrtOut As Object
        Dim k As Long
        Dim xMailSections As Object
        Dim xFinalRw As Long
        Dim CrVbLf As String
        Dim xMsg As String
        Dim xSubEmail As String
        On Error Resume Next
        
        ' Set the worksheet
        Set ws = ThisWorkbook.Sheets("YourSheetName") ' Replace "YourSheetName" with the actual sheet name
        
        ' Define the ranges from the worksheet
        Set XDueDate = ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row) ' Assuming due dates are in column A starting from row 2
        Set XRcptsEmail = ws.Range("B2:B" & ws.Cells(ws.Rows.Count, "B").End(xlUp).Row) ' Assuming email addresses are in column B starting from row 2
        Set xMailContent = ws.Range("C2:C" & ws.Cells(ws.Rows.Count, "C").End(xlUp).Row) ' Assuming email content is in column C starting from row 2
        
        ' Count rows for the due dates
        xFinalRw = XDueDate.Rows.Count
        
        ' Set command to open MS Outlook Application
        Set xCrtOut = CreateObject("Outlook.Application")
        
        ' Apply For loop to conduct the operation in each row one by one
        For k = 1 To xFinalRw
            ' Apply If condition for the Due Date values
            If XDueDate.Cells(k).Value  "" Then
                ' Condition set to send mail if the difference between due dates and current date is greater than 1 and less than 7 days
                ' Means 1 < X < 7, X = Due Date - Current Date
                If CDate(XDueDate.Cells(k).Value) - Date  0 Then
                    ' Create the subject, body, and text contents with the required variables
                    xValSendRng = XRcptsEmail.Cells(k).Value
                    xSubEmail = xMailContent.Cells(k).Value & " on " & XDueDate.Cells(k).Value
                    CrVbLf = vbCrLf
                    xMsg = "Dear " & xValSendRng & CrVbLf
                    xMsg = xMsg & "Text : " & xMailContent.Cells(k).Value & CrVbLf
                    
                    ' Create the email
                    Set xMailSections = xCrtOut.CreateItem(0)
                    
                    ' Define the position to place the Subject, Body, and Recipients Address
                    With xMailSections
                        .Subject = xSubEmail
                        .To = xValSendRng
                        .Body = xMsg
                        .Send
                    End With
                    
                    Set xMailSections = Nothing
                End If
            End If
        Next
        
        Set xCrtOut = Nothing
    End Sub
        MsgBox "Reminder emails have been sent.", vbInformation
    End Sub
    

    Create a task-scheduler:
    Now, follow the steps below to create a task scheduler:
    1. Type “Task Scheduler” in the Windows search bar and press “Enter”.
    2. In the right-hand Actions pane, click on “Create Basic Task”. Set a name and description for your task.
    3. Choose Daily in the “Trigger” option and Start a Program in the “Action” part.
    4. In the Program/Script box, give the directory of “excel.exe” file.
    5. In the “Add arguments” field, specify the full path to your Excel file.
    6. Click “Finish.”

    Hopefully, following the steps above, you will be able to perform your desired task.

    Regards
    Md Junaed Ar Rahman

  3. Hello Michiel,

    Thanks for your question. If you want to add new products to the dataset and include them in your calculation, you will have to convert the range into a table. Then you will be able to add as many new products as you required by inserting new rows in the dataset.

    On the other hand, if the products in the dataset are discontinued, you don’t need to modify any cells that contain the “Quantity” or “Revenue” values since we are not doing any arithmetic operation on them. For safety reasons, you can apply the IFERROR()function in all the cells where you are applying formula. For example, you would change the formula in Column G into IFERROR(E5/C5). The exception is the cells where we will calculate the sum of different values. For those cells, we will modify the formula of M11 into SUM(IF(ISNUMBER(M5:M10), M5:M10)).

    Hopefully, you have got the desired answer.

  4. Hello Terry Smith,

    Thanks for your question. Most probably you are using a function like TODAY() function to find the first date in our worksheet. Then you have applied the Fill Handle feature. That’s why, the days are updating every day. The solution to this problem is to manually input the first date and then apply the Fill Handle feature.

    Also, if your problem is different from the problem that I have assumed, then give us the Excel files. It will be convenient for us to solve the issue.

  5. Reply MD. JUNAED-AR-RAHMAN
    Junaed-Ar-Rahman Mar 9, 2023 at 1:00 PM

    Dear HARIRI,

    When you use the following command in your code, the prompt will not appear.
    Application.DisplayAlerts = False
    However, if you want to open a new file in the same name in the same folder, there may be two scenarios. You might be able to do that without any obstacles because you have a file with .xlsm extension only with that name and no .xlsx file with that name. But if you have two files with same name in same folder, one with .xlsm extension and another with .xlsx extension, then you cannot open a new Excel file in that folder. Prompt will appear to warn you that there is a folder with the same name and you will have to discard that action or have to modify the file name.

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo