Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

User Posts: Bishawajit Chakraborty
0
How to Make an Excel Metadata Extractor (with Easy Steps)
0

When you develop, download, or upload a Microsoft Excel workbook, it may contain information that you do not want to make public online. The formal name for ...

0
How to Find Largest Lookup Value in Excel (3 Easy Ways)
0

In this article, we will demonstrate 3 handy ways to find the largest lookup value using the combination of the VLOOKUP function with other functions in Excel. ...

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

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

0
Refresh All Data Connections and Pivot Tables with Excel VBA
0

After you create a pivot table, you must use the change data source command to update or refresh the data in it. However, it is time-consuming and inconvenient ...

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

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

0
How to Use DVAR Function in Excel (2 Suitable Examples)
0

You can employ the Excel database functions to help you when using an Excel dataset. Sometimes, you may need to find the variance due to some specific ...

0
How to Calculate Tracking Error in Excel (with Detailed Steps)
0

Tracking error is one of the most crucial metrics for evaluating a portfolio's performance and a portfolio manager's potential for outperforming the market or ...

0
How to Add SmartArt Graphics in Excel (7 Suitable Examples)
0

SmartArt mixes graphic and textual content to give information a more aesthetically pleasing presentation. You can make fantastic SmartArt with the help of ...

0
How to Use IMSUB Function in Excel (2 Suitable Examples)
0

An imaginary coefficient with a real coefficient makes up a complex number. Real and imaginary coefficients can be transformed into complex numbers of the type ...

0
How to Set Intercept Trendline in Excel (with Easy Steps)
0

Excel charts are a fantastic tool for displaying data. Excel charts can easily represent complex data and provide users with useful information. The majority ...

0
How to Make Textbox Datepicker with Excel VBA (with Easy Steps)
0

Users frequently want to click a button and choose a date. The same holds true for Microsoft Excel programmers. Making use of InputBox and MessageBox while ...

0
How to Generate or List All Possible Permutations in Excel
0

When the order of the arrangements is concerned, a permutation is a mathematical technique that establishes the total number of possible arrangements in a ...

0
How to Do Web Scraping Without Browser with Excel VBA
0

Definitely, one of the most popular software programs utilized globally and in many fields is Microsoft Excel. Users can access web pages and extract data from ...

0
How to Encode Data in Excel (with Easy Steps)
0

You have come to the right place if you're looking for instructions on how to encode data in Excel. Despite this being a necessity, there is currently no ...

0
How to Remove Highlighted Rows in Excel (2 Easy Ways)
0

When working with large data sets in Microsoft Excel, you may need to remove specific rows from the worksheet because of security purposes. This article will ...

Browsing All Comments By: Bishawajit Chakraborty
  1. Reply
    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 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

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

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

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

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

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

  8. Reply
    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].

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

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

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

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

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

  14. Reply
    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)

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

ExcelDemy
Logo