Bishawajit Chakraborty

About author

Bishawajit Chakraborty, a Rajshahi University of Engineering & Technology graduate with a B.Sc. in Mechanical Engineering, has been associated with ExcelDemy since 2022. Presently, he is a content developer, specializing in Excel Power Query, Data Analysis and VBA. It is worth mentioning that he has authored more than 90 articles on VBA content development. His profound interest lies in the fields of data analytics and data science. He possesses expertise in VBA, Power BI, machine learning, and Python automated dashboards.

Designation

Content Developer at ExcelDemy in SOFTEKO.

Lives in

Dhaka, Bangladesh.

Education

B.Sc. in Mechanical Engineering, Rajshahi University of Engineering and Technology

Expertise

Data Analysis, Content Writing, C, Python, SolidWorks, AutoCAD, and 3D Modeling.

Experience

  • Technical Content Writer, SOFTEKO
  • Published 90+ tutorials on Advanced Excel and VBA Macro

Publication

Latest Posts From Bishawajit Chakraborty

0
Change Table Style in Excel (Change, Create & Remove)

Excel Table Styles & Formats is a powerful feature in Excel that allows you to enhance the presentation and organization of datasets in a worksheet by ...

0
Excel Doughnut Chart

Excel's Doughnut Chart is a fantastic feature. It is quite useful for visualizing any statistical difference. You will learn how to make a doughnut chart with ...

0
Box and Whisker Plot in Excel (Create & Customize)

The box and whisker plot in Excel shows the distribution of quartiles, medians, and outliers in the assigned dataset. This article will demonstrate how to ...

0
How to Calculate Payment in Excel?

In this article, you will learn how to -Apply direct formula to calculate monthly payment  -Use PMT function to calculate monthly payment  -Use PMT ...

0
Excel Accounting Number Format (Apply and Customize)

Using the Accounting Number Format in Excel is easy and can be done in a variety of ways. You will learn to apply accounting number format in Excel by using ...

0
VBA to Get Array Dimensions in Excel (4 Examples)

You must give the appropriate index for the desired element in order to access a specific element of an array. Based on the dimensions of the element, the ...

0
[Fixed!] Excel Application-Defined or Object-Defined Error in VBA

A runtime error in VBA is the 1004 error. It is also known as an application-defined or object-defined error in VBA. How come? since Excel only has a certain ...

0
Activate Another Workbook with VBA in Excel (7 Examples)

You need to know the technique that can assist you in activating a workbook that you wish to work on while you are working with numerous workbooks at once, by ...

0
Excel VBA to Lookup Value in Range (5 Examples)

The lookup function aids in retrieving data from the primary table using a single lookup value. That sort of data structure is not necessary for the VBA LOOKUP ...

0
How to Redim 2D Array with VBA in Excel (3 Examples)

An object that stores the same types of data is called an array. A one-dimensional array is one that contains just one row or one column of material. A 2D ...

0
How to Filter Email Addresses in Excel (5 Easy Ways)

Email addresses are an important way of communicating in today's digital world. Excel, a popular spreadsheet program, can be an effective tool for managing and ...

0
Excel VBA For Loop with Array (5 Examples)

In Excel VBA, we use the For Loop to iterate through an array (For Next Loop and For Each Loop). Also, in order to use both as the loop's counter, you must be ...

0
Excel VBA Vlookup with Multiple Criteria (3 Examples)

The VLookup function is typically used to find a value in the table's leftmost column and will return a value from the specified column in the same row. With ...

0
How to Use IF Statement with Yes or No in Excel (3 Examples)

Using this function, it is possible to analyze text, values, and even errors. It extends beyond simply determining whether two things are equal and then giving ...

1
How to Create a Bar Chart with Standard Deviation in Excel 

If all the data were flawless, absolute, and comprehensive, that would be good. Excel, however, provides us with some helpful tools to communicate margins of ...

