User Posts: Bishawajit Chakraborty
0
VBA to Get Array Dimensions in Excel (4 Examples)
0

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
Excel VBA to Set Column Width Based on Cell Value (3 Examples)
0

One of the most frequent actions you complete each day while developing your reports, summary tables, dashboards, or even when using spreadsheets solely for ...

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

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)
0

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

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

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
Excel VBA to Sort Worksheet (3 Examples)
0

If you use Excel frequently, you are likely aware of how difficult it can be to handle. After you have more than a couple of worksheets, you need to manually ...

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

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)
0

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)
0

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)
0

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

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

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

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

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

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

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

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

  8. Reply Avatar photo
    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

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

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

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

     

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

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

  14. Reply Avatar photo
    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

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

  16. Reply Avatar photo
    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

  17. Reply Avatar photo
    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

  18. Reply Avatar photo
    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].

  19. Reply Avatar photo
    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

  20. Reply Avatar photo
    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

  21. Reply Avatar photo
    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

  22. Reply Avatar photo
    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.

  23. Reply Avatar photo
    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

  24. Reply Avatar photo
    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)

  25. Reply Avatar photo
    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