Browsing All Comments By: Bishawajit Chakraborty
  1. Reply Bishawajit Chakraborty
    Bishawajit Chakraborty Nov 16, 2022 at 11:28 AM

    Thank you so much Sharath for your response. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.

    Regards,

    Bishawajit Chakraborty.

  2. Reply Bishawajit Chakraborty
    Bishawajit Chakraborty Jan 16, 2024 at 11:43 AM

    Hello Laura,

    To save the file with the inserted images and send it via email, you can follow these steps:

    Save the Workbook:

    1. Click the Save button or press Ctrl+S to save the workbook. 
    2. Click NO.
    3. Select the file type as “Excel Macro-Enabled Workbook (*.xlsm)”.
    4. Click on the Save option.

    Attach the Workbook to Email:

    • Open your email client (such as Outlook, Gmail, etc.).
    • Compose a new email.
    • Look for an option like “Attach” or “Attach File” in your email client.
    • Navigate to the location where you saved your workbook and select it.

    Send the Email:

    • Complete the email by adding the recipient’s email address, subject, and any additional message you want to include.
    • Finally, click the “Send” button to send the email.By following these steps, you will send the Excel workbook with the inserted images via email. The recipient will be able to open the workbook and view the images as long as they enable macros when prompted (since the workbook contains VBA code).

    If you encounter any issues or have further questions, feel free to ask!

  3. Reply Bishawajit Chakraborty
    Bishawajit Chakraborty Oct 29, 2023 at 12:44 PM

    Thank you, Peter Abbott, for your wonderful question.

    Here is the solution to your question:

    This is the VBA code we have changed for multiple data columns and multiple timestamp columns.

    First, input data for multiple columns, like below.

    Now, paste the following in the module. Then, click on the Run button to see the output.

    Sub InsertTimestamps()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Integer
    'Set the worksheet where you want to insert timestamps
    Set ws = ThisWorkbook.Sheets("Sheet1")
    'Set the range where you want to insert timestamps (A5 to C7)
    Dim startRow As Long
    Dim endRow As Long
    startRow = 5
    endRow = 7
    'Loop through the columns and insert timestamps
    For i = 1 To 3 'Columns A, B, and C
    ws.Range(ws.Cells(startRow, i), ws.Cells(endRow, i)).Offset(0, 3).Value = Now
    ws.Range(ws.Cells(startRow, i), ws.Cells(endRow, i)).Offset(0, 3).NumberFormat = "yyyy-mm-dd hh:mm:ss"
    Next i
    End Sub

    Finally, see the following output:

    I hope this may solve your issue.

    Bishawajit, on behalf of ExcelDemy

  4. Reply Bishawajit Chakraborty
    Bishawajit Chakraborty Sep 18, 2023 at 4:05 PM

    Thank you, DAWOOD, for your wonderful question.

    In Excel, you can use a barcode font to create barcodes that include hyphens (“-“). Here are the steps to do it:

    1. Download and Install a Barcode Font:

    • First, you’ll need to download and install a barcode font that supports hyphens. You can find various barcode fonts online, and some popular ones include Code 128, Code 39, or EAN-13. Download the font and install it on your computer following the font installation instructions for your operating system.

    2. Enter Your Barcode Data:

    • In an Excel cell, enter your barcode data, including the hyphens.
    • Select all the codes in the cell.
    • From the Font group in the Home tab, choose Code 128 font format.
    • Press Enter.

    • Now, you will see the following result.

    I hope this may solve your issue. Also, please follow this article to get your answer with detailed explanations.

    That’s it! You’ve created a barcode in Excel with hyphens included in the data. Remember that the actual appearance and functionality of the barcode depend on the specific barcode font you’re using, so make sure to choose a font that supports the format you need.

    Bishawajit, on behalf of ExcelDemy

  5. Reply Bishawajit Chakraborty
    Bishawajit Chakraborty Sep 7, 2023 at 5:08 PM

    Thank you, ELEANOR TINDALL, for your wonderful question.

    To apply multiple selections from a data validation list to specific columns (A and K) in an Excel sheet while restricting the rest of the columns to single-option entry, you can use VBA code. Here’s a modified version of the code that applies multiple selections only to columns A and K:

    Sub Apply_MultipleSelections_For_Columns_A_and_K()
        Dim ValidationRange As Range
        Dim Cell As Range
        Dim Oldvalue As String
        Dim Newvalue As String
        Dim Target As Range
        On Error Resume Next
        Set Target = ActiveCell
        On Error GoTo 0
        Set ValidationRange = ActiveSheet.Range("A:A,K:K")
        If Not Intersect(Target, ValidationRange) Is Nothing Then
            Application.EnableEvents = False
            Newvalue = Target.Value
            Application.Undo
            Oldvalue = Target.Value
            If Oldvalue = "" Then
                Target.Value = Newvalue
            ElseIf InStr(1, Oldvalue, Newvalue) = 0 Then
                Target.Value = Oldvalue & ", " & Newvalue
            Else
                Target.Value = Oldvalue
            End If
        End If
        Application.EnableEvents = True
    End Sub
    

    I hope this may solve your issue. 

    Bishawajit, on behalf of ExcelDemy

  6. Reply Bishawajit Chakraborty
    Bishawajit Chakraborty Jun 15, 2023 at 12:10 PM

    Thank you, STEVEN BRITTON, for your wonderful question.

    Here is the explanation to your question.

    It is true that the “Microsoft Date and Time Picker Control 6.0 (SP6)” is no longer available in Windows 11 and more recent versions of Microsoft Office. This control is no longer supported by Microsoft and is no longer included in the default ActiveX control set.

    The most recent editions of Excel, including Windows 11, allow you to enter a date picker using a different method. You can now choose dates in Excel by using the built-in Microsoft “Calendar Control” date picker.

    This article will help you how to add date and time picker control. Check this below link.

    How to Use Excel UserForm as Date Picker

    I hope this may solve your issue. 

    Bishawajit, on behalf of ExcelDemy

  7. Reply Bishawajit Chakraborty
    Bishawajit Chakraborty Jun 15, 2023 at 11:59 AM

    Thank you, KC, for your wonderful question.

    Here is the explanation to your question.

    It is true that the “Microsoft Date and Time Picker Control 6.0 (SP6)” is no longer available in Windows 11 and more recent versions of Microsoft Office. This control is no longer supported by Microsoft and is no longer included in the default ActiveX control set.

    The most recent editions of Excel, including Windows 11, allow you to enter a date picker using a different method. You can now choose dates in Excel by using the built-in Microsoft “Calendar Control” date picker.

    This article will help you how to add date and time picker control. Check this below link.

    How to Use Excel UserForm as Date Picker

    I hope this may solve your issue. 

    Bishawajit, on behalf of ExcelDemy

  8. Reply Bishawajit Chakraborty
    Bishawajit Chakraborty Jun 15, 2023 at 11:58 AM

    Thank you, KC, for your wonderful question.

    Here is the explanation to your question.

    It is true that the “Microsoft Date and Time Picker Control 6.0 (SP6)” is no longer available in Windows 11 and more recent versions of Microsoft Office. This control is no longer supported by Microsoft and is no longer included in the default ActiveX control set.

    The most recent editions of Excel, including Windows 11, allow you to enter a date picker using a different method. You can now choose dates in Excel by using the built-in Microsoft “Calendar Control” date picker.

    This article will help you how to add date and time picker control. Check this below link.

    How to Use Excel UserForm as Date Picker

    I hope this may solve your issue. 

    Bishawajit, on behalf of ExcelDemy

  9. Reply Bishawajit Chakraborty
    Bishawajit Chakraborty May 21, 2023 at 3:31 PM

    Thank you, KLAUDIUS, for your wonderful question.

    Here is the solution to your question. Please take a look at the below steps.

    • Here is our sample data set and we will see the notifications of upcoming birthdays notifications.

    • Now, paste the following code into the module and run the VBA macro to see the result.
    Sub Birthdate_Remainder()
    Dim LastRow As Long
    Dim i As Long
    Dim J As Long
    Dim xDate As Date
    LastRow = Range("D" & Rows.Count).End(xlUp).Row
    J = 0
    For i = 2 To LastRow
    xDate = DateSerial(Year(Date), Month(Cells(i, 4).Value), Day(Cells(i, 4).Value))
    If xDate >= Date And xDate < Date + 8 Then
    J = 1
    MsgBox Format(Cells(i, 4).Value, "dd, mmmm") & " is " & Cells(i, 1).Value & _
    "'s birthday" & vbCrLf & vbCrLf & "His phone number is " & Cells(i, 2).Value & vbCrLf & _
    "And his email is " & Cells(i, 3).Value
    End If
    Next
    If J = 0 Then MsgBox "No Birthday today, tomorrow and the next 7 days"
    End Sub

    • Finally, you can see the following result.

    I hope this may solve your issue. 

    Bishawajit, on behalf of ExcelDemy.

  10. Reply Bishawajit Chakraborty
    Bishawajit Chakraborty May 3, 2023 at 10:39 AM

    Thank you, FAJAR, for your wonderful question.

    Here is the explanation to your question.

    It is still possible for someone to obtain the cell value from another workbook without opening it using Excel VBA even if the source file is password- or other security-protected.

    However, depending on the situation, accessing a protected file in this manner might be regarded as unethical or unlawful. It’s crucial to respect the security precautions taken by the file’s owner and to only access the data through legitimate, approved methods. Additionally, it is important to keep in mind that Excel offers a variety of protection options, and the efficacy of the protection depends on the particular technique used. While some security measures can be easily bypassed with a basic understanding of VBA, others are more robust and call for sophisticated techniques.

    Because of this, it’s crucial to carefully examine the degree of protection needed for your unique use case and implement the proper security measures in accordance.

     

    I hope this may solve your issue. 

    Bishawajit, on behalf of ExcelDemy

  11. Reply Bishawajit Chakraborty
    Bishawajit Chakraborty Apr 26, 2023 at 1:41 PM

    Thank you, RAFAL, for your wonderful question.

    Here is the solution to your question.

    This is the VBA code we have applied for multiple columns starting from row 2.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim CellCol, CellCol1, TimeCol, Row, Col As Integer
    Dim DpRng, Rng As Range
    CellCol1 = 1
    CellCol = 2
    TimeCol = 3
    Row = Target.Row
    Col = Target.Column
    If Row <= 1 Then Exit Sub
    Timestamp = Format(Now, "DD-MM-YYYY HH:MM:SS AM/PM")
    If Target.Text <> "" Then
    If Col = CellCol Then
    Cells(Row, TimeCol) = Timestamp
    ElseIf Col = CellCol1 Then
    Cells(Row, TimeCol) = Timestamp
    Else
    On Error Resume Next
    Set DpRng = Target.Dependents
    For Each Rng In DpRng
    If Rng.Column = CellCol Then
    Cells(Rng.Row, TimeCol) = Timestamp
    End If
    Next
    End If
    End If
    End Sub
    

    Now, for a better understanding of the output, you can see the below image.

    I hope this may solve your issue. 

    Raiyan , on behalf of ExcelDemy

  12. Reply Bishawajit Chakraborty
    Bishawajit Chakraborty Apr 17, 2023 at 2:42 PM

    Thank you, Wlad, for your wonderful question.

    Here is the explanation for your question.

    Array Looping: The “For” loop iterates through the values in the “arr” array. The “LBound(arr, 1)” method returns the lower bound of the array’s first dimension, while the “UBound(arr, 1)” function delivers the upper bound of the array’s first dimension. The loop counter “i” takes values from the lower bound to the higher bound of the “arr” array’s first dimension.

    Empty Loop: Because there are no statements or actions between the “For” and “Next” keywords, the loop looks to be an empty loop that does nothing significant.

    I hope this may solve your issue. 

    Bishawajit, on behalf of ExcelDemy

  13. Reply Bishawajit Chakraborty
    Bishawajit Chakraborty Apr 3, 2023 at 2:47 PM

    Thank you, SAJAD, for your wonderful question.

    Here is the solution to your question. Please take a look at the below steps.

    • Open a new workbook in Excel and create a table with three columns – “Invoice”, “Item”, and “Quantity”.
    • Enter the data as follows:
    • Load this data into Power Query by selecting the table and clicking on “From Table/Range” under the “Data” tab.
    • In the Power Query Editor window, select the “Item” column and click on “Group By” under the “Transform” tab.
    • In the Group By dialog box, set the “New column name” as “Count” and select the “Count Rows” option for the “Operation”.
    • Click on “OK” to create a new column that shows the count of each item sold.
    • Add a new column and name it “Combination”.
    • In the “Combination” column, enter the following formula: =IF([Count]>1,”Coffee and sandwich”,”Coffee only”)
    • Click on “Close & Load” to load the transformed data into Excel.
    • Insert a Pivot Table and select the transformed data as the source.
    • Drag the “Combination” field to the Rows area and the “Count” field to the Values area.
    • Add a new field to the Values area and name it “Total items sold”.
    • Set the “Total items sold” field to the “Sum” aggregation function.
    • Your final result should look like this:

    I hope this may solve your issue. 

    Bishawajit, on behalf of ExcelDemy

  14. Reply Bishawajit Chakraborty
    Bishawajit Chakraborty Apr 3, 2023 at 12:16 PM

    Thank you, SIDDHARTH, for your wonderful question.

    Here is the solution to your question. Please take a look at the below steps.

    • Create a table with columns for Roll Number, Name, and Marks in Excel.
    • Enter the Roll Number, Name, and Marks for each student in the table.
    • Create a new sheet in Excel and name it “Marksheets”.
    • In cell A1 of the “Marksheets” sheet, enter the text “Roll Number”.
    • In cell B1, enter the text “Name”.
    • In cell C1, enter the text “Marks”.
    • Select cell A2 on the “Marksheets” sheet.

    • Go to the “Data” tab in the Excel ribbon and click on “Data Validation”.
    • In the “Data Validation” window, select “List” as the validation criteria.
    • In the “Source” field, enter the range of Roll Numbers from the table you created earlier.
    • Click “OK” to close the “Data Validation” window.
    • Repeat steps for cells B2 and C2, but select the range of Names and Marks respectively.
    • Now, select the Roll Number, Name, and Marks here.

    Here is our  VBA code. Therefore, you can apply this code to solve your problem.

    Sub Generate_Marksheet()
    Dim roll_number As String
    Dim name As String
    Dim marks As Integer
    Dim i As Integer
    i = 2 'Starting at row 2 of the Marksheets sheet
    Do While Worksheets("Marksheets").Cells(i, 1) <> "" 'Loop until the Roll Number column is empty
    roll_number = Worksheets("Marksheets").Cells(i, 1).Value
    name = Worksheets("Marksheets").Cells(i, 2).Value
    marks = Worksheets("Marksheets").Cells(i, 3).Value
    'Creating a new sheet for each Roll Number
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).name = roll_number
    'Adding the Name and Marks to the sheet
    Worksheets(roll_number).Range("A1").Value = "Name"
    Worksheets(roll_number).Range("B1").Value = "Marks"
    Worksheets(roll_number).Range("A2").Value = name
    Worksheets(roll_number).Range("B2").Value = marks
    i = i + 1 'Moving to the next row
    Loop
    End Sub

    Here, you will see the final result in another sheet with names and marks.

    I hope this may solve your issue. 

    Bishawajit, on behalf of ExcelDemy

  15. Reply Bishawajit Chakraborty
    Bishawajit Chakraborty Mar 9, 2023 at 11:55 AM

    Thank you, CHAD for your wonderful question.

    Firstly, you cannot change the recipient’s name after emailing. When you apply the VBA code, the emails will pop up for sending. And on the Outlook email section, you can customize emails. So, you have to put the email address first in the recipient’s column.

    This will do what you desire. If you have further queries, let us know.

    Regards

    Bishawajit, on behalf of ExcelDemy

  16. Reply Bishawajit Chakraborty
    Bishawajit Chakraborty Feb 22, 2023 at 3:52 PM

    Hi NICOLÁS,
    Thanks for following our article.  Please go through the whole article and create the template. And, you can use one ticker for only one sheet to import stock prices from any other website.
    Let us know if your problem is fixed.
    Regards,
    Bishawajit Chakraborty
    Author at ExcelDemy

  17. Reply Bishawajit Chakraborty
    Bishawajit Chakraborty Feb 22, 2023 at 3:12 PM

    Thank you JACK MACEY, for your wonderful question

    If you want to import stock prices on a daily, weekly, monthly basis, you can visit this website link, from which we scraped our live data in accordance with this article.

    Then, click on the Frequency option and select whatever you want for your desired frequency.

    Finally, apply the rest procedures mentioned in the article. I hope this may solve your issue.

    Bishawajit, on behalf of ExcelDemy

  18. Reply Bishawajit Chakraborty
    Bishawajit Chakraborty Feb 19, 2023 at 11:28 AM

    Thank you, MEREDITH, for your wonderful question. 

    Here is our modified VBA code. Therefore, you can apply this code to solve your problem but you can also modified this code for three digit numbers by using word “and” in our code section.

    Here is the image of modified code where we have added the word “and”.

    Function word(SNum As String)
    'Declare the Variables
    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 excced 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(xNumStr) - xDPInt) > 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(xStrTemp), 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
    word = 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
    'Converts a Number from 100 to 999 into Word
    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 = " and " & 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
    'Converts a Number from 10 to 19 into Word
    
    zTArr1 = Array("Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
    'Converts a Number from 20 to 99 into Word
    
    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
    'Converts a Number from 1 to 9 into Word
    
    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

    Here, you will see the final result.

    I hope this may solve your issue. 

    Bishawajit, on behalf of ExcelDemy

     

  19. Reply Bishawajit Chakraborty
    Bishawajit Chakraborty Jan 17, 2023 at 4:31 PM

    Thank you BRIAN for your wonderful question

    If you want to import stock prices on a weekly basis, you can visit this website link, from which we scraped our live data in accordance with this article.

    Then, click on the Frequency option and select Weekly for your desired frequency.

    Finally, apply the rest procedures mentioned in the article. I hope this may solve your issue. 

    Bishawajit, on behalf of ExcelDemy

  20. Reply Bishawajit Chakraborty
    Bishawajit Chakraborty Jan 17, 2023 at 4:18 PM

    Thank you, Fred, for your wonderful question.

    Firstly, go to the Home tab.

    You can keep the decimal places of any number at any place by using the Increase Decimal and the Decrease Decimal icons here.

    Bishawajit, on behalf of ExcelDemy

  21. Reply Bishawajit Chakraborty
    Bishawajit Chakraborty Jan 17, 2023 at 4:13 PM

    Thank you, SALWA for your wonderful question. 

    First off, you cannot change the time for a scheduled email; however, you can add the remaining date in the email using the VBA code. When you apply the VBA code, the emails will pop up for sending. And on the Outlook email section, you can customize emails with scheduled time

    Public Sub SendEmail02()
        Dim Date_Range As Range
        Dim Mail_Recipient As Range
        Dim Email_Text As Range
        Dim Outlook_App_Create As Object
        Dim Mail_Item As Object
        Dim Last_Row As Long
        Dim VB_CR_LF, Email_Body, Date_Range_Value, Send_Value, Subject As String
        Dim i As Long
        On Error Resume Next
        Set Date_Range = Application.InputBox("Please choose the date range:", "Message Box", , , , , , 8)
        If Date_Range Is Nothing Then Exit Sub
        Set Mail_Recipient = Application.InputBox("Please select the Email addresses:", "Message Box", , , , , , 8)
        If Mail_Recipient Is Nothing Then Exit Sub
        Set Email_Text = Application.InputBox("Select the Email Text:", "Message Box", , , , , , 8)
        If Email_Text Is Nothing Then Exit Sub
        Last_Row = Date_Range.Rows.Count
        Set Date_Range = Date_Range(1)
        Set Mail_Recipient = Mail_Recipient(1)
        Set Email_Text = Email_Text(1)
        Set Outlook_App_Create = CreateObject("Outlook.Application")
        For i = 1 To Last_Row
            Date_Range_Value = ""
            Date_Range_Value = Date_Range.Offset(i - 1).Value
            If Date_Range_Value <> "" Then
            If CDate(Date_Range_Value) - Date <= 7 And CDate(Date_Range_Value) - Date > 0 Then
                Send_Value = Mail_Recipient.Offset(i - 1).Value
                Subject = Email_Text.Offset(i - 1).Value & " on " & Date_Range_Value
                VB_CR_LF = "<br><br>"
                Email_Body = "<HTML><BODY>"
                Email_Body = Email_Body & "Dear " & Send_Value & VB_CR_LF
                Email_Body = Email_Body & "Text : " & Email_Text.Offset(i - 1).Value & VB_CR_LF
                Email_Body = Email_Body & "</BODY></HTML>"
                Set Mail_Item = Outlook_App_Create.CreateItem(0)
                With Mail_Item
                    .Subject = Subject
                    .To = Send_Value
                    .HTMLBody = Email_Body
                    .Display
                End With
                Set Mail_Item = Nothing
            End If
        End If
        Next
        Set Outlook_App_Create = Nothing
    End Sub

    Then, using your Outlook account, you can set it up for a scheduled email. I hope this may solve your issue. 

    Bishawajit, on behalf of ExcelDemy

  22. Reply Bishawajit Chakraborty
    Bishawajit Chakraborty Dec 21, 2022 at 4:34 PM

    Thank you, JOHN SCURAS, for your comment.

    You can use the below formula to determine your commission of the model.

    =IF(H36>C33,SUMPRODUCT(–(H36>$C$33:$C$56),(H36-$C$33:$C$56),$D$33:$D$56)+C33*E33,C33*E33)

    Here, H36 is the sales amount, C33 is your first Deal Size Range (High), C33:C56 is all values for Deal Size Range (High) column, D33:D56 is all values from the % incremental commission column, and E33 is the first value of the incremental commission (5%).

    Best Regards,
    Bishwajit
    Team ExcelDemy

  23. Reply Bishawajit Chakraborty
    Bishawajit Chakraborty Dec 18, 2022 at 10:56 AM

    Thank you so much JOHN JOYCE for your comment. To set all the columns into text, you can follow the below steps accordingly.

    • Firstly, click on the General option from the Home tab.

    • Then, choose the Text option after selecting your entire data set. After that, you will get your desired result.

    After changing the format save the file and re-open it to check whether the data shows in the proper format.

    Best Regards,
    Bishwajit
    Team ExcelDemy

  24. Reply Bishawajit Chakraborty
    Bishawajit Chakraborty Dec 18, 2022 at 12:21 AM

    Greetings AND74,
    Thanks for noticing the error.
    It will be OR(C5QUARTILE($C$5:$C$14,3)+1.5*$D$16) instead of OR(C5QUARTILE($C$5:$C$14,1)+1.5*$D$16).
    We will make the corrections shortly.

    Best Regards,
    Bishwajit
    Team ExcelDemy

  25. Reply Bishawajit Chakraborty
    Bishawajit Chakraborty Dec 17, 2022 at 11:51 PM

    Thank you, Edward Vinieratos, for your comment. According to your formula, your data seems quite large. It is hard to identify where the error lies. Can you please kindly share your excel file with us? We will create another Excel file with your desired result. We will reply to you back as soon as possible. Email Address: [email protected].

  26. Reply Bishawajit Chakraborty
    Bishawajit Chakraborty Dec 12, 2022 at 2:37 PM

    Thank you Edijs for your wonderful question. To edit the cell range follow these steps:

    Firstly, enable the Microsoft Forms 2.0.

    Secondly, type this code on to sheet 2.

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Cells.Count > 1 Then Exit Sub
        If Not Application.Intersect(Range("D5:D10"), Target) Is Nothing Then
        Dim xDataObj As New MSForms.DataObject
        Dim rowNumber As Integer
        rowNumber = Split(Target.Address, "$")(2)
        xDataObj.SetText rowNumber
        xDataObj.PutInClipboard
            If IsNumeric(Target.Value) And Target.Value > 700 Then
                Call Send_Email_Condition_Cell_Value_Change
            End If
        End If
    End Sub

    Thirdly, type change the code from Module 1.

    Sub Send_Email_Condition_Cell_Value_Change()
        Dim pApp As Object
        Dim pMail As Object
        Dim pBody As String
        Dim xDataObj As New MSForms.DataObject
        xDataObj.GetFromClipboard
        Set pApp = CreateObject("Outlook.Application")
        Set pMail = pApp.CreateItem(0)
        pBody = "Hello, " & Cells(xDataObj.GetText, 2).Value & vbNewLine & _
                  "You've Payment Due." & vbNewLine & _
                  "Please Pay it to avoid extra fees."
        On Error Resume Next
        With pMail
            .To = Cells(xDataObj.GetText, 3).Value
            .CC = ""
            .BCC = ""
            .Subject = "Request For Payment"
            .Body = pBody
            .Display  'We can use .Send to Send the Email
        End With
        On Error GoTo 0
        Set pMail = Nothing
        Set pApp = Nothing
    End Sub

    This should solve your problem. You can see the output from the following animated image.

    If you have any further question, please let us know.

    Regards

    Bishawajit, on behalf of ExcelDemy

  27. Reply Bishawajit Chakraborty
    Bishawajit Chakraborty Dec 12, 2022 at 2:36 PM

    Thank you, Brian for the comment. You can use the following code to insert timestamp when value from a specific row change:

    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim CellRow, TimeRow, Row, Col As Integer
    Dim DpRng, Rng As Range
    Dim Timestamp As Variant
    CellRow = 8
    TimeRow = 9
    Row = Target.Row
    Col = Target.Column
    If Row <= 7 Then Exit Sub
    Timestamp = Format(Now(), "dd-mm-yy hh:mm:ss AM/PM")
    If Target.Text <> "" Then
    If Row = CellRow Then
    Cells(TimeRow, Col) = Timestamp
    Else
    On Error Resume Next
    Set DpRng = Target.Dependents
    For Each Rng In DpRng
    If Rng.Row = CellRow Then
    Cells(Rng.Row, TimeRow) = Timestamp
    End If
    Next
    End If
    End If
    End Sub

    The following shows it working.

    Now, for your second part of the comment. You can use this code for multiple rows:

    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim CellRow, CellRow1, TimeRow, Row, Col As Integer
    Dim DpRng, Rng As Range
    Dim Timestamp As Variant
    CellRow1 = 7
    CellRow = 8
    TimeRow = 9
    Row = Target.Row
    Col = Target.Column
    If Row <= 6 And Row >= 9 Then Exit Sub
    Timestamp = Format(Now(), "dd-mm-yy hh:mm:ss AM/PM")
    If Target.Text <> "" Then
    If Row = CellRow1 Then
    Cells(TimeRow, Col) = Timestamp
    ElseIf Row = CellRow Then
    Cells(TimeRow, Col) = Timestamp
    Else
    On Error Resume Next
    Set DpRng = Target.Dependents
    For Each Rng In DpRng
    If Rng.Row = CellRow Then
    Cells(Rng.Row, TimeRow) = Timestamp
    End If
    Next
    End If
    End If
    End Sub

    The animated image shows it is working for two rows.

    Regards

    Bishawajit, on behalf of ExcelDemy

  28. Reply Bishawajit Chakraborty
    Bishawajit Chakraborty Dec 12, 2022 at 2:34 PM

    Thank you Prachi Davade for your wonderful question. You can change the

    Set XDueDate = Application.InputBox("Select the column for Deadline/Due Date date column:", "ExcelDemy", , , , , , 8)

    to

    Set XDueDate = Range("D5:D7")

    This will do what you desire. If you have further queries, let us know.

    Regards

    Bishawajit, on behalf of ExcelDemy

  29. Reply Bishawajit Chakraborty
    Bishawajit Chakraborty Nov 23, 2022 at 9:57 AM

    Thank you, Madan, for your comment. In our Excel file, this formula is working. Can you please share your excel file with us? Email Address: [email protected]. We will try to solve your problem as soon as possible.

  30. Reply Bishawajit Chakraborty
    Bishawajit Chakraborty Nov 13, 2022 at 1:07 PM

    Thank you so much, Peter, for your comment.
    You will find your solution in the below steps. Please follow the steps accordingly.

    Steps:
    • Firstly, go to the Data tab.
    • And, select the group option.
    1
    • Now, you will see here the plus (+) sign.
    • Here, we have grouped the 5th, 6th, and 7th rows.
    • Then, go to the Review tab.
    • And, select the Allow Edit Ranges option.
    • After that, choose the ranges.
    • And, type the password and click OK.
    2
    • Retype the password and click OK.
    3
    • Now remove this window.
    4
    • Firstly, go to the Review tab.
    • Secondly, select the Protect Sheet option.
    5
    • Now, write your password.
    • And, click OK.
    7
    • Then, retype your password and click OK.
    6
    • So, if you click on the plus (+) sign, it will show you the given result you want.
    8

  31. Reply Bishawajit Chakraborty
    Bishawajit Chakraborty Oct 20, 2022 at 1:50 PM

    Hello Rash,
    Thank you for your comment. If you want to send a table within the email body for any range, then you have to create a table in any worksheet. For this, you will follow our Method No.3 where we have used a VBA code to send an active sheet within the email body. Firstly, you can create a table in this active sheet and send this sheet with your table within the email body. Can you please share your Excel file with us? We will customize the file according to your requirements. Email address [email protected].

    Regards,
    Bishawajit Chakraborty (Exceldemy Team)

  32. Reply Bishawajit Chakraborty
    Bishawajit Chakraborty Oct 19, 2022 at 3:13 PM

    Thank you, Dhroov, for your comment. In our Excel file, this formula is working. Can you please share your excel file with us? Email Address: [email protected]. We will try to solve your problem as soon as possible.

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo